Message Font: Serif | Sans-Serif

No. of Recommendations: 1
I managed to put this infamous equation into Excel. I know that my version works. However, the answer I get is slightly different from the book I got it from, probably the built-in Excel function for Gaussian distribution is slightly off. I think I have successfully translated my spreadsheet to the below, but no guarantees.

cell A = underlying asset price
cell B = strike price
cell C = risk free interest rate
cell D = sqrt of asset price variance
cell E = time in years or fraction
cell F = LN(cell A/cell B)
cell G = (cell C + (0.5*cell D*cell D))*cell E
cell H = cell D*SQRT(cell E)
cell I = (cell F + cell G)/cell H
cell J = cell I - (cell D*SQRT(cell E))
cell K = hedge ratio = NORMSDIST(cell I)
cell L = NORMSDIST(cell J)
cost of call option = (cell A*NORMSDIST(cell I))-(cell B*cell L*EXP(-1*cell C*cell E))
cost of put option = ((1-NORMSDIST(cell J))*cell B*EXP(-cell C*cell E))-(cell A*(1-cell K))

math check: if this is correct,
asset=55, strike=52, interest=0.10, sqrt of variance=0.33, Time=0.4