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

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

PFE, Chapter 10: What is risk? page 23

[Separate Page]

An Excel Note: The Frequency Function

The frequency distribution which gave rise to the “bell curve” for McDonald’s stock returns was

calculated with an Excel function called

Frequency. To use this function, consider the

following example, which gives the monthly returns on Ford stock between January 2001 and

January 2003:

When you finish putting in range C4:C27 and F3:F22 as shown above, don’t click on

OK!

Instead, simultaneously press the keys [Ctrl]+[Shift]+[Enter]. This will put the frequency in the

spreadsheet as shown below:

PFE, Chapter 10: What is risk? page 24

-22% 0

-19% 1

-16% 4

-13% 1

-10% 3

-7% 1

-4% 1

-1% 4

2% 3

5% 1

8% 1

11% 2

14% 0

17% 0

20% 1

23% 0

26% 0

29% 0

32% 0

35% 1

Frequency distribution

of returns

This table says that in the period January 2001 – January 2003, there was 1 month when

Ford stock had a return between -22% and -19%, 4 months when Ford stock had a return

between -19% and -16%, and so on.

PFE, Chapter 10: What is risk? page 25

Computing the mean and standard deviation of the McDonald’s returns

If we concentrate on the end-year prices of McDonald’s, we can compute the average

annual return of 18.86 percent; on average, a McDonald’s shareholder got an annual return of

18.86 percent per year over the period 1990-1999. The standard deviation of the annual returns

is 23.28 percent. The standard deviation is a statistical measure of the variation of the stock’s

returns—the greater the standard deviation, the greater the riskiness of the stock.

ABC DE F G H

Date

Closing

stock price

Return

29-Dec-89 8.50

Statistics

31-Dec-90 7.17 -15.647% <-- =B4/B3-1 Largest annual return 60.88% <-- =MAX(C4:C13)

31-Dec-91 9.36 30.544% <-- =B5/B4-1 Smallest annual return -15.65% <-- =MIN(C4:C13)

31-Dec-92 12.01 28.312%

31-Dec-93 14.04 16.903% Average annual return 18.86% <-- =AVERAGE(C4:C13)

30-Dec-94 14.41 2.635% Variance of annual returns 0.0542 <-- =VARP(C4:C13)

29-Dec-95 22.23 54.268% Standard deviation of annual returns 23.28% <-- =STDEVP(C4:C13)

31-Dec-96 22.35 0.540%

31-Dec-97 23.52 5.235%

31-Dec-98 37.84 60.884%

31-Dec-99 39.71 4.942%

McDonald's

return

Return minus

average,

squared

31-Dec-90 -15.65% 11.908% <-- =(B17-$B$28)^2 See the "Excel and Statistical Review" box in the text for more details.

31-Dec-91 30.54% 1.365%

31-Dec-92 28.31% 0.893%

31-Dec-93 16.90% 0.038%

30-Dec-94 2.64% 2.633%

29-Dec-95 54.27% 12.536%

31-Dec-96 0.54% 3.357%

31-Dec-97 5.23% 1.857%

31-Dec-98 60.88% 17.659%

31-Dec-99 4.94% 1.938%

Average 18.86% <-- =SUM(B17:B26)/10

Variance 0.0542 <-- =SUM(C17:C26)/10

Standard deviation 23.28% <-- =SQRT(B29)

STATISTICAL REVIEW

McDONALD'S--End-Year Stock Prices, 1989 - 1999

This statistical review shows you how to compute the average, variance, and

standard deviation using the formulas described in the text and (hopefully)

learned in your statistics course.

PFE, Chapter 10: What is risk? page 26

EXCEL AND STATISTICAL REVIEW

We delve deeper into statistics in Chapter 11. To remind you of the meanings of the

terms:

•

The average (also called the mean) annual return for McDonald’s is computed by

summing the annual returns and dividing by 10, the number of returns. In cell G7 we’ve

computed the average by using the Excel function

Average; in cell B28 we compute the

average by using

=Sum(B17:B26)/10.

•

The variance of the McDonald annual returns is computed in three steps: i) Subtract

each return from the average. Then: ii) Square the result; these “squared deviations from

the average” are shown in cells C17:C26. The third step in computing this variance is to:

iii) Average the sum of the squared deviations. This is illustrated in cell B29. Cell G8

shows that the Excel function

