Stategic financial management

This query is : Resolved

Querist : Anonymous

Querist : Anonymous (Querist)

07 April 2013 How to find out value of call option by using Black scholes method without using logorothimic table particularly to find out Nd1&Nd2 value when standard deviation MEAN values single & two tails are given in question.

C A S.S.Agarwal, M.Com.,LL.B,

C A S.S.Agarwal, M.Com.,LL.B, (Expert)
Follow

28 May 2013 This article offers VBA code and an Excel spreadsheet to calculate the implied volatility of an option. This parameter is often compared to the historical volatility of the underlying asset to determine if the price of an option represents good value.

Implied volatility is the volatility estimated from the option price, asset price, strike price risk-free-rate, time to maturity and dividend yield. Calculating implied volatility needs iterative solution methods. Several technique are commonly used; one method uses Excel’s Goal Seek functionality, while other approaches use bisection or Newton-Raphson iteration.

The theory behind the Newton-Raphson method for finding the root of an equation is well documented. The approach gives the following equation to calculate the implied volatility of an option.

Vmkt is the market price of the option
VBS is the option price given by the Black-Scholes equation
σ is the volatility
Implied Volatility with Newton-Raphson Iteration

This VBA function calculates the price of a European option with the Black-Scholes equation.

Function EuropeanOption(CallOrPut, S, K, v, r, T, q)
Dim d1 As Double, d2 As Double, nd1 As Double, nd2 As Double
Dim nnd1 As Double, nnd2 As Double
d1 = (Log(S / K) + (r - q + 0.5 * v ^ 2) * T) / (v * Sqr(T))
d2 = (Log(S / K) + (r - q - 0.5 * v ^ 2) * T) / (v * Sqr(T))
nd1 = Application.NormSDist(d1)
nd2 = Application.NormSDist(d2)
nnd1 = Application.NormSDist(-d1)
nnd2 = Application.NormSDist(-d2)
If CallOrPut = "Call" Then
EuropeanOption = S * Exp(-q * T) * nd1 - K * Exp(-r * T) * nd2
Else
EuropeanOption = -S * Exp(-q * T) * nnd1 + K * Exp(-r * T) * nnd2
End If
End Function
This VBA function calculates the implied volatility of a European option with Newton-Raphson iteration.

Function ImpliedVolatility(CallOrPut, S, K, r, T, q, OptionValue, guess)
Dim epsilon As Double, dVol As Double, vol_1 As Double
Dim i As Integer, maxIter As Integer, Value_1 As Double, vol_2 As Double
Dim Value_2 As Double, dx As Double

dVol = 0.00001
epsilon = 0.00001
maxIter = 100
vol_1 = guess
i = 1
Do
Value_1 = EuropeanOption(CallOrPut, S, K, vol_1, r, T, q)
vol_2 = vol_1 - dVol
Value_2 = EuropeanOption(CallOrPut, S, K, vol_2, r, T, q)
dx = (Value_2 - Value_1) / dVol
If Abs(dx) < epsilon Or i = maxIter Then Exit Do
vol_1 = vol_1 - (OptionValue - Value_1) / dx
i = i + 1
Loop
ImpliedVolatility = vol_1
End Function
S is the strike price
CallOrPut is either “Call” or “Put”
K is the strike price
r is the risk-free rate
q is the dividend yield
v is the volatility
epsilon is a calculation tolerance
maxIter is the maximum allowable number of iterations
This Excel spreadsheet implements the two VBA functions given above for a sample option.

You need to be the querist or approved CAclub expert to take part in this query .
Click here to login now