18.1 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 riskfree 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 18.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 d1 formula is ln / 2 / 2/ RF P X k ⋅t ⋅t . In cell B11,

enter

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

Thed2 formula is 1d −__________t . In cell B12, enter

=B11-B5*SQRT(B8).

3. Cumulative Normal Formulas. Enter 1 N d using the cumulative normal function

NORMSDIST in cell B13 =NORMSDIST(B11). Copy the cell B13 to cell B14 or enter

2 N d using the cumulative normal function NORMSDIST in cell B14 =NORMSDIST(B12).

4. European Call Price Formula. The Black-Scholes call formula is

1 2

V PN d −Xe−kRFtN d . 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 Binomial 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 N −d1 using the cumulative normal function

NORMSDIST in cell B19

=NORMSDIST(B17)

Copy the cell B19 to cell B20 or enter 2 N −d using the cumulative normal function

NORMSDIST in cell B20

=NORMSDIST(B18)

7. European Put Price Formula. The Black-Scholes put formula is

1 2

Put −PN −d Xe−kRFtN −d . 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 Binomial 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.

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 riskfree 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 18.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 d1 formula is ln / 2 / 2/ RF P X k ⋅t ⋅t . In cell B11,

enter

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

Thed2 formula is 1d −__________t . In cell B12, enter

=B11-B5*SQRT(B8).

3. Cumulative Normal Formulas. Enter 1 N d using the cumulative normal function

NORMSDIST in cell B13 =NORMSDIST(B11). Copy the cell B13 to cell B14 or enter

2 N d using the cumulative normal function NORMSDIST in cell B14 =NORMSDIST(B12).

4. European Call Price Formula. The Black-Scholes call formula is

1 2

V PN d −Xe−kRFtN d . 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 Binomial 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 N −d1 using the cumulative normal function

NORMSDIST in cell B19

=NORMSDIST(B17)

Copy the cell B19 to cell B20 or enter 2 N −d using the cumulative normal function

NORMSDIST in cell B20

=NORMSDIST(B18)

7. European Put Price Formula. The Black-Scholes put formula is

1 2

Put −PN −d Xe−kRFtN −d . 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 Binomial 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.