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

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

PFE, Chapter 10: What is risk? page 33

on 1 January 1999 would have paid € 532.09; if she sold the index one month later, she would

have gotten € 536.12. This is a Euro return of

€536.12

0.75%

€532.09







On the other hand, a dollar investor who bought the Amsterdam index on 1 January 1999

would have paid $617.76 ( at the point he purchased the index, $1 was worth € 1.161, so that the

Euro price of the index becomes

€

532.09*1.161 $617.76

). When this dollar investor sold the

index after one month, it was at € 536.12 and the value of a dollar had fallen to $1 = € 1.121, so

that the dollar price of the index was

€

536.12*1.121 $600.99

. As a result the investor’s dollar

return was

$600.99

2.75%

$617.76



=−





The conclusion: Whether the Amsterdam Stock Exchange index was just a bad or a very

bad investment depends very much on whether you were a Euro investor (in which case it was a

bad investment) or a dollar investor (much worse).

The unit of account (dollar or Euro) matters.

Conclusion

In this chapter we have tried to give you some intuitions into the nature of financial risk

by a series of examples. Risk—the variability of returns from an asset over time—depends on a

number of factors. Broadly speaking the characteristics of an asset’s risk are its horizon, its

safety, and its liquidity. As we’ve shown, even safe assets like U.S. Treasury bills can be risky

because their prices can change over the asset’s horizon. With our example of McDonald’s stock

we’ve shown that some statistical sense can be made of the variability of the stock’s return over

time—by using Excel’s

Frequency function, we were able to show that McDonald’s stock

PFE, Chapter 10: What is risk? page 34

returns look very much like the familiar statistical “bell curve.” Finally, with our example of the

Amsterdam stock exchange index, we’ve shown that risks can differ depending on who’s

measuring them: The dollar investor in Amsterdam stocks did much worse than the Euro

investor.

Risk is the most problematic concept in finance: The variability of financial asset returns

is the main fact of financial life, but risk is not easy to define or measure. In the chapters which

follow we will develop a model to price risks; by this we mean a model which will help us

determine the risk-adjusted discount rate. The important innovation of this model (to come) is

that risk depends on a portfolio context—it is not just the asset’s returns by themselves that

determine the asset’s riskiness, but the asset’s returns in the context of the portfolio of all the

assets held by the investor. To some extent we have already hinted at this model in this chapter—

by showing that there is a relation between the historic returns of assets and the standard

deviation of these returns.

In the next chapters we will refine this intuition. We’ll show you that it’s not the

standard deviation but rather the asset’s beta (a measure of risk which we’ll define) which helps

us determine the risk-adjusted return. Beta is a widely used measure of risk, and as a finance

student you should understand how to use it. But before you do this, you’ll need a brush-up on

your statistics skills. This is the task we set ourselves in the next chapter.

PFE, Chapter 10: What is risk? page 35

Exercises

1. It’s 1 January 2001 and you’re considering buying a $1,000 face-value U.S. Treasury bill

which matures in 1 year. The interest rate is 7% annually.

1.a. If you buy the T-Bill now, how much will you pay?

1.b. If the interest rate remains 7% annually, how much will the bill be worth on 1

February 2001? 1 March? 1 April? ... 1 December?

On March 15, 2002, you purchased a 2-year Treasury bond with face value $10,000 and a 4%

coupon (payable semi-annually). The price of the bond was $9,750; it promises a coupon of

$200 on 15 September 2002, 15 March 2003, 15 September 2003, and 15 March 2004 (on this

last date the bond will repay its face value).

a. Based on the following, compute the annualized IRR of the bond purchase:

ABC

Date Cash flow

15-Mar-02 -9,750

15-Sep-02 200

15-Mar-03 200

15-Sep-03 200

15-Mar-04 10,200

2.67% <-- =IRR(B5:B9)

b. On 16 September 2002 you sold the bond for $10,000. What was the ex-post annualized

yield that you got? What was the ex-ante annualized yield of the buyer of the bond?

PFE, Chapter 11: Statistics chapter page 1

CHAPTER 11: STATISTICS FOR PORTFOLIOS