Varp gives the same result.

•

Since the returns are in percent, the variance has units “percent squared.” This is a bit

difficult to understand. The standard deviation of the returns is the square root of the

variance (this has units which are percent). Informally, you can think of the standard

deviation as representing the average percentage variability of the individual returns.

Cell B30 used the Excel function

Sqrt to derive the standard deviation, and cell G9 uses

the Excel function

Stdevp.

How risky are other assets?

To give you some feel for how risky different assets are, here is a table of the annualized

returns and standard deviations for various assets:

PFE, Chapter 10: What is risk? page 27

Average

return

Standard

deviation

Average

return

Standard

deviation

Abbott 17.12% 19.27% Marriott 7.15% 39.81%

Airproducts 12.73% 27.05% McGraw-Hill 17.96% 19.52%

Alcoa 19.03% 27.59% Microsoft 62.72% 37.99%

AmericanAirlines 9.26% 29.34% Nasdaq 23.00% 20.17%

ATT 6.76% 27.96% Nicor 9.15% 16.50%

Boeing 7.57% 25.57% Nordstrom 5.44% 38.21%

Cisco 67.31% 38.80% Northrop 14.76% 34.40%

Coke 10.18% 24.29% PPG 14.42% 23.02%

Dell 69.86% 55.48% Procter & Gamble 19.41% 20.91%

Duke 11.07% 15.43% Safeway 25.65% 33.53%

Exxon 12.42% 13.96% Standard & Poor's 500 Index 15.09% 13.18%

Ford 9.12% 29.11% Teva 26.57% 36.17%

GM 11.37% 27.63% U.S. Steel 4.17% 32.80%

Hershey 13.17% 20.74% UST 10.39% 22.89%

IBM 14.89% 29.57% Vanguard Long-Term Treasury Fund 2.43% 7.94%

Kellogg 8.92% 23.16% Walmart 25.84% 25.45%

Magellan Fund 17.82% 14.40% WR Grace 8.73% 31.20%

Manpower 9.93% 33.32%

AVERAGE RETURN VERSUS STANDARD DEVIATION OF RETURNS

A Somewhat Arbitrary List of Assets, 1990-1999

Return versus Standard Deviation of Returns

10%

20%

30%

40%

50%

60%

70%

80%

0% 10% 20% 30% 40% 50% 60%

Standard deviation

Average return

A closer look at the four highlighted financial assets can give you some better intuitions

on the relation between financial risk and return:

•

Vanguard’s Long-Term Treasury Fund is a mutual fund which invests in long-term U.S.

Treasury bonds. As you saw in Section 10.2, the absence of default risk in these bonds

PFE, Chapter 10: What is risk? page 28

does not mean that they are riskless: Their prices can vary considerably, and as a result

the holder of the Vanguard Long-Term Treasury Fund may experience uncertainty in her

returns. Nevertheless, our intuition tells us (correctly, as you’ll see in the succeeding

bullets) that this fund ought to be less risky than most stocks. During the decade of 1990-

1999, the Vanguard Long-Term Treasury Fund gave an average annual return of 2.43%

and this return had a standard deviation of 7.94%.

•

The Standard & Poor’s 500 Index is a broad-based index of the largest U.S. stocks and is

often used as a measure of the performance of the U.S. stock markets. During the decade

of 1990s, the S&P 500 Index had an average annual return of 15.09% and a standard

deviation of 13.18%. As you might expect, there’s a clear tradeoff between the average

return of the S&P 500 and the Vanguard Long-Term Treasury Fund: The S&P 500 gives

more return, but you pay for this return with greater variability (measured by the standard

deviation):

2.43% 15.09% & 500

7.94% 13.18% & 500

Treasury Fund average return S P average return

Treasury Fund standard deviation S P standard deviation

=< =

•

The riskiest asset in our table is Dell stock. This stock performed spectacularly over the

decade, giving an annual average return of 69.86%.

On the other hand Dell was also the

riskiest stock in the table, with a standard deviation of return of 55.48%.

•

After the fact (or, as economists like to say ex-post) some assets were clearly inferior to

other assets. Manpower’s stock turned out to have lower average return and higher risk

(measured by standard deviation) than the S&P 500.

This number deserves some thought and admiration: If you’d invested $100 in Dell stock in 1990, it would have

grown to

()

