Black-Scholes Pricing and Greeks in VBA

Use this Excel program to calculate European call's price, delta, gamma, theta, vega, and rho, as well as the European put's price, delta, gamma, theta, vega, and rho.


Download Excel Spreadsheet to Calculate Black-Scholes Prices and Greeks



The code underlying the procedure is as follows:



Function CallPrice(StockPrice As Double, StrikePrice As Double, TimeToExpiration As Double, Volatility As Double, RiskFreeRate As Double, DividendYield As Double)
    
    Dim d(2) As Double
    
    d(1) = (Log(StockPrice / StrikePrice) + (RiskFreeRate - DividendYield + (Volatility ^ 2) / 2) * TimeToExpiration) / (Volatility * Sqr(TimeToExpiration))
    d(2) = d(1) - Volatility * Sqr(TimeToExpiration)
    CallPrice = StockPrice * Exp(-DividendYield * TimeToExpiration) * Application.WorksheetFunction.NormSDist(d(1)) - _
        StrikePrice * Exp(-RiskFreeRate * TimeToExpiration) * Application.WorksheetFunction.NormSDist(d(2))

End Function

Function PutPrice(StockPrice As Double, StrikePrice As Double, TimeToExpiration As Double, Volatility As Double, RiskFreeRate As Double, DividendYield As Double)
    
    Dim d(2) As Double
    
    d(1) = (Log(StockPrice / StrikePrice) + (RiskFreeRate - DividendYield + (Volatility ^ 2) / 2) * TimeToExpiration) / (Volatility * Sqr(TimeToExpiration))
    d(2) = d(1) - Volatility * Sqr(TimeToExpiration)
    PutPrice = StrikePrice * Exp(-RiskFreeRate * TimeToExpiration) * Application.WorksheetFunction.NormSDist(-d(2)) - _
        StockPrice * Exp(-DividendYield * TimeToExpiration) * Application.WorksheetFunction.NormSDist(-d(1))

End Function

Function CallDelta(StockPrice As Double, StrikePrice As Double, TimeToExpiration As Double, Volatility As Double, RiskFreeRate As Double, DividendYield As Double)
    
    CallDelta = Exp(-DividendYield * TimeToExpiration) * Application.WorksheetFunction.NormSDist _
        ((Log(StockPrice / StrikePrice) + (RiskFreeRate - DividendYield + (Volatility ^ 2) / 2) * TimeToExpiration) / (Volatility * Sqr(TimeToExpiration)))
        
End Function

Function PutDelta(StockPrice As Double, StrikePrice As Double, TimeToExpiration As Double, Volatility As Double, RiskFreeRate As Double, DividendYield As Double)
    
    PutDelta = Exp(-DividendYield * TimeToExpiration) * (Application.WorksheetFunction.NormSDist _
        ((Log(StockPrice / StrikePrice) + (RiskFreeRate - DividendYield + (Volatility ^ 2) / 2) * TimeToExpiration) / (Volatility * Sqr(TimeToExpiration))) - 1)

End Function

Function CallTheta(StockPrice As Double, StrikePrice As Double, TimeToExpiration As Double, Volatility As Double, RiskFreeRate As Double, DividendYield As Double)
    
    Dim d(2) As Double

    d(1) = (Log(StockPrice / StrikePrice) + (RiskFreeRate - DividendYield + (Volatility ^ 2) / 2) * TimeToExpiration) / (Volatility * Sqr(TimeToExpiration))
    d(2) = d(1) - Volatility * Sqr(TimeToExpiration)

    CallTheta = -(StockPrice * Exp(-DividendYield * TimeToExpiration) * (1 / (Sqr(2 * Application.Pi())) * Exp(-(d(1) ^ 2) / 2)) * Volatility) / (2 * Sqr(TimeToExpiration)) _
        - (RiskFreeRate * StrikePrice * Exp(-RiskFreeRate * TimeToExpiration) * Application.WorksheetFunction.NormSDist(d(2))) _
        + (DividendYield * StockPrice * Exp(-DividendYield * TimeToExpiration) * Application.WorksheetFunction.NormSDist(d(1)))
        
End Function

