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

  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

  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.

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

  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

Leave a Reply to Mathieu Cancel reply

Your email address will not be published. Required fields are marked *

What is 7 + 7 ?
Please leave these two fields as-is:
IMPORTANT! To be able to proceed, you need to solve the following simple math (so we know that you are a human) :-)