18.4 Implied Volatility

Problem. On December 14, 1999, the S&P 500 index closed at 1,403. European call and put options on

the S&P 500 index with the exercise prices show below traded for the following prices:

Exercise price 1,350 1,375 1,400 1,425 1,450

Call price $81 $66 1/4 $46 $31 $19 1/4

Put price $18 $23 5/8 $30 1/2 $41 1/2 $55

These call options mature on January 21, 2000 (the third Friday of January). The S&P 500 portfolio pays

a continuous dividend yield of 1.18% per year and the annual yield on a Treasury Bill which matures on

January 20th is 5.34% per year. What is the implied volatility of each of these calls and puts? What pattern

do these implied volatilities follow across exercise prices and between calls vs. puts?

Solution Strategy. Calculate the difference between the observed option price and the option price

predicted by the continuous dividend yield version of the Black-Scholes model using a dummy value for

the stock volatility. Have the Excel Solver tool adjust the stock volatility by trial and error until the

difference between the observed price and the model price is equal to zero (within a very small error

tolerance).

FIGURE 18.6 Spreadsheet for Black Scholes Option Pricing - Implied Volatility.

How To Build This Spreadsheet Model.

1. Start with the Continuous Dividend Spreadsheet, Rearrange The Rows, and Add A Switch.

Open the spreadsheet that you created for Black Scholes Option Pricing – Continuous Dividend

and immediately save the spreadsheet under a new name using the File | Save As command. Add

a row by selecting the cell A4 and clicking on Insert | Rows. Select the range A6:B6 and drag

the range (hover the cursor over the lower highlighted line, click on the left mouse button, and

hold it down while you move it) to cell A27. Select the range A7:B10 and drag the range to cell

A6. Enter 1 in cell B4. This will serve as a switch between a call option and a put option.

2. Enter the January 1,350 Call Inputs. Enter the inputs for the January 1,350 Call in the range

B4:B9 and enter the observed option price for the January 1,350 Call the in cell B10.

3. Difference (Observed – Model). The Difference (Observed - Model) is:

Observed Option Price - Model Call Price for a Call Option

Observed Option Price - Model Put Price for a Put Option.

Enter =IF(B4=1,B10-B17,B10-B23) in cell B26.

4. Copy the Entire Column over Nine More Columns. Select the range B4:B27 and copy it to the

range C4:K27.

5. Enter the Options Inputs. In the range B4:F27, enter the inputs for the five call options. In the

range G4:K27, enter the inputs for the five put options. Select the put volatilities in the range

G27:K27 and drag the range down to cell G28.

6. Call Up Excel Solver. From Excel’s main menu, click on Tools and then Solver. (If you don't

see Solver on the Tools Menu, then click on Tools | Add-Ins, check the Solver Add-In box, and

click on OK.)

7. Set-up Solver. In the Solver dialog box, enter cell B26 as the Set Target Cell. In the Equal to

row, click on the option button for Value of and enter 0 in the adjacent box. Enter cell B27 as the

By Changing Cell. See figure below.

FIGURE 18.7 Solver dialog box.

8. Run Solver. Click on the Solve button.

By trial and error, the Solver adjusts the value of the Implied Volatility in cell B27 until the

Difference (Observed – Model) in cell B26 equals zero (within a very small error tolerance). This

results in an implied volatility of 26.03%. Your results may differ by a slight amount (usually

only in the second decimal) depending on Solver's error tolerance.

9. Repeat. Repeat steps 7 and 8 to solve the problems in columns D, E, ..., K.

10. Graph the Implied Volatilities Across Exercise Prices and Option Types. Highlight the range

B7:K7, then hold down the Control button and (while still holding it down) select the range

B27:K28. Next choose Insert | Chart from the main menu. Select an XY(Scatter) chart type

and make other selections to complete the Chart Wizard.

FIGURE 18.8. Graph of the “Scowl” Pattern of Implied Volatilities.

If the market’s beliefs about the distribution of returns of the S&P 500 Index matched the theoretical

distribution of returns assumed by the Black-Scholes model, then all of the implied volatilities would be

the same. From the graph we see this is not the case. The implied volatility pattern declines sharply with

the exercise price and puts have lower implied volatilities than calls. In the ‘70s and ‘80s, the typical

implied volatility pattern was a U-shaped, “Smile” pattern. Since the ‘90s, it is more typical to see a

downward-sloping, “Scowl” pattern.

