20.2 Using The Binomial Model

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