20.3 Sensitivity to Std Dev

К оглавлению 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

Problem. We know that an increase in the standard deviation of an underlying asset makes regular call

and put options more valuable. In the real option setting of the oil development project, what would

happen if you increased asset value standard deviation to the NPV Using Black-Scholes, NPV Ignoring

Option, and NPV Using Binomial?

FIGURE 20.3 A Spreadsheet of the Sensitivity to Std Dev.

How To Build Your Own Spreadsheet Model.

1. Start with the Real Options - Using Black Scholes Spreadsheet. Open the spreadsheet that you

created for Real Options - Using Black Scholes and immediately save the spreadsheet under a

new name using the File | Save As command. Open the spreadsheet that you created for Real

Options - Using The Binomial Model and keep it open. You will build a cell link to it and from it.

2. Create A List of Input Values, Two Output Formulas, And A Standard Deviation

Connection. Create a list of input values for the Asset Value Standard Deviation (0.1%, 10.0%,

20.0%, etc.) in the range C29:G29. Create three output formulas. One that references the NPV

Using Black Scholes by entering the formula =B19 in cell B30. Another that references the NPV

Ignoring Option by entering the formula =B25 in cell B31. A third that references the NPV

Using Binomial by entering the formula =[Realobin.xls]Sheet1!$B$38 in cell B32, where the

[Realobin.xls] is the name of the Excel file which contains the Real Options - Using The

Binomial Model spreadsheet. We need to connect the standard deviation on the two spreadsheets

so that the Data Table will work across both spreadsheets. In the Real Options - Using The

Binomial Model spreadsheet, enter =[Realostd.xls]Sheet1!$B$5 in cell B6.

3. Data Table. Select the range B29:G32 for the Data Table. This range includes both the list of

input values at the top of the data table and the three output formulas on the side of the data table.

Then choose Data | Table from the main menu and a Table dialog box pops up. Enter the Asset

Value Standard Deviation cell B5 in the Row Input Cell and click on OK.

4. Graph the Sensitivity Analysis. Highlight the range C29:G32 and then choose Insert | Chart

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

the Chart Wizard.

Both the data table and the graph show that NPV Using Black Scholes and NPV Using Binomial are

nearly identical over a wide range of Asset Value Standard Deviations with only a penny or two

difference. Figure 1 shows that an increase in Asset Value Standard Deviation causes both the NPV Using

Black Scholes and the NPV Using Binomial to increase, but leaves the NPV Ignoring Option unaffected.

Why does the increase in Asset Value Standard Deviation have such different effects? Essentially, the two

option-based techniques take into account the option to develop the oil only if it is profitable and this

option ncreases in value as standard deviation goes up. By contrast, the NPV Ignoring Option approach

precommits to develop the oil no matter what and thus ignores the option. Indeed, Asset Value Standard

Deviation is not even an input in the NPV Ignoring Option calculation. This lack of sensitivity to Asset

Value Standard Deviation by the NPV Ignoring Option technique is an error. Both the NPV Using Black

Scholes and the NPV Using Binomial show the correct amount of sensitivity to Asset Value Standard

Deviation.