$100* 1 69.86% $19,955+=

by the end of the decade!

PFE, Chapter 10: What is risk? page 29

Over this period there seems to be a positive relation between the standard deviation of

the returns and the annualized return of the assets, although a closer look at the graph will show

you that there is a large group of assets with different standard deviations and roughly the same

average returns. The graph should be taken as somewhat indicative of a possible relation

between risk (as measured here by the standard deviation of returns) and average returns. From

this graph you might be tempted to conclude that when an asset’s standard deviation of returns

increase, the return expected from the asset increases. This is not far from the truth—but in

Chapter 12 we define another measure of asset risk, called beta, which works better.

10.4. Advanced topic: Using continuously-compounded returns to compute

annualized return statistics

We discussed continuous compounding in Chapter 2. As explained there, the

continuously-compounded return is calculated using the

Ln function. For reasons that are

beyond the purview of this book, the continuously-compounded return is the only consistent

method of computing return statistics (by “consistent” we mean two things: there’s a theory

behind the numbers, and this theory gives the same results whether you’re computing the annual

statistics from daily, weekly, or monthly data). In the spreadsheet below, we’ve computed the

continuously-compounded return statistics for McDonald’s.

Actually if you look really carefully at the trendline in the graph, you’ll see it’s mostly influenced by the three

rightmost points (these are for Microsoft, Cisco, and Dell). Without these points, there would be no relation

between the return and the standard deviation of the returns—we need a better measure of asset risk, which we

develop in the following chapters.

PFE, Chapter 10: What is risk? page 30

ABCDE F G H

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

This spreadsheet uses the continously compounded return

Date

Closing

stock price

Date Close

Statistics

29-Dec-89 8.50

Daily returns

2-Jan-90 8.59 1.053% <-- =LN(B7/B6) Largest daily return 10.31% <-- =MAX(C7:C2534)

3-Jan-90 8.50 -1.053% <-- =LN(B8/B7) Smallest daily return -10.62% <-- =MIN(C7:C2534)

4-Jan-90 8.34 -1.900%

5-Jan-90 8.19 -1.815% Average daily return 0.0610% <-- =AVERAGE(C7:C2534)

8-Jan-90 8.34 1.815% Variance of daily returns 0.0257% <-- =VARP(C7:C2534)

9-Jan-90 8.28 -0.722% Standard deviation of daily returns 1.6039% <-- =SQRT(G11)

10-Jan-90 8.13 -1.828%

11-Jan-90 8.07 -0.741%

nnualized

12-Jan-90 7.91 -2.003% Average annual return 15.43% <-- =G10*253

15-Jan-90 7.82 -1.144% Variance of annual returns

6.51% <-- =G11*253

16-Jan-90 7.91 1.144% Standard deviation of annual returns 25.51% <-- =SQRT(G16)

17-Jan-90 7.91 0.000%

18-Jan-90 7.94 0.379%

19-Jan-90 7.85 -1.140%

22-Jan-90 7.63 -2.843%

23-Jan-90 7.76 1.689%

24-Jan-90 7.63 -1.689%

25-Jan-90 7.60 -0.394%

26-Jan-90 7.70 1.307%

29-Jan-90 7.70 0.000%

30-Jan-90 7.70 0.000%

The average daily continuously compounded return (cell G10) is 0.0610%. To annualize

this return, we multiply by 253, the average number of business days per year.

The annualized

average continuously compounded return is 15.43%. Similarly the annualized return variance is

6.51% (cell G16), and the annualized standard deviation of returns is 25.51% (cell G17).

Over the 10-year period 1990-1999, there were 2528 days on which McDonald’s stock was transacted. This

averages out to 253 days per year.

Note that the continuously-compounded average return of 15.43% is less than the discretely-compounded return of

18.86% computed in the previous sub-section. One reason for this is that continuous compounding builds up faster

than discretely-compounded interest. Another reason, as noted in Chapter 2, is that there are legitimate alternative

ways to compute returns. To compare the returns of two assets, make sure that the basis of the computation is the

same.

PFE, Chapter 10: What is risk? page 31

10.5. Risk and return depend on the your unit of account

As we’ve shown in the examples above, risk and return depend on the kind of security

you’re considering. Returns can also depend on what currency you’re calculating in. Investors

these days are putting their money in many stock markets around the world, and their returns are

