Black-Scholes Formula as a VBA Function in Excel

The computation of the Black-Scholes option pricing model, considering dividends and including associated Greeks (first, second, and third order), can be a challenging endeavor in Excel.

The worksheet formulas can get really big really fast.

Provided below is a streamlined VBA function designed to compute the Black-Scholes model, accounting for dividend yield, as well as the first-order Greeks (Delta, Gamma, Theta, Vega, and Rho).

Calculating second and third-order Greeks, however, is considerably more intricate and typically necessitates the application of numerical techniques such as finite differences or Taylor series expansions. The ensuing code presents the simplified function:

Function BlackScholesWithDividends(S As Double, K As Double, T As Double, r As Double, _
    Sigma As Double, Dividend As Double, OptionType As String) As Double
    Dim d1 As Double, d2 As Double
    Dim CallPutFactor As Double
    
    ' Calculate the required variables
    d1 = (Log(S / K) + (r - Dividend + 0.5 * Sigma ^ 2) * T) / (Sigma * Sqr(T))
    d2 = d1 - Sigma * Sqr(T)
    
    ' Determine the Call/Put factor
    If OptionType = "Call" Then
        CallPutFactor = 1
    ElseIf OptionType = "Put" Then
        CallPutFactor = -1
    Else
        BlackScholesWithDividends = CVErr(xlErrValue)
        Exit Function
    End If
    
    ' Calculate the option price
    BlackScholesWithDividends = CallPutFactor * (S * Exp(-Dividend * T) * WorksheetFunction.Norm.S.Dist(CallPutFactor * d1, 0, 1, True) _
        - K * Exp(-r * T) * WorksheetFunction.Norm.S.Dist(CallPutFactor * d2, 0, 1, True))
End Function

Function BlackScholesDelta(S As Double, K As Double, T As Double, r As Double, _
    Sigma As Double, Dividend As Double, OptionType As String) As Double
    ' Calculate Delta (First Order Greek)
    Dim d1 As Double
    d1 = (Log(S / K) + (r - Dividend + 0.5 * Sigma ^ 2) * T) / (Sigma * Sqr(T))
    
    If OptionType = "Call" Then
        BlackScholesDelta = Exp(-Dividend * T) * WorksheetFunction.Norm.S.Dist(d1, 0, 1, True)
    ElseIf OptionType = "Put" Then
        BlackScholesDelta = -Exp(-Dividend * T) * WorksheetFunction.Norm.S.Dist(-d1, 0, 1, True)
    Else
        BlackScholesDelta = CVErr(xlErrValue)
    End If
End Function

Function BlackScholesGamma(S As Double, K As Double, T As Double, r As Double, _
    Sigma As Double, Dividend As Double) As Double
    ' Calculate Gamma (First Order Greek)
    Dim d1 As Double
    d1 = (Log(S / K) + (r - Dividend + 0.5 * Sigma ^ 2) * T) / (Sigma * Sqr(T))
    
    BlackScholesGamma = Exp(-Dividend * T) * WorksheetFunction.Norm.S.Dist(d1, 0, 1, True) / (S * Sigma * Sqr(T))
End Function

Function BlackScholesTheta(S As Double, K As Double, T As Double, r As Double, _
    Sigma As Double, Dividend As Double, OptionType As String) As Double
    ' Calculate Theta (First Order Greek)
    Dim d1 As Double, d2 As Double
    d1 = (Log(S / K) + (r - Dividend + 0.5 * Sigma ^ 2) * T) / (Sigma * Sqr(T))
    d2 = d1 - Sigma * Sqr(T)
    
    If OptionType = "Call" Then
        BlackScholesTheta = -(S * Sigma * Exp(-Dividend * T) * WorksheetFunction.Norm.S.Dist(d1, 0, 1, True)) / (2 * Sqr(T)) _
            - r * K * Exp(-r * T) * WorksheetFunction.Norm.S.Dist(d2, 0, 1, True) + Dividend * S * Exp(-Dividend * T) * WorksheetFunction.Norm.S.Dist(d1, 0, 1, True)
    ElseIf OptionType = "Put" Then
        BlackScholesTheta = -(S * Sigma * Exp(-Dividend * T) * WorksheetFunction.Norm.S.Dist(-d1, 0, 1, True)) / (2 * Sqr(T)) _
            + r * K * Exp(-r * T) * WorksheetFunction.Norm.S.Dist(-d2, 0, 1, True) - Dividend * S * Exp(-Dividend * T) * WorksheetFunction.Norm.S.Dist(-d1, 0, 1, True)
    Else
        BlackScholesTheta = CVErr(xlErrValue)
    End If
End Function

Function BlackScholesVega(S As Double, K As Double, T As Double, r As Double, _
    Sigma As Double, Dividend As Double) As Double
    ' Calculate Vega (First Order Greek)
    Dim d1 As Double
    d1 = (Log(S / K) + (r - Dividend + 0.5 * Sigma ^ 2) * T) / (Sigma * Sqr(T))
    
    BlackScholesVega = S * Exp(-Dividend * T) * WorksheetFunction.Norm.S.Dist(d1, 0, 1, True) * Sqr(T)
End Function

Function BlackScholesRho(S As Double, K As Double, T As Double, r As Double, _
    Sigma As Double, Dividend As Double, OptionType As String) As Double
    ' Calculate Rho (First Order Greek)
    Dim d1 As Double, d2 As Double
    d1 = (Log(S / K) + (r - Dividend + 0.5 * Sigma ^ 2) * T) / (Sigma * Sqr(T))
    d2 = d1 - Sigma * Sqr(T)
    
    If OptionType = "Call" Then
        BlackScholesRho = K * T * Exp(-r * T) * WorksheetFunction.Norm.S.Dist(d2, 0, 1, True)
    ElseIf OptionType = "Put" Then
        BlackScholesRho = -K * T * Exp(-r * T) * WorksheetFunction.Norm.S.Dist(-d2, 0, 1, True)
    Else
        BlackScholesRho = CVErr(xlErrValue)
    End If
End Function

To use these functions, you need to have a basic understanding of the Black-Scholes model and its inputs. Each function calculates one of the Greeks