Black-Scholes in Excel, Part II

Following on from the post a few weeks ago on a simple Black-Scholes Excel macro, here is a follow up, with a slightly updated version that also calculates the major greeks (vega, gamma and delta). The formulae are from Hull’s book, or see here for examples of the closed-form greeks.

Usage of the spreadsheet is identical to the previous example, so refer to that post. The only difference is the output, which now looks like this (click to enlarge):

The spreadsheet is available for download here, and the code is below for reference.

Function BlackScholes(SpotPrice As Double, ExercisePrice As Double,
        TimeToMaturity As Double, RiskFreeRate As Double, sigma As Double,
        Optional DividendYield As Double) As Double()

    Dim d1 As Double
    Dim d2 As Double
    Dim Nd1 As Double
    Dim Nd2 As Double
    Dim N_dash_d1 As Double
    Dim ResultArray() As Double
    Dim gamma As Double
    Dim vega As Double
    Dim theta As Double

    ReDim ResultArray(10) As Double

    If (IsMissing(DividendYield)) Then
        d1 = WorksheetFunction.Ln(SpotPrice / ExercisePrice)
        + ((RiskFreeRate + (0.5 * (sigma ^ 2))) * TimeToMaturity)
    Else
        d1 = WorksheetFunction.Ln(SpotPrice / ExercisePrice)
        + ((RiskFreeRate - DividendYield + (0.5 * (sigma ^ 2))) * TimeToMaturity)
    End If

    d1 = d1 / (sigma * (TimeToMaturity ^ (1 / 2)))
    d2 = d1 - (sigma * (TimeToMaturity ^ (1 / 2)))
    Nd1 = WorksheetFunction.NormSDist(d1)
    Nd2 = WorksheetFunction.NormSDist(d2)

    'Call Value
    If (IsMissing(DividendYield)) Then
        ResultArray(0) = (SpotPrice * Nd1) - (ExercisePrice * Exp(-RiskFreeRate * TimeToMaturity) * Nd2)
    Else
        ResultArray(0) = Exp(-DividendYield * TimeToMaturity)
        * (SpotPrice * Nd1) - (ExercisePrice * Exp(-RiskFreeRate * TimeToMaturity) * Nd2)
    End If

    'Call Delta
    ResultArray(1) = Nd1 * Exp(-DividendYield * TimeToMaturity)

    'Call Gamma
    N_dash_d1 = (1 / ((2 * WorksheetFunction.Pi) ^ (1 / 2)) * (Exp((-d1 ^ 2) / 2)))
    gamma = (N_dash_d1 * Exp(-DividendYield * TimeToMaturity)) / (SpotPrice * sigma * (TimeToMaturity ^ (1 / 2)))
    ResultArray(2) = gamma

    'Call Vega
    vega = SpotPrice * (TimeToMaturity ^ (1 / 2)) * N_dash_d1 * Exp(-DividendYield * TimeToMaturity)
    ResultArray(3) = vega

    'Call Theta
    theta = -(SpotPrice * N_dash_d1 * sigma * Exp(-DividendYield * TimeToMaturity)) / (2 * (TimeToMaturity ^ (1 / 2)))
    theta = theta + (DividendYield * SpotPrice * Nd1 * Exp(-DividendYield * TimeToMaturity))
    theta = theta - (RiskFreeRate * ExercisePrice * Exp(-RiskFreeRate * TimeToMaturity) * Nd2)
    ResultArray(4) = theta

    'Put Value
    If (IsMissing(DividendYield)) Then
        ResultArray(5) = Exp(-RiskFreeRate * TimeToMaturity) * ExercisePrice
        * (1 - Nd2) - SpotPrice * (1 - Nd1)
    Else
        ResultArray(5) = Exp(-RiskFreeRate * TimeToMaturity) * ExercisePrice
        * WorksheetFunction.NormSDist(-d2) - Exp(-DividendYield * TimeToMaturity)
        * SpotPrice * WorksheetFunction.NormSDist(-d1)
    End If

    'Put delta
    ResultArray(6) = (Nd1 - 1) * Exp(-DividendYield * TimeToMaturity)

    'Put Gamma
    ResultArray(7) = gamma

    'Put Vega
    ResultArray(8) = vega

    'Put Theta
    theta = -(SpotPrice * N_dash_d1 * sigma * Exp(-DividendYield * TimeToMaturity)) / (2 * (TimeToMaturity ^ (1 / 2)))
    theta = theta - (DividendYield * SpotPrice * WorksheetFunction.NormSDist(-d1) * Exp(-DividendYield * TimeToMaturity))
    theta = theta + (RiskFreeRate * ExercisePrice * Exp(-RiskFreeRate * TimeToMaturity) * WorksheetFunction.NormSDist(-d2))
    ResultArray(9) = theta

    BlackScholes = ResultArray
End Function