Principles of Finance with Excel (Основы финансов c Excel)

Подождите немного. Документ загружается.

PFE, Chapter 10: What is risk? page 13

ABC D E F G

THE PRICE OF THE TREASURY BILL THROUGHOUT THE YEAR

Date Bill price

1-Jan-01 953.04

1-Feb-01 958.08

1-Mar-01 964.59

1-Apr-01 970.46

1-May-01 974.95

1-Jun-01 979.23

1-Jul-01 981.92

1-Aug-01 985.56

1-Sep-01 990.62

1-Oct-01 994.14

1-Nov-01 996.36

1-Dec-01 998.12

1-Jan-02 1,000.00

Treasury Bill Price over 1 Year

920

930

940

950

960

970

980

990

1,000

1,010

Jan-01

Feb-01

Mar-01

Apr-01

May-01

Jun-01

Jul-01

Aug-01

Sep-01

Oct-01

Nov-01

Dec-01

Jan-02

Date

We use this monthly price data to compute some returns.

What ex-post rate of return would you have earned if you’d sold the Treasury bill

early?

Suppose you had sold the T-bill on 1 May 2001 for $974.95. What would you have

earned? A relatively simple calculation provides the answer. The monthly rate of return—the

ex-post return—is defined by:

101

974.95

1.0057

953.04

Price on May

ex post monthly rate of return

Initial price on Jan













The exponent of ¼ is there because of the 4 month interval between January and May. If we

raise this to the 12

power, we will get 1+annual rate of return:

PFE, Chapter 10: What is risk? page 14

ABC

ANNUALIZED EX-POST RETURN, JAN-MAY

Bought, 1 January 2001 953.04

Sold, 1 May 2001 974.95

Monthly return 0.57% <-- =(B4/B3)^(1/4)-1

Annualized return 7.06% <-- =(1+B5)^12-1

If, instead, you had sold the Treasury bill on April 1, one month earlier, you would have

made 7.51% in annual terms:

ABC

ANNUALIZED EX-POST RETURN, JAN-APRIL

Bought, 1 January 2001 953.04

Sold, 1 April 2001 970.46

Monthly return 0.61% <-- =(B4/B3)^(1/3)-1

Annualized return 7.51% <-- =(1+B5)^12-1

We can do this exercise for each of the months from February – December:

PFE, Chapter 10: What is risk? page 15

ABC D

EX-POST INTEREST ON THE TREASURY BILL

Date Bill price

Annualized return if

sold at beginning

of month

Jan-01 953.04

Feb-01 958.08 6.53% <-- =(B5/$B$4)^(12/(MONTH(A5)-MONTH($A$4)))-1

Mar-01 964.59 7.50% <-- =(B6/$B$4)^(12/(MONTH(A6)-MONTH($A$4)))-1

Apr-01 970.46 7.51%

May-01 974.95 7.06%

Jun-01 979.23 6.72%

Jul-01 981.92 6.15%

Aug-01 985.56 5.92%

Sep-01 990.62 5.97%

Oct-01 994.14 5.79%

Nov-01 996.36 5.48%

Dec-01 998.12 5.17%

Jan-02 1,000.00 4.93%

Annualized Ex-Post Interest

4.0%

4.5%

5.0%

5.5%

6.0%

6.5%

7.0%

7.5%

8.0%

Feb-01

Mar-01

Apr-01

May-01

Jun-01

Jul-01

Aug-01

Sep-01

Oct-01

Nov-01

Dec-01

Jan-02

Month sold

Interest (annualized)

As you can see, if the T-bill is sold before its maturity, there is a considerable amount of

risk—defined here as the possible variation in the ex-post rate of return.

PFE, Chapter 10: What is risk? page 16

An Excel Note

The interest rate calculations above use the following formula:

()

1 monthly interest rate =

1 annual interest rate=

number of months held

monthly

annual monthly

Tbillpricemontht

T bill purchase price

r1+r







In order to calculate the number of months the T-bill has been held, we use the Excel

function Month. This function, when applied to a date, identifies the month by a number

(January = 1, February = 2, ....):

AB C DE

USING EXCEL'S MONTH FUNCTION

Date Month

3-Jan-03 1 <-- =MONTH(A4)

16-Sep-06 9 <-- =MONTH(A5)

What ex-ante rate of return would you have earned if you’d bought the T-bill

during the year?

In the previous exercise we calculated the ex-post rate of return which you would have

earned if you had bought the T-bill on 1 January 2001 and had sold it before the bill’s maturity

on 1 January 2002. There’s a second “game” we can play with the T-bill prices. Suppose you

