17.4 Full-Scale Real Data

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

The binomial model can be used to price real-world European calls and puts. Further, the Binomial Tree

Risk Neutral method can be extended to price any type of derivative security (European vs. American vs.

other, on any underlying asset(s), with any underlying dividends or cash flows, with any derivative

payoffs at maturity and/or payoffs before maturity). Indeed, it is one of the most popular techniques on

Wall Street for pricing and hedging derivatives.

Problem Using Real Data. On December 13, 1999, the stock price of Amazon.com was $102.50, the

yield on a riskfree Treasury Bill maturing on April 20, 2000 was 5.47%, the exercise price of an April 100

European call on Amazon.com was $100.00, the exercise price of an April 100 European put on

Amazon.com was $100.00, and the time to maturity for both April 21, 2000 maturity options was 0.3556

years. What is the annual standard deviation of Amazon.com stock? What are the current prices of the call

and put under the continuous annualization convention? What are the current prices of the call and put

under the discrete annualization convention?

Solution Strategy. Collect Amazon.com's historical stock prices from Yahoo Finance! and calculate the

annual standard deviation. Use the annual standard deviation and the annual riskfree rate to calculate the

up movement / period, down movement / period, and riskfree rate / period. Extend the Binomial Option

Pricing - Risk Neutral model to full-scale (50 periods) in order to achieve greater price accuracy.

FIGURE 17.9 Spreadsheet Model of Binomial Option Pricing - Estimating Volatility.

How To Build This Spreadsheet Model.

1. Collect Historical Stock Price Data. Go to Yahoo Finance! (quote.yahoo.com), enter AMZN

(the ticker symbol for Amazon.com) in the Get Quotes box, click on Chart, at the bottom of the

page click on Other: historical quotes, adjust the start date if you want more than three months

of data, click on Download Spreadsheet Format, and save the csv file. Launch Excel and open

the csv file.

2. Calculate Discrete and Continuous Returns. There are two conventions for calculating stock

returns. A simple percent change yields the Discrete Return = [(Price on date t) - (Price on date t-

1)] / (Price on date t-1). Enter =(E4-E5)/E5 in cell G4 and copy it down. The Continuous Return

= LN[(Price on date t) / (Price on date t-1)]. Enter =LN(E4/E5) in cell H4 and copy it down.

3. Calculate the Daily and Annual Standard Deviation. Use Excel's function STDEV to calculate

the sample standard deviation of daily discrete returns and daily continuous returns. Enter

=STDEV(G4:G68) in cell G71 and copy the cell to H71. Convert the daily standard deviation to

annual standard deviation by multiplying by the square root of the number of trading days in the

year. By way of explanation, the stock variance is proportion to the units of time. Hence, the

stock standard deviation is proportional to the square root of the units of time. The empirical

evidence shows that is better to use trading days rather than calendar days, since trading days is a

better predictor of stock volatility than calendar days. There are 252 trading days in the year, so

we multiply by the square root of 252. Enter =G71*SQRT(252) in cell G72 and copy it to the

cell H72.

We find that Amazon.com's annual standard deviation is 90.23% based on discrete returns and is 86.07%

based on continuous returns.

FIGURE 17.10 Spreadsheet Model of Binomial Option Pricing - Full-Scale Real Data -

Call.

4. Start with the Risk Neutral Spreadsheet and Freeze Panes. 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. It will be helpful for navigation purposes to lock in

both column titles and row titles. Select cell G16 and click on Window | Freeze Panes.

5. 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.

6. Enter the New Inputs. Enter the Full-Scale Real Data inputs in the range B4:B11 as shown in

Figure 2. The value in cell B11 serves as a switch between the Discrete and Continuous

Annualization Conventions. To accommodate both annualization conventions, enter

=IF(B11=1,90.23%,86.07%) in cell B6 for the Annual Standard Deviation. To highlight which

annualization convention is in use, enter =IF($B$11=1,"Discrete","Continuous") in cell E2.

7. 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.

The up movement / period and down movement / period are calibrated to correspond to

the stock's annual standard deviation. It is not necessary to calibrate them to the stock's

expected return.2

8. Extend The Periods and Time to 50 Periods. Select the range B14:C14, grab the fill bar (hover

the mouse over the lower-right corner of the selection - when it turns to a "+" sign, click the left

mouse button), and fill in the range D14:AZ14. Select the cell B15 and copy it to the range

C15:AZ15.

9. Extend The Stock Price Tree to 50 Periods. Add some rows to make space between the Stock

Price Tree and the Option Price Tree. Select the range A26:A67 and click on Insert | Rows.

Then, copy cell C17 to the 50-by-51 range C17:AZ67. A binomial tree will form in the triangular

area from C17 to AZ17 to AZ67.

2 At full-scale (50 periods), the binomial option price is very insensitive to the expected return of

the stock. For example, suppose that you calibrated this Amazon.com case to an annual expected

return of 10%. Just add 10%*F4 to the formulas for the up and down movements / period. So the

up movement / period in cell F6 would become

=IF($B$11=1,10%*F4+B6*SQRT(F4),EXP(10%*F4+B6*SQRT(F4))-1) and the down

movement / period in cell F7 would become =IF($B$11=1,10%*F4-

B6*SQRT(F4),EXP(10%*F4-B6*SQRT(F4))-1). This changes the option price by less than

1/100th of one penny! In the (Black Scholes) limit as the number of (sub)periods goes to infinity,

the option price becomes totally insensitive to the expected return of the stock. Because of this

insensitivity, the conventions for calculating the up movement / period and down movement /

period ignore the expected return of the stock.

FIGURE 17.11 Spreadsheet of Binomial Option Pricing - Full-Scale Real Data - Call (Continued).

10. Extend The Option Payoffs At Maturity to 50 Periods. Copy the old payoffs at maturity

starting in cell J69 and to the new payoffs at maturity range AZ69:AZ119.

11. Extend The Option Price Tree to 50 Periods. Copy cell B69 to the range B69:AY118. A

binomial tree will form in the triangular area from B69 to AZ69 to AZ119.

We see that the Full-Scale Real Data model predicts an European call price of $22.61. This is only one

cent different that what the Black-Scholes model predicts given identical inputs! Now let's check the put.

FIGURE 17.12 Spreadsheet Model of Binomial Option Pricing - Full-Scale Real Data - Put Option.

12. Put Option. Enter 0 in cell B4.

FIGURE 17.13 Spreadsheet of Binomial Option Pricing - Full-Scale Real Data - Put (Continued).

We see that the Full-Scale Real Data model predicts an European put price of $18.18. This is only one

cent different that what the Black-Scholes model predicts given identical inputs! The accuracy of the

binomial model can be increased to any desired degree by increasing the number of periods. Whereas the

Black Scholes model (and its natural extensions) is limited to a narrow range of derivatives, the Binomial

Option Pricing model can be extended to price any derivative security (any type, any underlying asset(s),

any underlying cash flows, any derivative payoffs).