BLACK
SCHOLES OPTION
PRICING - Dynamic
Chart
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 1. 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.
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 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.
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 2. 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”?