Investments, tenth edition

A

B

C

D

E

F

H

G

I

Purchase Price 

5

$100

State of the

Market

Probability

Excellent

Good

Poor

0.05

0.25

0.45

0.25

Expected Value (mean)

Standard Deviation of HPR

Variance of HPR

Crash

Year-End

Price

46.00

126.50

110.00

89.75

2.00

4.50

4.00

3.50

20.5600

0.2700

0.1000

20.1075

0.3815

0.0451

0.0018

0.0273

20.5200

0.3100

0.1400

20.0675

Cash

Dividends

T-bill Rate 

5 0.04

HPR

20.6176

0.2124

0.0424

20.1651

Squared

Deviations

from Mean

Deviations

from Mean

0.3815

0.0451

0.0018

0.0273

0.0380

0.1949

0.0976

Excess

Returns

Squared

Deviations

from Mean

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

SUMPRODUCT

(B8:B11, E8:E11) 

5

SUMPRODUCT

(B8:B11, G8:G11) 

5

SQRT(G13)

 

5

0.0576

Risk Premium

Standard Deviation of Excess Return

0.1949

SQRT(SUMPRODUCT

(B8:B11, I8:I11)) 

5

SUMPRODUCT

(B8:B11, H8:H11) 

5

 Spreadsheet 5.1

Scenario analysis of holding period return of the stock-index fund  

e

X

c e l

Please visit us at 

www.mhhe.com/bkm

bod61671_ch05_117-167.indd   128

bod61671_ch05_117-167.indd   128

6/18/13   8:03 PM

6/18/13   8:03 PM

Final PDF to printer

---

  C H A P T E R  

5

  Risk, Return, and the Historical Record 

129

  Spreadsheet  5.1  shows that this sum can be evaluated easily in Excel, using the 

SUMPRODUCT function, which first calculates the products of a series of number 

pairs, and then sums the products. Here, the number pair is the probability of each 

scenario and the rate of return.  

 The standard deviation of the rate of return ( s ) is a measure of risk. It is defined as the 

square root of the variance, which in turn is the expected value of the squared deviations 

from the expected return. The higher the volatility in outcomes, the higher will be the aver-

age value of these squared deviations. Therefore, variance and standard deviation provide 

one measure of the uncertainty of outcomes. Symbolically,   

 

s

2

5 a

s

p(s)

3r(s) 2 E(r)4

2

 

 (5.12)   

 Therefore, in our example   

 

 s

2

5 .25(.31 2 .0976)

2

1 .45(.14 2 .0976)

2

1 .25(2.0675 2 .0976)

2

 

  1 .05(2.52 2 .0976)

2

5 .0380   

 This value is calculated in cell G13 of  Spreadsheet 5.1  using the SUMPRODUCT func-

tion. The standard deviation is calculated in cell G14 as   

s 5

".0380 5 .1949 5 19.49%  

 Clearly, what would trouble potential investors in the index fund is the downside risk 

of a crash or poor market, not the upside potential of a good or excellent market. The stan-

dard deviation of the rate of return does not distinguish between good or bad surprises; it 

treats both simply as deviations from the mean. As long as the probability distribution is 

more or less symmetric about the mean,  s  is a reasonable measure of risk. In the special 

case where we can assume that the probability distribution is normal—represented by the 

well-known bell-shaped curve— E ( r ) and  s  completely characterize the distribution.