had bought the bill at the beginning of May for 974.95 and suppose you intended to hold it until

the end of the year. What’s the annualized interest ex-ante return you could expect?

()

1, 000

01 01

1, 000

1.0032

974.95

- 1.0032 1 3.88%

ex ante monthly rate of return

Price on May

Annualized ex ante return













=−=

PFE, Chapter 10: What is risk? page 17

If we do this for each of the months:

ABC D E F

Date Bill price

Months

till

maturity

Implied

monthly interest

ex-ante rate

Ex-ante rate

annualized

Jan-01 953.04 12 0.40% 4.93% <-- =(1+D24)^12-1

Feb-01 958.08 11 0.39% 4.78%

Mar-01 964.59 10 0.36% 4.42%

Apr-01 970.46 9 0.33% 4.08%

May-01 974.95 8 0.32% 3.88%

Jun-01 979.23 7 0.30% 3.66%

Jul-01 981.92 6 0.30% 3.72%

Aug-01 985.56 5 0.29% 3.55%

Sep-01 990.62 4 0.24% 2.87%

Oct-01 994.14 3 0.20% 2.38%

Nov-01 996.36 2 0.18% 2.21%

Dec-01 998.12 1 0.19% 2.29%

Jan-02 1,000.00 0

IMPLIED ANNUAL INTEREST RATE

Jan-01

Feb-01

Mar-01

Apr-01

May-01

Jun-01

Jul-01

Aug-01

Sep-01

Oct-01

Nov-01

Dec-01

=($B$36/B24)^(1/C24)-1

What’s the message?

This simple example, which illustrates the riskiness of a “riskless” U.S. Treasury bill,

illustrates that financial risk depends on horizon: A financial asset can be riskless over one

horizon and risky over another. In our example, buying the Treasury bill at any point during the

PFE, Chapter 10: What is risk? page 18

year and holding it until maturity guarantees that the ex-ante return will equal the ex-post return.

On the other hand, selling the bill before its maturity involves risk—in this case the realized

return (the ex-post return) varies.

A final word: What caused the riskiness of the Treasury bills?

We’ve shown that holding a T-bill during 2001 could have been pretty risky—if you

were thinking of selling the bill before maturity. The cause of all this uncertainty was the

Federal Reserve Bank’s Open Market Committee. This powerful committee sets short-term

interest rates, which have a dramatic effect on the value of all bonds, but especially on short-term

bonds like Treasury bills. In an effort to shore up the flagging U.S. economy, the Fed’s Open

Market Committee reduced interest rates eleven times during the course of 2001! These changes

in interest rate caused the changes in the ex-post and ex-ante returns which we’ve documented in

this section.

10.3. Risk in stock prices—McDonald’s stock

A U.S. Treasury bill is a relatively simple security: The issuer is very well-known and

has never defaulted, the ex-ante return can be derived from the price, and this return is

guaranteed if you hold the bill until maturity. A stock has none of these properties, and is thus in

every sense riskier. The problem is how to quantify this risk.

Here’s an example—Figure 3 shows how the stock price of McDonald’s varied over the

decade of the 90s:

PFE, Chapter 10: What is risk? page 19

McDonald's--Stock Price, 29Dec89-31Dec99

29-Dec-89

22-Jun-90

14-Dec-90

7-Jun-91

29-Nov-91

22-May-92

13-Nov-92

7-May-93

29-Oct-93

22-Apr-94

14-Oct-94

7-Apr-95

29-Sep-95

22-Mar-96

13-Sep-96

7-Mar-97

29-Aug-97

20-Feb-98

14-Aug-98

5-Feb-99

30-Jul-99

Figure 3: The stock price of McDonald’s, 1990 - 1999

The fact that the stock’s price goes up and down is an indication of the stock’s riskiness.

If we calculate the daily returns, we see a different kind of risk. Below we calculate the daily

return from holding McDonald’s stock—this is what you would earn in percentages if you

bought the stock at its closing price on day t and sold it at its closing price on day t+1:

Daily return day t

−

If you plot the daily returns for one month, you get a very spiky pattern:

A technical note which you can ignore but which may make your finance prof happy: The prices of the

McDonald’s stock have been adjusted to include dividends.

PFE, Chapter 10: What is risk? page 20

ABCDEFGHIJK

McDONALD'S--DAILY STOCK PRICES, 29Dec89 - 31Dec99

Date

Closing

stock price

Date Close

29-Dec-89 8.50

2-Jan-90 8.59 1.059% <-- =B6/B5-1

3-Jan-90 8.50 -1.048% <-- =B7/B6-1

4-Jan-90 8.34 -1.882%

