Black-Scholes Put Price in VBA

Here is a VBA function that easily allows you to calculate the Black-Scholes price on any put option.



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