affected both by fluctuations in stock prices and in the rates of exchange.

In the example below we calculate the return—in Euros and in dollars—from holding the

Amsterdam Stock Exchange index (symbol: AEX). In the table below we use the continuously-

compounded returns. As shown in Section 10.4, using continuously-compounded returns makes

it much easier to go from monthly data to annual data. For example:

•

The average monthly Euro return on the AEX index is -0.14%. To compute the average

annual return, we simply multiply this number by 12: 12*-0.14% = -1.67%.

•

The monthly standard deviation of AEX Euro returns is 5.10%. To compute the average

annual standard deviation, we multiply this number by

12 *5.10% 17.68%=

PFE, Chapter 10: What is risk? page 32

ABCDEFGH

Date

Index

price

in Euros

Monthly

return in

Euros

Euro/$

exchange

rate

Index

price

in $

Monthly

return in

January-99 532.09 1.161 617.76

February-99 536.12 0.75% <-- =LN(B4/B3) 1.121 600.99 -2.75% <-- =LN(F4/F3)

March-99 536.93 0.15% <-- =LN(B5/B4) 1.088 584.18 -2.84% <-- =LN(F5/F4)

April-99 573.52 6.59% 1.07 613.67 4.92%

May-99 554.06 -3.45% 1.063 588.97 -4.11%

June-99 561.19 1.28% 1.038 582.52 -1.10%

July-99 552.77 -1.51% 1.035 572.12 -1.80%

August-99 572.42 3.49% 1.06 606.77 5.88%

September-99 547.45 -4.46% 1.05 574.82 -5.41%

October-99 571.82 4.36% 1.071 612.42 6.34%

November-99 602.11 5.16% 1.034 622.58 1.65%

December-99 671.41 10.89% 1.011 678.80 8.64%

January-00 612.38 -9.20% 1.014 620.95 -8.91%

February-00 664.28 8.14% 0.983 652.99 5.03%

March-00 662.29 -0.30% 0.964 638.45 -2.25%

April-00 661.38 -0.14% 0.947 626.33 -1.92%

May-00 655.5 -0.89% 0.906 593.88 -5.32%

June-00 672.14 2.51% 0.949 637.86 7.14%

July-00 668.18 -0.59% 0.94 628.09 -1.54%

August-00 689.52 3.14% 0.904 623.33 -0.76%

September-00 661.52 -4.15% 0.872 576.85 -7.75%

October-00 680.56 2.84% 0.855 581.88 0.87%

November-00 649.92 -4.61% 0.856 556.33 -4.49%

December-00 637.6 -1.91% 0.897 571.93 2.76%

January-01 639.98 0.37% 0.938 600.30 4.84%

February-01 597.33 -6.90% 0.922 550.74 -8.62%

March-01 558.36 -6.75% 0.91 508.11 -8.06%

April-01 593.09 6.03% 0.892 529.04 4.04%

May-01 585.15 -1.35% 0.874 511.42 -3.39%

June-01 573.5 -2.01% 0.853 489.20 -4.44%

July-01 548.72 -4.42% 0.861 472.45 -3.48%

August-01 523.63 -4.68% 0.9 471.27 -0.25%

September-01 453.87 -14.30% 0.911 413.48 -13.08%

October-01 460.33 1.41% 0.906 417.06 0.86%

November-01 492.67 6.79% 0.888 437.49 4.78%

December-01 506.78 2.82% 0.892 452.05 3.27%

Return statistics In Euros In Dollars

Monthly average -0.14% -0.89% <-- =AVERAGE(G4:G38)

Monthly standard deviation 5.10% 5.15% <-- =STDEVP(G4:G38)

Annual average -1.67% -10.71% <-- =12*G41

Annual standard deviation 17.68% 17.83% <-- =SQRT(12)*G42

AMSTERDAM STOCK EXCHANGE INDEX (AEX)

in Euros and Dollars

A “Euro investor”—someone living in Euro-land and who thinks in Euros—would have

lost 1.67 percent per year (cell C44) on her investment in the AEX over the two years surveyed.

Over the same time period a “dollar investor”—say an American investing in the Amsterdam

AEX—would have lost almost 10.71 percent per year (cell G44).

Why do the Euro returns and the dollar returns differ so radically? Take a look at what

happened between 1 January 1999 and 1 February 1999. A Euro investor who bought the index