Black-Scholes Pricing and Greeks in VBA

[RonAkke]

Hi all,

Here are functions which will calculate the Black-Scholes call value as well as all of it's greeks in VBA (delta, gamma, vega, theta and rho).

The functions for the Black-Scholes put price and greeks are available here. Enjoy!

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 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 CallTheta(StockPrice As Double, StrikePrice As Double,  TimeToExpiration As Double, Volatility As Double, RiskFreeRate As  Double, DividendYield As Double)

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