20.2 Using The Binomial Model
К оглавлению1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 1617 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. Given the same real options project as was analyzed using the Black-Scholes technique,
calculate the project’s NPV using the binomial model. Compare this result to the Black-Scholes result.
Solution Strategy. The project can be viewed as a call option, where the Cost of Development is the
Exercise Price and the Value of the Reserves Now is the Asset Price Now. This call option can be valued
using the Binomial Option Pricing model. Open the spreadsheet that you created for Binomial Option
Pricing - Risk Neutral. Make some changes so that the standard deviation and other inputs can be
translated into corresponding up and down movements of the binomial model. Calculate the NPV Using
Binomial by taking the Binomial Option Value and subtracting the cost of the option (i.e., cost of the
land).
FIGURE 20.2 Spreadsheet for Real Options Using The Binomial Model.
How To Build Your Own Spreadsheet Model.
1. Start with the Risk Neutral Spreadsheet. Open the spreadsheet that you created for Binomial
Option Pricing – Risk Neutral and immediately save the spreadsheet under a new name using the
File | Save As command.
2. Rearrange the Inputs. Select the range A6:B7 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
E6. Select the range A8:B8 and drag the range to cell E5. Select the range A9:B11 and drag the
range to cell A8. Select the range E4:F4 and drag the range to cell E8.
3. Enter the New Inputs. Enter the Full-Scale Real Data inputs in the range B4:B11 as shown in
Figure 2. Enter 30.00% in cell B6 for the Annual Standard Deviation. The value in cell B11
serves as a switch between the Discrete and Continuous Annualization Conventions. To highlight
which annualization convention is in use, enter =IF($B$11=1,"Discrete","Continuous") in cell
E2.
4. Calculate the New Outputs. Calculate four new "per period" outputs:
o Time / Period = (Time To Maturity) / (Number of Periods). Enter =B9/B10 in cell F4.
o Riskfree Rate / Period = (Annual Riskfree Rate) * (Time / Period) under the discrete
annualization convention or = exp[(Annual Riskfree Rate) * (Time / Period)] -1 under the
continuous annualization convention. Enter =IF($B$11=1,B7*F4,EXP(B7*F4)-1) in cell
F5.
o Up Movement / Period = (Annual Standard Deviation) * Square Root (Time / Period)
under the discrete annualization convention or = exp[(Annual Standard Deviation) *
Square Root (Time / Period)] -1 under the continuous annualization convention. Enter
=IF($B$11=1,B6*SQRT(F4),EXP(B6*SQRT(F4))-1) in cell F6.
o Down Movement / Period = -(Annual Standard Deviation) * Square Root (Time /
Period) under the discrete annualization convention or = exp[-(Annual Standard
Deviation) * Square Root (Time / Period)] -1 under the continuous annualization
convention. Enter =IF($B$11=1,-B6*SQRT(F4),EXP(-B6*SQRT(F4))-1) in cell F7.
5. NPV Using Binomial. The formula for NPV Using Binomial = Call Value - Real Option Cost.
Enter =B28-B12 in cell B38.
The NPV Using Binomial is $0.22 million. This is very close to the NPV Using Black-Scholes of $0.20
million. If you expanded the number of steps in the binomial model to 50 or 100 steps, then the difference
between the two techniques would go away.
Looking at the range J28:J36, we see that in 4 of the 9 states of nature the value of the reserves is high
enough to lead to oil development and thus profit is positive. In 5 of the 9 states of nature the value of
reserves is low enough that the oil is not developed and thus the profit is zero.
Problem. Given the same real options project as was analyzed using the Black-Scholes technique,
calculate the project’s NPV using the binomial model. Compare this result to the Black-Scholes result.
Solution Strategy. The project can be viewed as a call option, where the Cost of Development is the
Exercise Price and the Value of the Reserves Now is the Asset Price Now. This call option can be valued
using the Binomial Option Pricing model. Open the spreadsheet that you created for Binomial Option
Pricing - Risk Neutral. Make some changes so that the standard deviation and other inputs can be
translated into corresponding up and down movements of the binomial model. Calculate the NPV Using
Binomial by taking the Binomial Option Value and subtracting the cost of the option (i.e., cost of the
land).
FIGURE 20.2 Spreadsheet for Real Options Using The Binomial Model.
How To Build Your Own Spreadsheet Model.
1. Start with the Risk Neutral Spreadsheet. Open the spreadsheet that you created for Binomial
Option Pricing – Risk Neutral and immediately save the spreadsheet under a new name using the
File | Save As command.
2. Rearrange the Inputs. Select the range A6:B7 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
E6. Select the range A8:B8 and drag the range to cell E5. Select the range A9:B11 and drag the
range to cell A8. Select the range E4:F4 and drag the range to cell E8.
3. Enter the New Inputs. Enter the Full-Scale Real Data inputs in the range B4:B11 as shown in
Figure 2. Enter 30.00% in cell B6 for the Annual Standard Deviation. The value in cell B11
serves as a switch between the Discrete and Continuous Annualization Conventions. To highlight
which annualization convention is in use, enter =IF($B$11=1,"Discrete","Continuous") in cell
E2.
4. Calculate the New Outputs. Calculate four new "per period" outputs:
o Time / Period = (Time To Maturity) / (Number of Periods). Enter =B9/B10 in cell F4.
o Riskfree Rate / Period = (Annual Riskfree Rate) * (Time / Period) under the discrete
annualization convention or = exp[(Annual Riskfree Rate) * (Time / Period)] -1 under the
continuous annualization convention. Enter =IF($B$11=1,B7*F4,EXP(B7*F4)-1) in cell
F5.
o Up Movement / Period = (Annual Standard Deviation) * Square Root (Time / Period)
under the discrete annualization convention or = exp[(Annual Standard Deviation) *
Square Root (Time / Period)] -1 under the continuous annualization convention. Enter
=IF($B$11=1,B6*SQRT(F4),EXP(B6*SQRT(F4))-1) in cell F6.
o Down Movement / Period = -(Annual Standard Deviation) * Square Root (Time /
Period) under the discrete annualization convention or = exp[-(Annual Standard
Deviation) * Square Root (Time / Period)] -1 under the continuous annualization
convention. Enter =IF($B$11=1,-B6*SQRT(F4),EXP(-B6*SQRT(F4))-1) in cell F7.
5. NPV Using Binomial. The formula for NPV Using Binomial = Call Value - Real Option Cost.
Enter =B28-B12 in cell B38.
The NPV Using Binomial is $0.22 million. This is very close to the NPV Using Black-Scholes of $0.20
million. If you expanded the number of steps in the binomial model to 50 or 100 steps, then the difference
between the two techniques would go away.
Looking at the range J28:J36, we see that in 4 of the 9 states of nature the value of the reserves is high
enough to lead to oil development and thus profit is positive. In 5 of the 9 states of nature the value of
reserves is low enough that the oil is not developed and thus the profit is zero.