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 pricecell B = strike pricecell C = risk free interest ratecell D = sqrt of asset price variancecell E = time in years or fractioncell F = LN(cell A/cell B)cell G = (cell C + (0.5*cell D*cell D))*cell Ecell H = cell D*SQRT(cell E)cell I = (cell F + cell G)/cell Hcell 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.4correct answer: call=7.17, put 2.13when I manually look up the NORMDIST, I get the correct answer. Using Excel’s built in function, I get slightly different numbers.
Best Of |
Favorites & Replies |
Start a New Board |
My Fool |
BATS data provided in real-time. NYSE, NASDAQ and NYSEMKT data delayed 15 minutes.
Real-Time prices provided by BATS. Market data provided by Interactive Data.
Company fundamental data provided by Morningstar. Earnings Estimates, Analyst Ra