Black-Scholes and Greeks in VBA

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)

Black Scholes and Greeks VBA

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


6 thoughts on “Black-Scholes and Greeks in VBA”

  1. 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

    Reply

  2. 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

    Reply

  3. 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.

    Reply

    • 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.

      Reply

  4. 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

    Reply

Leave a Comment

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.