18.3 Dynamic Chart

К оглавлению 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33
34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50
51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67
68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84
85 86 87 88 89 90 91 92 93 94 95 96 97

If you increased the standard deviation of the stock, what would happen to the price of the call option? If

you increased the time to maturity, what would happen to the price of the call? You can answer these

questions and more by creating an Dynamic Chart using “spinners.” Spinners are up-arrow / down-arrow

buttons that allow you to easily change the inputs to the model with the click of a mouse. Then the

spreadsheet recalculates the model and instantly redraws the model outputs on the graph.

FIGURE 18.3 Spreadsheet model for Black Scholes Option Pricing - Dynamic Chart - Call Option.

How To Build This Spreadsheet Model.

1. Start with the Basics Spreadsheet, Rearrange the Rows, and Add A Switch. Open the

spreadsheet that you created for Black Scholes Option Pricing – Basics and immediately save the

spreadsheet under a new name using the File | Save As command. Add six rows by selecting the

range A11:A16, clicking on Insert | Rows. Select the range A4:B4 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 A13. Enter 1 in cell B4. This will serve as a switch between a call option and a

put option. To highlight which type of option is being graphed, enter =IF($B$4=1,"Call","Put")

in cell I1.

2. Increase Row Height for the Spinners. Select the range A4:A8. Then click on Format | Row |

Height from the main menu. Enter a height of 30 and click on OK.

3. Display the Forms Toolbar. Select View | Toolbars | Forms from the main menu.

4. Create the Spinners. Look for the up-arrow / down-arrow button on the Forms toolbar (which

will display the word “Spinner” if you hover the cursor over it) and click on it. Then draw the

box for a spinner from the upper left corner of cell C4 down to the lower right corner of the cell.

Then a spinner appears in the cell C4. Right click on the spinner (press the right mouse button

while the cursor is above the spinner) and a small menu pops up. Click on Copy. Then select the

cell C5 and click on Paste. This creates an identical spinner in the cell C5. Repeat the process

three times more. Select cell C6 and click on Paste. Select cell C7 and click on Paste. Select cell

C8 and click on Paste. You now have five spinners down column C.

5. Create The Cell Links. Right click on the first spinner in the cell C4 and a small menu pops up.

Click on Format Control and a dialog box pops up. Click on the Control tab, then enter the

cell link D4 in the Cell link edit box and click on OK. Repeat this procedure for the other four

spinners. Link the spinner in cell C5 to cell D5. Link the spinner in cell C6 to cell D6. Link the

spinner in cell C7 to cell D7. Link the spinner in cell C8 to cell D8. Test your spinners by

clicking on the up-arrows and down-arrows of the spinners to see how they change the values in

the linked cells.

6. Create Scaled Inputs. The values in the linked cells are always integers, but they can be scaled

appropriately to the problem at hand. Restrict the value in cell B4 to be either 1 or 0 by entering

=IF(D4>1,1,D4). In cell B5, enter =D5/10+0.001. In cell B6, enter =D6/100. In cell B7, enter

=D7. In cell B8, enter =D8/10+0.001. The additional terms +0.001 in cells B5 and B8, prevent

the scaled value from going to zero when the linked cell goes to zero. When the standard

deviation or a time to maturity literally became zero, then the Black Scholes call and put formulas

blow-up.

7. Create Stock Price Inputs. In the range B13:L13, enter the values 0.01, 20, 40, 60, ..., 200. In

cell M13, enter 0.01. In cell N13, enter =B7. In cell O13, enter =L13.

8. Convert The Input Cell References To Absolute References. In order to convert the input cell

references contained in the formulas in cells B17, B18, B21, and B27 to absolute references. That

is, put $s in front of any references to the input cells in the range B4:B8. When you are done, the

formula in cell B17 will look like

=(LN(B13/$B$7)+($B$6+$B$5^2/2)*$B$8)/($B$5*SQRT($B$8)). Cell B18 will look like

=B17-$B$5*SQRT($B$8). Cell B21 will look like =B13*B19-$B$7*EXP(-$B$6*$B$8)*B20.

Cell B27 will look like =-B13*B25+$B$7*EXP(-$B$6*$B$8)*B26.

9. Copy The Formulas. Select the formulas in the range B17:B27 and copy them to the range

C17:O27.

10. Option Price. Reference the Call Price or the Put Price depending on which type of option is

selected in cell B4. Enter =IF($B$4=1,B21,B27) in cell B14 and copy the cell to the range

C14:L14.

11. Add The Intrinsic Value. If the option was maturing now, rather than later, its payoff would be:

For a call, Max (Stock Price Now - Exercise Price, 0).

For a put, Max (Exercise Price - Stock Price Now, 0).

This is the so-called “Intrinsic Value” of the option. In cell M15, enter the formula

=IF(B4=1,MAX(M13-$B$7,0),MAX($B$7-M13,0)) and copy this cell to the

range N15:O15.

FIGURE 18.4 Option Price and Intrinsic Value.

12. Graph the Option Price and Intrinsic Value. Select the range B13:O15. Next choose Insert |

Chart from the main menu. Select an XY(Scatter) chart type and make other selections to

complete the Chart Wizard. Place the graph in the range E2:J11.

FIGURE 18.5 Spreadsheet model for Black Scholes Option Pricing - Dynamic Chart - Put Option.

Your Dynamic Chart allows you to change Black-Scholes inputs and instantly see the impact on a graph

of the option price and intrinsic value. This allows you to perform instant experiments on the Black-

Scholes option pricing model. Below is a list of experiments that you might want to perform:

What happens when the standard deviation is increased?

What happens when the time to maturity is increased?

What happens when the exercise price is increased?

What happens when the riskfree rate is increased?

What happens when the dividend yield is increased?

What happens when the standard deviation is really close to zero?

What happens when the time to maturity is really close to zero?

Notice that the Black-Scholes option price is usually greater than the payoff you would obtain if the

option was maturing today (the “intrinsic value”). This extra value is called the “Time Value” of the

option. Given your result in the last experiment above, can you explain why the extra value is called the

“Time Value”?