David,
The following may help. Found it on another SI thread. Haven't figured out how to use it in an Excel spreadsheet yet. I haven't done much in the way of fancy Excel spreadsheets. Let me know if you get it working.
=========== cut after this line ================
'---------------------------------------------
'BSD : Internal Function used to calculate Delta
'---------------------------------------------
Function BSD(Price, Strike, IntRate, Vol, T)
If Vol = 0 Then
BSD = 1E+100
Else
BSD = (Log(Price / Strike) + (IntRate + Vol * Vol / 2) * T) / (Vol * Sqr(T))
End If
End Function
'-----------------------------------------------------
'BSDelta : Sensitivity of option value to changes in the
' price of the underlying asset. If T > 0, the
' value returned is the sensitivity of the time
' component only of the option value.
'-----------------------------------------------------
Function BSDelta(Price, Strike, IntRate, Vol, T)
BSDelta = BSSND(BSD(Price, Strike, IntRate, Vol, T))
End Function
'------------------------------------------------------
'BSSND : Cumulative standard normal distribution function
' (mean=0, standard deviation=1)
'------------------------------------------------------
Function BSSND(x)
BSSND = Application.NormDist(x, 0, 1, True)
End Function
'------------------------------------------------------
'BSCall : Black-Scholes model for a European call (also
' valid for an American call option based on the
' assumption that it is never optimal to exercise
' an American call early.
'--------------------------------------------------------
Function BSCall(Price, Strike, IntRate, Vol, T)
CD = BSD(Price, Strike, IntRate, Vol, T)
BSCall = Price * BSSND(CD) - Strike * Exp(-IntRate * T) * BSSND(CD - Vol * Sqr(T)) End Function
'------------------------------------------------------
'BSPut : Black-Scholes model for a European put (also
' valid for an American put option if the extra
' value of being able to exercise early is ignored.)
'--------------------------------------------------------
Function BSPut(Price, Strike, IntRate, Vol, T)
BSPut = 0
If Vol = 0 Then
If Price < Strike Then
BSPut = Strike - Price
End If
Else
CD = BSD(Price, Strike, IntRate, Vol, T)
BSPut = -Price * BSSND(-CD) + Strike * Exp(-IntRate * T) * BSSND(-CD + Vol * Sqr(T))
End If
End Function
'------------------------------------------------------------
'BSVega : Sensitivity of option price to volatility. This
' function can be used for either calls or puts. It
' returns a minimum value of 1e-20 to avoid overflow
' problems
'------------------------------------------------------------
Function BSVega(Price, Strike, IntRate, Vol, T)
Vol1 = Vol * 0.999999
Vol2 = Vol * 1.000001
Value1 = BSCall(Price, Strike, IntRate, Vol1, T)
Value2 = BSCall(Price, Strike, IntRate, Vol2, T)
BSVega = (Value2 - Value1) / (Vol2 - Vol1)
If BSVega = 0 Then BSVega = 1E-99
End Function
'------------------------------------------------------------
'BSIVol : Computes implied volatility of underlying stock
' from known option price using the method of bisection.
' Set IsPut to TRUE if OptionPrice is for a put option.
' (FALSE for a call option)
'Note: The implied volatility consistent for a given option
' price may or may not exist (ie: there may be no
' volatility for which the theoretical option price is
' equal to the given OptionPrice). For example, if the
' quoted option price is less than the intrinsic value
' plus the time value of lending the strike price, the
' the computed volatility is set to zero.
'------------------------------------------------------------
Function BSIVol(StockPrice, OptionPrice, Strike, IntRate, T, IsPut As Boolean) As Double
Dim NPass As Integer
Dim LoVol As Double
Dim LoOpt As Double
Dim HiVol As Double
Dim HiOpt As Double
Dim MidVol As Double
Dim MidOpt As Double
Dim done As Boolean
MaxPass = 100
NPass = 0
LoVol = 0
HiVol = 0.9999
If Not IsPut Then
LoOpt = BSCall(StockPrice, Strike, IntRate, LoVol, T)
HiOpt = BSCall(StockPrice, Strike, IntRate, HiVol, T)
Else
LoOpt = BSPut(StockPrice, Strike, IntRate, LoVol, T)
HiOpt = BSPut(StockPrice, Strike, IntRate, HiVol, T)
End If
done = False
While NPass < MaxPass And Not done
NPass = NPass + 1
MidVol = LoVol + (OptionPrice - LoOpt) * (HiVol - LoVol) / (HiOpt - LoOpt)
If Not IsPut Then
MidOpt = BSCall(StockPrice, Strike, IntRate, MidVol, T)
Else
MidOpt = BSPut(StockPrice, Strike, IntRate, MidVol, T)
End If
If (Abs(OptionPrice - MidOpt) / OptionPrice) < 0.000001 Then
done = True
ElseIf MidOpt < OptionPrice Then
LoVol = MidVol
LoOpt = MidOpt
ElseIf MidOpt > OptionPrice Then
HiVol = MidVol
HiOpt = MidOpt
Else
done = True
End If
Wend
BSIVol = MidVol
End Function |