Function PutTheta(StockPrice As Double, StrikePrice As Double, TimeToExpiration As Double, Volatility As Double, RiskFreeRate As Double, DividendYield As Double)
    
    Dim d(2) As Double

    d(1) = (Log(StockPrice / StrikePrice) + (RiskFreeRate - DividendYield + (Volatility ^ 2) / 2) * TimeToExpiration) / (Volatility * Sqr(TimeToExpiration))
    d(2) = d(1) - Volatility * Sqr(TimeToExpiration)

    PutTheta = -(StockPrice * Exp(-DividendYield * TimeToExpiration) * (1 / (Sqr(2 * Application.Pi())) * Exp(-(d(1) ^ 2) / 2)) * Volatility) / (2 * Sqr(TimeToExpiration)) _
        + (RiskFreeRate * StrikePrice * Exp(-RiskFreeRate * TimeToExpiration) * Application.WorksheetFunction.NormSDist(-d(2))) _
        - (DividendYield * StockPrice * Exp(-DividendYield * TimeToExpiration) * Application.WorksheetFunction.NormSDist(-d(1)))

End Function

Function CallGamma(StockPrice As Double, StrikePrice As Double, TimeToExpiration As Double, Volatility As Double, RiskFreeRate As Double, DividendYield As Double)
    
    Dim d(1) As Double

    d(1) = (Log(StockPrice / StrikePrice) + (RiskFreeRate - DividendYield + (Volatility ^ 2) / 2) * TimeToExpiration) / (Volatility * Sqr(TimeToExpiration))
    CallGamma = Exp(-DividendYield * TimeToExpiration) * (1 / (Sqr(2 * Application.Pi())) * Exp(-(d(1) ^ 2) / 2)) _
        / (StockPrice * Volatility * Sqr(TimeToExpiration))

End Function

Function PutGamma(StockPrice As Double, StrikePrice As Double, TimeToExpiration As Double, Volatility As Double, RiskFreeRate As Double, DividendYield As Double)
    
    Dim d(1) As Double

    d(1) = (Log(StockPrice / StrikePrice) + (RiskFreeRate - DividendYield + (Volatility ^ 2) / 2) * TimeToExpiration) / (Volatility * Sqr(TimeToExpiration))
    PutGamma = Exp(-DividendYield * TimeToExpiration) * (1 / (Sqr(2 * Application.Pi())) * Exp(-(d(1) ^ 2) / 2)) _
        / (StockPrice * Volatility * Sqr(TimeToExpiration))

End Function

Function CallVega(StockPrice As Double, StrikePrice As Double, TimeToExpiration As Double, Volatility As Double, RiskFreeRate As Double, DividendYield As Double)
    
    Dim d(1) As Double

    d(1) = (Log(StockPrice / StrikePrice) + (RiskFreeRate - DividendYield + (Volatility ^ 2) / 2) * TimeToExpiration) / (Volatility * Sqr(TimeToExpiration))

    CallVega = StockPrice * Exp(-DividendYield * TimeToExpiration) * (1 / (Sqr(2 * Application.Pi())) * Exp(-(d(1) ^ 2) / 2)) * Sqr(TimeToExpiration)

End Function

Function PutVega(StockPrice As Double, StrikePrice As Double, TimeToExpiration As Double, Volatility As Double, RiskFreeRate As Double, DividendYield As Double)
    
    Dim d(1) As Double

    d(1) = (Log(StockPrice / StrikePrice) + (RiskFreeRate - DividendYield + (Volatility ^ 2) / 2) * TimeToExpiration) / (Volatility * Sqr(TimeToExpiration))

    PutVega = StockPrice * (1 / (Sqr(2 * Application.Pi())) * Exp(-(d(1) ^ 2) / 2)) * Sqr(TimeToExpiration)

End Function

Function CallRho(StockPrice As Double, StrikePrice As Double, TimeToExpiration As Double, Volatility As Double, RiskFreeRate As Double, DividendYield As Double)
    
    Dim d(2) As Double

    d(1) = (Log(StockPrice / StrikePrice) + (RiskFreeRate - DividendYield + (Volatility ^ 2) / 2) * TimeToExpiration) / (Volatility * Sqr(TimeToExpiration))
    d(2) = d(1) - Volatility * Sqr(TimeToExpiration)
    CallRho = StrikePrice * TimeToExpiration * Exp(-RiskFreeRate * TimeToExpiration) * Application.WorksheetFunction.NormSDist(d(2))

End Function

Function PutRho(StockPrice As Double, StrikePrice As Double, TimeToExpiration As Double, Volatility As Double, RiskFreeRate As Double, DividendYield As Double)
    
    Dim d(2) As Double

    d(1) = (Log(StockPrice / StrikePrice) + (RiskFreeRate - DividendYield + (Volatility ^ 2) / 2) * TimeToExpiration) / (Volatility * Sqr(TimeToExpiration))
    d(2) = d(1) - Volatility * Sqr(TimeToExpiration)
    PutRho = -StrikePrice * TimeToExpiration * Exp(-RiskFreeRate * TimeToExpiration) * Application.WorksheetFunction.NormSDist(-d(2))

End Function