5-Jan-90 8.19 -1.799%

8-Jan-90 8.34 1.832%

9-Jan-90 8.28 -0.719%

10-Jan-90 8.13 -1.812%

11-Jan-90 8.07 -0.738%

12-Jan-90 7.91 -1.983%

15-Jan-90 7.82 -1.138%

16-Jan-90 7.91 1.151%

17-Jan-90 7.91 0.000%

18-Jan-90 7.94 0.379%

19-Jan-90 7.85 -1.134%

22-Jan-90 7.63 -2.803%

23-Jan-90 7.76 1.704%

24-Jan-90 7.63 -1.675%

25-Jan-90 7.60 -0.393%

26-Jan-90 7.70 1.316%

29-Jan-90 7.70 0.000%

30-Jan-90 7.70 0.000%

31-Jan-90 8.03 4.286%

McDonald's--Daily Returns for January 1990

-4.0%

-3.0%

-2.0%

-1.0%

0.0%

1.0%

2.0%

3.0%

4.0%

5.0%

1-Jan-90

4-Jan-90

7-Jan-90

10-Jan-90

13-Jan-90

16-Jan-90

19-Jan-90

22-Jan-90

25-Jan-90

28-Jan-90

31-Jan-90

If you plot the daily returns for all 2,528 data points, you get a very “noisy” pattern (there

are dots all over the place, though there seem to be slightly more dots above the x-axis than

below it):

McDonald's--Daily Returns 29Dec89 - 31Dec99

-12.0%

-9.0%

-6.0%

-3.0%

0.0%

3.0%

6.0%

9.0%

12.0%

2-Jan-90

9-Sep-90

17-May-91

22-Jan-92

28-Sep-92

5-Jun-93

10-Feb-94

18-Oct-94

25-Jun-95

1-Mar-96

6-Nov-96

14-Jul-97

21-Mar-98

26-Nov-98

3-Aug-99

PFE, Chapter 10: What is risk? page 21

The distribution of McDonald’s stock returns

The previous two graph show the return on McDonald’s stock on each specific date.

These graphs clearly show that the stock is risk—the returns vary from day to day—but they

don’t give much insight into the statistical nature of the riskiness of the stock. A different way to

think about the riskiness of McDonald’s stock is to look at the frequency distribution of the daily

returns. Of the 2528 daily returns, how many were between 1.09% and 1.79%? The answer

turns out to be 416, which is 16.462% of the total number of returns. As another example: A

total of 36 of the returns (1.425% of the total), were between -3.09% and -2.40%. In the

spreadsheet below we’ve used Excel’s

Frequency function to calculate the whole frequency

distribution of the returns (see Box0.00 for more on how to use this function). The plots of the

returns look very much like the normal distribution (the “bell curve”) you’ve probably studied in

a statistics course.

PFE, Chapter 10: What is risk? page 22

FGHIJKLMNOPQ

Computing the frequency distribution of MCD returns

Largest daily return 10.86% <-- =MAX(C7:C2534)

Smallest daily return -10.07% <-- =MIN(C7:C2534)

Bin How many? Percentage

-10.07% 1 0.040%

-9.37% 0 0.000%

-8.68% 0 0.000%

-7.98% 1 0.040%

-7.28% 0 0.000%

-6.58% 1 0.040%

-5.88% 1 0.040%

-5.19% 1 0.040%

-4.49% 3 0.119%

-3.79% 11 0.435%

-3.09% 36 1.425%

-2.40% 68 2.691%

-1.70% 138 5.461%

-1.00% 339 13.415%

-0.30% 469 18.560%

0.40% 457 18.085%

1.09% 416 16.462%

1.79% 265 10.487%

2.49% 162 6.411%

3.19% 75 2.968% Note: The yellow highlighted data in the table is marked

3.88% 48 1.899% in red in the graph.

4.58% 18 0.712%

5.28% 8 0.317%

5.98% 3 0.119%

6.68% 4 0.158%

7.37% 0 0.000%

8.07% 1 0.040%

8.77% 0 0.000%

9.47% 0 0.000%

10.16% 1 0.040%

10.86% 0 0.000%

Note: We've used the Excel

Frequency

function (see separate page).

To understand the numbers: there is 1 daily return between -10.07% and -9.37%;

There are 3 daily returns between -4.49% and -3.79%; etc.

McDonald Stock Returns Look

Like the Normal Distribution "Bell Curve"

100

150

200

250

300

350

400

450

500

-10.35%

-8.35%

-6.35%

-4.35%

-2.35%

-0.35%

1.65%

3.65%

5.65%

7.65%

9.65%