Problem. On December 14, 1999, the S&P 500 index closed at 1,403. European call and put options on

the S&P 500 index with the exercise prices show below traded for the following prices:

Exercise price 1,350 1,375 1,400 1,425 1,450

Call price $81 $66 1/4 $46 $31 $19 1/4

Put price $18 $23 5/8 $30 1/2 $41 1/2 $55

These call options mature on January 21, 2000 (the third Friday of January). The S&P 500 portfolio pays

a continuous dividend yield of 1.18% per year and the annual yield on a Treasury Bill which matures on

January 20th is 5.34% per year. What is the implied volatility of each of these calls and puts? What pattern

do these implied volatilities follow across exercise prices and between calls vs. puts?

Solution Strategy. Calculate the difference between the observed option price and the option price

predicted by the continuous dividend yield version of the Black-Scholes model using a dummy value for

the stock volatility. Have the Excel Solver tool adjust the stock volatility by trial and error until the

difference between the observed price and the model price is equal to zero (within a very small error

tolerance).

FIGURE 18.6 Spreadsheet for Black Scholes Option Pricing - Implied Volatility.

How To Build This Spreadsheet Model.

1. Start with the Continuous Dividend Spreadsheet, Rearrange The Rows, and Add A Switch.

Open the spreadsheet that you created for Black Scholes Option Pricing – Continuous Dividend

and immediately save the spreadsheet under a new name using the File | Save As command. Add

a row by selecting the cell A4 and clicking on Insert | Rows. Select the range A6:B6 and drag

the range (hover the cursor over the lower highlighted line, click on the left mouse button, and

hold it down while you move it) to cell A27. Select the range A7:B10 and drag the range to cell

A6. Enter 1 in cell B4. This will serve as a switch between a call option and a put option.

2. Enter the January 1,350 Call Inputs. Enter the inputs for the January 1,350 Call in the range

B4:B9 and enter the observed option price for the January 1,350 Call the in cell B10.

3. Difference (Observed – Model). The Difference (Observed - Model) is:

Observed Option Price - Model Call Price for a Call Option

Observed Option Price - Model Put Price for a Put Option.

Enter =IF(B4=1,B10-B17,B10-B23) in cell B26.

4. Copy the Entire Column over Nine More Columns. Select the range B4:B27 and copy it to the

range C4:K27.

5. Enter the Options Inputs. In the range B4:F27, enter the inputs for the five call options. In the

range G4:K27, enter the inputs for the five put options. Select the put volatilities in the range

G27:K27 and drag the range down to cell G28.

6. Call Up Excel Solver. From Excel’s main menu, click on Tools and then Solver. (If you don't

see Solver on the Tools Menu, then click on Tools | Add-Ins, check the Solver Add-In box, and

click on OK.)

7. Set-up Solver. In the Solver dialog box, enter cell B26 as the Set Target Cell. In the Equal to

row, click on the option button for Value of and enter 0 in the adjacent box. Enter cell B27 as the

By Changing Cell. See figure below.

FIGURE 18.7 Solver dialog box.

8. Run Solver. Click on the Solve button.

By trial and error, the Solver adjusts the value of the Implied Volatility in cell B27 until the

Difference (Observed – Model) in cell B26 equals zero (within a very small error tolerance). This

results in an implied volatility of 26.03%. Your results may differ by a slight amount (usually

only in the second decimal) depending on Solver's error tolerance.

9. Repeat. Repeat steps 7 and 8 to solve the problems in columns D, E, ..., K.

10. Graph the Implied Volatilities Across Exercise Prices and Option Types. Highlight the range

B7:K7, then hold down the Control button and (while still holding it down) select the range

B27:K28. Next choose Insert | Chart from the main menu. Select an XY(Scatter) chart type

and make other selections to complete the Chart Wizard.

FIGURE 18.8. Graph of the “Scowl” Pattern of Implied Volatilities.

If the market’s beliefs about the distribution of returns of the S&P 500 Index matched the theoretical

distribution of returns assumed by the Black-Scholes model, then all of the implied volatilities would be

the same. From the graph we see this is not the case. The implied volatility pattern declines sharply with

the exercise price and puts have lower implied volatilities than calls. In the ‘70s and ‘80s, the typical

implied volatility pattern was a U-shaped, “Smile” pattern. Since the ‘90s, it is more typical to see a

downward-sloping, “Scowl” pattern.