BLACK SCHOLES OPTION PRICING - Basics

 

Problem. On December 13, 1999, the stock price of Amazon.com was $102.50, the continuous annual standard deviation was 86.07%, the yield on a risk-free Treasury Bill maturing on April 20th was 5.47%, the exercise price of an April 100 European call on Amazon.com was $100.00, the exercise price of an April 100 European put on Amazon.com was $100.00, and the time to maturity for both April 21st maturity options was 0.3556 years. What are the current prices of the call and put? 

 

FIGURE 1.  Spreadsheet for Black Scholes Option Pricing - Basics.

 

 

How To Build This Spreadsheet Model.

 

1.       Inputs. Enter the inputs described above into the range B4:B8.

 

2.       d1 and d2 Formulas. The formula is . In cell B11, enter

 

                         =(LN(B4/B7)+(B6+B5^2/2)*B8)/(B5*SQRT(B8))

 

       The  formula is . In cell B12, enter

 

                         =B11-B5*SQRT(B8)

 

3.       Cumulative Normal Formulas. Enter  using the cumulative normal function NORMSDIST in cell B13

 

                         =NORMSDIST(B11)

 

       Copy the cell B13 to cell B14 or enter  using the cumulative normal function NORMSDIST in cell B14

 

                         =NORMSDIST(B12)

 

4.       European Call Price Formula. The Black-Scholes call formula is . In cell B15, enter

 

                         =B4*B13-B7*EXP(-B6*B8)*B14

 

We see that the Black-Scholes model predicts an European call price of $22.60. This is only one cent different that what the Binominal Option Pricing - Full-Scale Real Data model predicts given identical inputs! Now let's do the put.

 

 

5.       -d1 and -d2 Formulas. For the labels, enter '-d1 in A17 and '-d2 A18. The ' tells Excel that it is a label, not a formula. For the two put formula terms, they are just opposite in sign from their call formula counterparts. Enter =-B11 in B17 and =-B12 in B18.

 

6.       Cumulative Normal Formulas. Enter  using the cumulative normal function NORMSDIST in cell B19

 

                         =NORMSDIST(B17)

 

       Copy the cell B19 to cell B20 or enter  using the cumulative normal function NORMSDIST in cell B20

 

                         =NORMSDIST(B18)

 

7.       European Put Price Formula. The  Black-Scholes put formula is . In cell B21, enter

 

                         =-B4*B19+B7*EXP(-B6*B8)*B20

 

We see that the Black-Scholes model predicts an European put price of $18.17. This is only one cent different that what the Binominal Option Pricing - Full-Scale Real Data model predicts given identical inputs! The advantage of the Black Scholes model (and its natural extensions) is that it is quick and easy to calculate, but the disadvantage is that it is limited to a narrow range of derivatives.