How to calculate Black-Scholes options using Excel

Written by stephen byron cooper
• Share
• Tweet
• Share
• Pin
• Email

The Black-Scholes formula is a system for calculating the value of share options. It is sometimes called the Black-Scholes-Merton formula because it was defined by Fischer Black, Myron Scholes, and Robert Merton. Creating a calculation of options values in Excel requires you to set up a model. This was you can enter different figures and recalculate option values for any share.

Skill level:
Moderately Challenging

Instructions

1. 1

Open Excel with a new spreadsheet. Widen column A to be three times the standard column width. Click the “B” button in the “Home” tab to make the text bold. Enter the titles “Calculation Inputs” in cell A3. In A4, write “Current price (P)” and write “Exercise price (EX)” in A5. Write “Number of periods to exercise in years (t)” in A6 and “Compound risk-free interest rate (rf)” in A7. Write “Volitility (annualised) (v)” in A8.

2. 2

Highlight cells C4 and C5 and right click the mouse. Select “Format Cells” from the right-click menu. Select the “Accounting” format category, specify two decimal places and Select “£ English (UK)” from the drop-down list of symbols. Press the “OK” button. Click in C6. Press the right mouse button and select “Format Cells” again. Specify “Number” with no decimal places and press the “OK” button. Highlight C7 and C8 and press the right mouse button. Select “Format Cells” again. Specify “Percentage” as the category and set “Decimal Places” to 2. Press the “OK” button.

3. 3

Click in cell A11. Click the “B” button in the “Home” tab and write “Calculations.” Press “Enter.” Write “Present Value of Exercise Price (PV(EX))” in A12 and “vt^.5” in A13. Write “d1” in A14, “D2” in A15, “Delta N(d1)” in A16 and “N(d2)PV(EX)” in A17.

4. 4

Highlight C12 to C17 and press the right mouse button. Select “Format Cells” and specify the “Number” category with 4 decimal places. Press the OK button. Highlight cells A19 to C20. Press the fill button on the “Home” tab and select a light colour. These cells are where the results will appear. Write “Call Value” in A19 and “Put Value” in A20. Highlight C19 and C20. Right click, select “Format Cells” and choose the “Accounting” category. Set decimal places to 4 and s Select “£ English (UK)” as a symbol. Click the “OK” button.

5. 5

Click in C12. Enter the formula: =++C5EXP(-C7C6). Click in C13. Enter the formula: =+C8C6^0.5. IN C14 enter the formula: =++(LN(C4/C5)+(C7+C8C8/2)C6)/(C8C6^0.5). In C15 enter: =+C14-C13. In C16 enter: =NORMDIST(C14,0,1,TRUE). IN C17 enter: =NORMDIST(C15,0,1,TRUE)C12. In C19 enter: =+C16C4-C17. In C20 enter: =+C19+C12-C4

6. 6

Put different figures into the C column fields in the “Calculation Input” section to see option values in the results section. Save the file because you now have a model for calculating the Black-Scholes formula.

Don't Miss

• All types
• Articles
• Slideshows
• Videos
Sort:
• Most relevant
• Most popular
• Most recent

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.