How to Calculate Black-Scholes Options Using Excel

Black-Scholes returns a European call option.

The Black-Scholes formula is designed to give the variable value of an option on a security, such as a stock. It's calculated based on the price of the stock today, the duration of the option, the exercise price of the option, the annual risk-free rate, the volatility of the stock and the annual percentage of the stock paid in dividends. With those pieces of information, you can construct formulas in Excel to return the Black-Scholes Option value.

Instructions

1. Launch Excel and create a new, blank spreadsheet. In column "A," enter the labels for your data. In A1 type "Data," in A2, "Stock Price," in A3, "Duration," in A4 "Exercise Price," in A5 "Annual Risk-free Rate," in A6 "Annual Volatility" and in A7 type "Dividend Percentage." Type the labels for your formulas: in A9, type "D1," in A10, "D2," in A11, "Call Option" and in A12 "Put Option."

2. Enter the data in column "B." The Stock and Exercise prices should be in dollars, the Duration in years. The Annual Risk-free rate, Annual Volatility and Dividends should all be in percentages.

3. Type the following formula into cell B9:
   =(LN(B2*EXP(-B7*B3)/B4)+(B5+(B6^2)/2)*B3)/B6*SQRT(B3)

4. Type the following formula into cell B10: =B9-B6*SQRT(B3)

5. Type the following formula into cell B11:
   =B2*EXP(-B7*B3)*(NORMDIST(B9,0,1,TRUE)-B4*EXP(-B5*B3)*NORMDIST(B10,0,1,TRUE))

6. Type the following formula into cell B12:
   =B4*EXP(-B5*B3)*(1-(NORM.DIST(B10,0,1,TRUE)))-B2*EXP(-B7*B3)*(1-NORM.DIST(B9,0,1,TRUE))

Tips & Warnings

• This article does not constitute investment advice.

References

• Microsoft Office: How can I use Excel to Implement the Black-Scholes Formula?
• Microsoft Office: NORM.DIST Function
• Microsoft Office: EXP function