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.

FunctionBlackScholes(SpotPriceAsDouble,ExercisePriceAsDouble,TimeToMaturityAsDouble,RiskFreeRateAsDouble,sigmaAsDouble,Optional DividendYieldAsDouble)AsDouble()Dimd1AsDoubleDimd2AsDoubleDimNd1AsDoubleDimNd2AsDoubleDimN_dash_d1AsDoubleDimResultArray()AsDoubleDimgammaAsDoubleDimvegaAsDoubleDimthetaAsDoubleReDimResultArray(10)AsDoubleIf(IsMissing(DividendYield))Thend1=WorksheetFunction.Ln(SpotPrice/ExercisePrice)+((RiskFreeRate+(0.5*(sigma ^ 2)))*TimeToMaturity)Elsed1=WorksheetFunction.Ln(SpotPrice/ExercisePrice)+((RiskFreeRate-DividendYield+(0.5*(sigma ^ 2)))*TimeToMaturity)EndIfd1=d1/(sigma*(TimeToMaturity ^(1/2)))d2=d1-(sigma*(TimeToMaturity ^(1/2)))Nd1=WorksheetFunction.NormSDist(d1)Nd2=WorksheetFunction.NormSDist(d2)'Call ValueIf(IsMissing(DividendYield))ThenResultArray(0)=(SpotPrice*Nd1)-(ExercisePrice*Exp(-RiskFreeRate*TimeToMaturity)*Nd2)ElseResultArray(0)=Exp(-DividendYield*TimeToMaturity)*(SpotPrice*Nd1)-(ExercisePrice*Exp(-RiskFreeRate*TimeToMaturity)*Nd2)EndIf'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 ValueIf(IsMissing(DividendYield))ThenResultArray(5)=Exp(-RiskFreeRate*TimeToMaturity)*ExercisePrice*(1-Nd2)-SpotPrice*(1-Nd1)ElseResultArray(5)=Exp(-RiskFreeRate*TimeToMaturity)*ExercisePrice*WorksheetFunction.NormSDist(-d2)-Exp(-DividendYield*TimeToMaturity)*SpotPrice*WorksheetFunction.NormSDist(-d1)EndIf'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=ResultArrayEndFunction