this version: July 11, 2003

Chapter contents

Overview......................................................................................................................................... 1

11.1. Basic statistics for asset returns: mean, standard deviation, covariance, and correlation.... 2

11.2. Downloaded data from commercial sources is adjusted for dividends and splits.............. 12

11.3 Covariance and correlation—two additional statistics ........................................................ 14

11.4. Portfolio mean and variance for a two-asset portfolio........................................................ 22

11.5. Using regressions................................................................................................................ 26

11.6. Advanced section: portfolio statistics for multiple assets.................................................. 35

Conclusion and summary.............................................................................................................. 38

Exercises....................................................................................................................................... 39

Appendix: Downloading data from Yahoo.................................................................................. 55

Overview

In order to understand and work through Chapters 12 – 15, you will need to know some

statistics. If you’re like a lot of finance students, you’ve had a statistics course and forgotten

much of what you learned there. This chapter is a refresher—it show you exactly what you need

Notice: This is a preliminary draft of a chapter of Principles of Finance with Excel by Simon Benninga

(benninga@wharton.upenn.edu

). Check with the author before distributing this draft (though you will probably get

permission). Make sure the material is updated before distributing it. All the material is copyright and the rights

belong to the author.

PFE, Chapter 11: Statistics chapter page 2

in order to proceed with the succeeding chapters, using Excel to do all the calculations. (Excel is

a great statistical toolbox—someday all business-school statistics courses will use it. In the

meantime you’re stuck with this chapter.)

Finance concepts

• How to calculate stock returns and adjust them for dividends and stock splits

• Return mean, variance, and standard deviation for an asset

• Return mean and variance for a portfolio of two assets

• Regressions

Excel functions and techniques

• Average

• Var( ) and Varp( )

• Stdev( ) and Stdevp( )

• Covar( ) and Correl( )

• Trendlines (Excel’s term for regressions)

• Slope( ), Intercept( ), Rsq( )

11.1. Basic statistics for asset returns: mean, standard deviation, covariance,

and correlation

In this section you will learn to calculate the return on a stock and its statistics: the mean

(interchangeably referred to as the average or expected return), the variance, and the standard

PFE, Chapter 11: Statistics chapter page 3

deviation. You will also learn the meaning of the covariance of the return between two stocks

and of the correlation coefficient of the returns.

General Motors stock and its returns

The following spreadsheet shows data for General Motors stock during the decade of

1990. For each year, we’ve given the closing price of GM stock and the dividend the company

paid during the year. We’ve also calculated the annual returns and their statistics; these

calculations are explained after the table:

ABCD E

Date

Closing

Price Dividend

Annual

return

29-Dec-89 42.2500 -

31-Dec-90 34.3750 3.00 -11.54% <-- =(C4+B4)/B3-1

31-Dec-91 28.8750 1.60 -11.35% <-- =(C5+B5)/B4-1

31-Dec-92 32.2500 1.40 16.54%

31-Dec-93 54.8750 0.80 72.64%

30-Dec-94 42.1250 0.80 -21.78%

29-Dec-95 52.8750 1.10 28.13%

31-Dec-96 55.7500 1.60 8.46%

31-Dec-97 60.7500 5.59 19.00%

31-Dec-98 71.5625 2.00 21.09%

31-Dec-99 72.6875 14.15 21.34%

Average return 14.25% <-- =AVERAGE(D4:D13)

Variance of return 0.0638 <-- =VARP(D4:D13)

Standard deviation of return 25.25% <-- =STDEVP(D4:D13)

PRICE AND DIVIDEND DATA

FOR GENERAL MOTORS (GM)

PFE, Chapter 11: Statistics chapter page 4

Suppose you had bought a share of GM at the end of December 1989 for $42.25 and sold

it a year later, at the end of December 1990, for $34.375. During this year, GM paid a per-share

dividend of $3.

Your return from holding GM throughout 1990 would have been:

,1990 ,1990 ,1989

,1990

,1989

34.375 3.00 42.25

11.54%

42.25

GM GM GM

PDivP

+−

===−.

Several notes:

• We use r

GM,1990

