Get VBA and an Excel spreadsheet for Black-Scholes and the Greeks (Delta, Gamma, Vega, Theta, Rho) here. You can easily use the VBA in your own option pricing spreadsheets.
This VBA and the corresponding Excel spreadsheet prices a European option with continuous dividends).
The download link for the spreadsheet is at the bottom of the article.
In the following code
- OptionType is either “C” for a call or “P” for a put
- S is the stock price
- X is the strike price
- T is the time to expiry
- r is the risk free rate
- v is the volatility
- d is the dividend yield
An example of calling one of these functions in Excel is
=OptionTheta(“C”,StockPrice,StrikePrice,TimeToExpiry,RiskFreeRate,Volatility,Dividend)
Function dOne(S, X, T, r, v, d) dOne = (Log(S / X) + (r - d + 0.5 * v ^ 2) * T) / (v * (Sqr(T))) End Function Function NdOne(S, X, T, r, v, d) NdOne = Exp(-(dOne(S, X, T, r, v, d) ^ 2) / 2) / (Sqr(2 * Application.WorksheetFunction.Pi())) End Function Function dTwo(S, X, T, r, v, d) dTwo = dOne(S, X, T, r, v, d) - v * Sqr(T) End Function Function NdTwo(S, X, T, r, v, d) NdTwo = Application.NormSDist(dTwo(S, X, T, r, v, d)) End Function Function OptionPrice(OptionType, S, X, T, r, v, d) If OptionType = "C" Then OptionPrice = Exp(-d * T) * S * Application.NormSDist(dOne(S, X, T, r, v, d)) - X * Exp(-r * T) * Application.NormSDist(dOne(S, X, T, r, v, d) - v * Sqr(T)) ElseIf OptionType = "P" Then OptionPrice = X * Exp(-r * T) * Application.NormSDist(-dTwo(S, X, T, r, v, d)) - Exp(-d * T) * S * Application.NormSDist(-dOne(S, X, T, r, v, d)) End If End Function Function OptionDelta(OptionType, S, X, T, r, v, d) If OptionType = "C" Then OptionDelta = Application.NormSDist(dOne(S, X, T, r, v, d)) ElseIf OptionType = "P" Then OptionDelta = Application.NormSDist(dOne(S, X, T, r, v, d)) - 1 End If End Function Function OptionTheta(OptionType, S, X, T, r, v, d) If OptionType = "C" Then OptionTheta = -((S * v * NdOne(S, X, T, r, v, d)) / (2 * Sqr(T)) - r * X * Exp(-r * (T)) * NdTwo(S, X, T, r, v, d)) / 365 ElseIf OptionType = "P" Then OptionTheta = -((S * v * NdOne(S, X, T, r, v, d)) / (2 * Sqr(T)) + r * X * Exp(-r * (T)) * (1 - NdTwo(S, X, T, r, v, d))) / 365 End If End Function Function Gamma(S, X, T, r, v, d) Gamma = NdOne(S, X, T, r, v, d) / (S * (v * Sqr(T))) End Function Function Vega(S, X, T, r, v, d) Vega = 0.01 * S * Sqr(T) * NdOne(S, X, T, r, v, d) End Function Function OptionRho(OptionType, S, X, T, r, v, d) If OptionType = "C" Then OptionRho = 0.01 * X * T * Exp(-r * T) * Application.NormSDist(dTwo(S, X, T, r, v, d)) ElseIf OptionType = "P" Then OptionRho = -0.01 * X * T * Exp(-r * T) * (1 - Application.NormSDist(dTwo(S, X, T, r, v, d))) End If End Function |
Download Excel Spreadsheet To Calculate Option Price and Greeks in VBA
OptionPrice = Exp(-d * T) * S * Application.NormSDist(dOne(S, X, T, r, v, d)) – X * Exp(-r * T) * Application.NormSDist(dOne(S, X, T, r, v, d) – v * Sqr(T))
ElseIf OptionType = “P” Then
Hello, are you sure, of this equation ?
I would like know why in the second part of equation we have
Application.NormSDist(dOne(S, X, T, r, v, d) not Dtwo …
Best Regards
Because “dOne(S, X, T, r, v, d) – v * Sqr(T)” is the same thing as “dTwo(S, X, T, r, v, d)”.
There is an error in the formula of Theta
If OptionType = “C” Then
OptionTheta = -((S * v * NdOne(S, X, T, r, v, d)) / (2 * Sqr(T)) – r * X * Exp(-r * (T)) * NdTwo(S, X, T, r, v, d)) / 365
ElseIf OptionType = “P” Then
OptionTheta = -((S * v * NdOne(S, X, T, r, v, d)) / (2 * Sqr(T)) + r * X * Exp(-r * (T)) * (1 – NdTwo(S, X, T, r, v, d))) / 365
End If
The error is in a misplaced negative. The corrected formula should be this
If OptionType = “C” Then
OptionTheta = (-(S * v * NdOne(S, X, T, r, v, d)) / (2 * Sqr(T)) – r * X * Exp(-r * (T)) * NdTwo(S, X, T, r, v, d)) / 365
ElseIf OptionType = “P” Then
OptionTheta = (-(S * v * NdOne(S, X, T, r, v, d)) / (2 * Sqr(T)) + r * X * Exp(-r * (T)) * (1 – NdTwo(S, X, T, r, v, d))) / 365
End If
Hi there.
Can you please respond to the comments above (incorrect formula)? Are they correct? Should we be making changes?
As far as I know (which is not much), I get decent values when using the equations.
Maximilano is correct, the function for Theta should be changed. If you are not sure of the math, just use another option calculator (for example http://www.ivolatility.com/calc/) and compare the results. Everything is in line or close, except for the theta, and the theta gets in line after applying the fix.
I have re-wrote the VBA code.
(FYI in my code I use K instead of X)
Function dOne(S, K, T, r, v)
dOne = (Log(S / K) + (r+0.5 * v ^ 2) * T) / (v * (Sqr(T)))
End Function
Function NdOne(S, K, T, r, v)
NdOne = Application.WorksheetFunction.NormSDist(dOne(S, K, T, r, v))
End Function
Function dTwo(S, K, T, r, v)
dTwo = dOne(S, K, T, r, v) – v * Sqr(T)
End Function
Function NdTwo(S, K, T, r, v)
NdTwo = Application.WorksheetFunction.NormSDist(dTwo(S, K, T, r, v))
End Function
Function OptionPrice(OptionType, S, K, T, r, v)
If OptionType = “Call” Then
OptionPrice = Exp(-r * T) * S * Application.WorksheetFunction.NormSDist(dOne(S, K, T, r, v)) – (K * Exp(-r * T) * Application.WorksheetFunction.NormSDist(dTwo(S, K, T, r, v)))
ElseIf OptionType = “Put” Then
OptionPrice = Exp(-r * T) * K * Application.WorksheetFunction.NormSDist(-dTwo(S, K, T, r, v)) – Exp(-r * T) * S * Application.WorksheetFunction.NormSDist(-dOne(S, K, T, r, v))
End If
End Function
Function OptionDelta(OptionType, S, K, T, r, v)
If OptionType = “Call” Then
OptionDelta = Exp(-r * T) * Application.WorksheetFunction.NormSDist(dOne(S, K, T, r, v))
ElseIf OptionType = “Put” Then
OptionDelta = Exp(-r * T) * (Application.WorksheetFunction.NormSDist(dOne(S, K, T, r, v)) – 1)
End If
End Function
Function OptionGamma(S, K, T, r, v)
OptionGamma = Exp(-r * T) * Application.WorksheetFunction.NormDist(dOne(S, K, T, r, v), 0, 1, 0) / (S * (v * Sqr(T)))
End Function
Function OptionVega(S, K, T, r, v)
OptionVega = Application.WorksheetFunction.NormDist(dOne(S, K, T, r, v), 0, 1, 0) * S * Sqr(T)
End Function
Function OptionTheta(OptionType, S, K, T, r, v)
If OptionType = “Call” Then
OptionTheta = (-((S * Application.WorksheetFunction.NormDist(dOne(S, K, T, r, v), 0, 1, 0) * v) / (2 * Sqr(T)))) – (r * K * Exp(-r * T) * Application.WorksheetFunction.NormSDist(dTwo(S, K, T, r, v)))
ElseIf OptionType = “Put” Then
OptionTheta = (-((S * Application.WorksheetFunction.NormDist(dOne(S, K, T, r, v), 0, 1, 0) * v) / (2 * Sqr(T)))) + (r * K * Exp(-r * T) * Application.WorksheetFunction.NormSDist(-dTwo(S, K, T, r, v)))
End If
End Function
Regards