17.2 Multi-Period

К оглавлению 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. The current stock price of Energy Systems is $60.00, the potential up movement / period of

Energy Systems' stock price is 10.00%, the potential down movement / period of Energy Systems' stock

price is -5.00%, the riskfree rate is 0.5% per period, the exercise price of an one-period, European call

option on Energy Systems is $65.00, the exercise price of an one-period, European put option on Energy

Systems is $65.00, the time to maturity for both options is 2.00 years, and the number of periods for both

options is 8. What are the current prices of the call and put?

Solution Strategy. First, build a multi-period tree of stock prices. Second, calculate call and put payoffs

at maturity. Third, build the multi-period trees of the shares of stock and money borrowed to create a

replicating portfolio that replicates the option period by period. Finally, build a multi-period tree of the

value of the replicating portfolio and, in the absence of arbitrage, this will be value of the option.

FIGURE 17.3 Spreadsheet Model of Binomial Option Pricing - Multi-Period - Call Option.

How To Build This Spreadsheet Model.

1. Start with the Single Period Spreadsheet, Enter the Inputs, and Delete Rows. Open the

spreadsheet that you created for Binomial Option Pricing – Single Period and immediately save

the spreadsheet under a new name using the File | Save As command. Enter the new inputs into

the range B5:B11. Delete rows 20 through 29 by selecting the range A20:A29, clicking on Edit,

Delete, selecting the Entire Row radio button on the Delete dialog box, and clicking on OK.

2. Enter Periods and Time. Enter the periods 0, 1, 2, ... , 8 in cells B14 and J14. The formula for

Time = Time To Maturity * (Period / Number of Periods). Enter =$B$10*(B14/$B$11) in cell

B15 and copy the cell to the range C15:J15.

3. The Stock Price Tree. As in the single period case, the Period 0 Stock Price is equal to the

Stock Price Now. Turning to the rest of the Stock Price Tree, we want to create the entire tree

with one copy command to a square range. To do this we have to determine whether a cell in the

square area is on the tree or off the tree. Further, there are two different formulas to use on the

tree (a Down Price vs. an Up Price). Hence, there are three possibilities:

o When the cell to the left and the cell diagonally to the upper left are both blank, then

show a blank.

o When the cell to the left is blank and the cell diagonally to the upper left has a number,

then you are on the lower edge of the triangle so calculate the Down Price = (Stock Price

in the Upper Left) * (1 + Down Movement / Period)

o When both cells have numbers, then calculate the Up Price = (Stock Price to the Left) *

(1 + Up Movement / Period) Enter

=IF(B17="",IF(B16="","",B16*(1+$B$7)),B17*(1+$B$6)) in cell C17 and copy this

cell to the 9-by-9 square range C17:J25. The nested IF statements cause a binomial tree

to form in the triangular area from C17 to J17 to J25. Incidentally, the same procedure

could create a binomial tree for any number of periods. For example, if you wished to

create a 20 period model, then you would simply copy this cell to a 20-by-21 square

range. In the Binomial Option Pricing Full-Scale Real Data spreadsheet model, we will

exploit this feature to create a 50 period model!

4. Option Payoffs At Maturity. Copy the option type indicator from cell G1 and copy this cell to

cell A27. The formulas for option payoffs are:

o For a Call, the Payoff At Maturity = Max (Stock Price At Maturity – Exercise Price, 0).

o For a Put, the Payoff At Maturity = Max (Exercise Price – Stock Price Maturity, 0).

Enter =IF($B$4=1,MAX(J17-$B$9,0),MAX($B$9-J17,0)) in cell J27 and copy this

cell to the range J28:J35.

FIGURE 17.4 Spreadsheet of Binomial Option Pricing - Multi-Period - Call (Continued).

5. The Stock Shares Bought (Sold) Tree. At each point in the 8-by-8 square range, you need to

determine if you are on the tree or off the tree. There are two possibilities:

o When the corresponding cell in the Stock Price area is blank, then show a blank.

o When the corresponding cell in the Stock Price area has a number, then use the Hedge

Ratio = (Option Up Payoff – Option Down Payoff) / (Stock Up Price – Stock Down

Price).

Enter =IF(C28="","",(C27-C28)/(C17-C18)) in cell B40 and copy this cell to the 8-

by-8 square range B40:I47. A binomial tree will form in the triangular area from B40 to

I40 to I47. Again the same procedure could create a binomial tree for any number of

periods.

6. The Money Lent (Borrowed) Tree. At each point in the 8-by-8 square range, you need to

determine if you are on the tree or off the tree. There are two possibilities:

o When the corresponding cell in the Stock Price area is blank, then show a blank.

o When the corresponding cell in the Stock Price area has a number, then calculate the

amount of Money Lent (Borrowed) = (Call Down Payoff –Hedge Ratio * Stock Down

Price) / (1 + Riskfree Rate / Period). Enter

=IF(C28="","",(C28-B40*C18)/(1+$B$8)) in cell B50 and copy this cell to the 8-by-8

square range B50:I57. A binomial tree will form in the triangular area from B50 to I50 to

I57. Again the same procedure could create a binomial tree for any number of periods.

7. The Option Price Tree. At each point in the 8-by-8 square range (excluding column J containing

option payoffs at maturity), you need to determine if you are on the tree or off the tree. There are

two possibilities:

o When the corresponding cell in the Stock Price area is blank, then show a blank.

o When the corresponding cell in the Stock Price area has a number, then (in the absence of

arbitrage) the Option Price At Each Node = Price Of The Corresponding Replicating

Portfolio = Number of Shares of Stock * Stock Price + Money Borrowed.

Enter =IF(C28="","",B40*B17+B50) in cell B27 and copy this cell to the 8-by-9 range

B27:I34. Be sure not to copy over column J containing option payoffs at maturity. A

binomial tree will form in the triangular area from B27 to J27 to J35. Again the same procedure

could create a binomial tree for any number of periods.

We see that the Binomial Option Pricing model predicts an eight-period European call price of

$3.93. Now let's check the put.

FIGURE 17.5 Spreadsheet Model of Binomial Option Pricing - Multi-Period - Put.

8. Put Option. Enter 0 in cell B4.

We see that the Binomial Option Pricing model predicts an eight-period European put price of $6.39.

FIGURE 17.6 Spreadsheet of Binomial Option Pricing - Multi-Period - Put (Continued).

As in the single period case, replicating a Call option requires Buying Shares of Stock and Borrowing

Money, whereas a Put option requires Selling Shares of Stock and Lending Money. Notice that the

quantity of Money Borrowed or Lent and the quantity of Shares Bought or Sold changes over time and

differs for up nodes vs. down nodes. This process of changing the replicating portfolio every period based

on the realized up or down movement in the underlying stock price is called dynamic replication.

Price accuracy can be increased by subdividing the interval into more periods (15, 30, etc.). Typically,

from 30 subperiods to 100 periods are required in order to achieve price accuracy to the penny.