to denote the return on GM stock in 1990 and we use Div

GM,1990

to denote

GM’s dividend in 1990.

• The numerator of r

GM,1990

,1990 ,1990 ,1989

34.375 3.00 42.25 4.875

GM GM GM

PDivP+−=+−=−

This is the gain on holding GM during the year (in this case it’s a negative gain: a loss of

$4.875). The denominator is of r

GM,1990

is the initial investment from buying GM stock at

the beginning of the year.

•

In cell D4 of the spreadsheet we’ve written r

GM,1990

, the return for 1990, in a slightly

different form as

(C4+B4)/B3-1. This is equivalent to:

,1990 ,1990

,1990

,1989

GM GM

PDiv

=−

Cells D15, D16, and D17 give the return statistics for GM:

•

D15: The average return over the decade is 14.25% per year. This number is also called

the mean return and it’s calculated with the Excel function

=Average(D4:D13) . We

Actually the company paid 4 quarterly dividends of $0.75, but we’ve added these together to get the annual

dividend.

PFE, Chapter 11: Statistics chapter page 5

often use the past returns to predict future returns. When we make this use of the data,

we also call the mean the expected return, meaning that we use the historic average of

GM’s stock returns as a prediction of what the stock will return in the future. We will

sometimes use the notations

(

)

GM GM

Er r In this book the terms mean, average, and

expected return will be used almost interchangeably. The formal definition is:

()

,1990 ,1991 ,1999

GM GM GM

GM GM

rr r

Mean GM return E r r

===

…

You might wonder at the number of expressions (mean, average, expected return) and the

number of symbols

()

GM GM

Er r for the same idea. We’ve introduced them all both

for convenience and because, in your further finance studies, you’re likely to see them

used synonymously.

• D16: The variance of the annual returns is 6.38%. Variance and standard deviation are

statistical measures of the variability of the returns. The variance is calculated with the

Excel function

=Varp(D4:D13). (See the “Excel note” box further on to see more

information about this function and its cousin

=Var(D4:D13). ) The variance is often

denoted by the Greek symbol

(pronounced “sigma squared of GM”); sometimes it’s

written as

()

Var r

. The formal definition of the variance is:

()

(

)

(

)

22 2

,1990 ,1991 ,1999

GM GM GM GM GM GM

GM GM

rrrr rr

Var r

−+ −++ −

…

• D17: The standard deviation of the annual returns is the square root of the variance:

0.0638 25.25%= . Excel has two functions, Stdevp( ) and Stdev( ), to do this

calculation directly. Since we usually use

Varp( ) for the variance, we will use Stdevp( )

PFE, Chapter 11: Statistics chapter page 6

for the standard deviation. It is common to use the Greek letter sigma for the standard

deviation, writing

PFE, Chapter 11: Statistics chapter page 7

Statistical Note (skip until later, or perhaps forever, if you like)

Excel has two variance functions, Varp and Var. The former measures the “population

variance,” and the latter measures the “sample variance.” Similarly Excel has two functions for

the standard devation,

Stdevp and Stdev. In this book we use only the functions Varp and

Stdevp. This box is a reminder but not an explanation of the difference between the two

concepts.

If you have return data

{

}

,1 ,2 ,

, ,...,

tock stock stock N

rr r for a stock, then the mean return is

tock stock t

∑

. The definitions of the two variance functions are:

{}

()

{}

()

,1 ,2 , ,

, ,...,

stock stock stock N stock j i

VarP r r r r r

Var r r r r r

=−

−

∑

There’s a long story about the difference between these two concepts which we’ll leave

for someone else (like your statistics instructor) to explain. Suffice it to say that in the examples

covered in this book we’ll use

VarP and its standard deviation equivalent StdevP.

Finally, you might wonder why there are two expressions—the variance and the standard

deviation—which measure the variability. The answer has to do with the units of these

expressions. Each term in the variance is squared in order to make everything positive. But this

means that the units of the variance are “percent squared,” which is a bit difficult to understand.

The standard deviation, the square root of the variance, reduces the squared percentages of the

variance back to “percent.” This way the mean and the standard deviation have the same units.