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