CFIN6
Chapter 8 Spreadsheet Problem
Risk and Rates of Return
Use the model in File C08 to work the problem.
Stock A and Stock B produced the following returns during the past five years (Year -1 is one year ago,
Year -2 is two years ago, and so forth):
Year Stock A’s Returns,
A
r
&&
Stock B’s Returns,
B
r
&&
-1 –18.00% –14.50%
-2 33.00 21.80
a. Calculate the average rate of return for each stock during the past five years.
b. Assume that someone held a portfolio consisting of 50 percent Stock A and 50 percent Stock B.
What would have been the realized rate of return on the portfolio in each year for the past five years?
What would have been the average return on the portfolio during this period?
e. Assume a third stock, Stock C, is available for inclusion in the portfolio. Stock C produced the
following returns during the past five years:
Year Stock C’s Return, σ
-1 32.00%
-2 –11.75
CFIN6
Input these values and calculate the average return, standard deviation, and coefficient of variation
for Stock C.
h. In part b, you should see that the standard deviation of the portfolio decreased only slightly
because Stocks A and B were highly positively correlated with each other. The addition of
Stock C causes the standard deviation of the portfolio to decline dramatically, even though
C = A = B. What does this change indicate about the correlation between Stock C and
Stocks A and B?