Use the spreadsheet models related to concepts from Chapter 8 from the CFIN textbook to answer the questions below. Most of the questions require only inputting data into the specified places on the spreadsheet and reviewing the model-generated data, including the key outputs portions of the spreadsheets to support your written answer. Please answer each question and subpart in good form (complete sentences, etc.). Please type your answers in this document either below each question or in numerical order after the last question, but be sure to either highlight, bold, or change your font color so its clear what text constitutes your answer. Should you need to show your spreadsheet/work for any of the problems please use screen captures (google print screen if youre not sure what to do). You DO NOT need to submit the Excel file with the spreadsheets.

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 As Returns, Stock Bs Returns,

-1 18.00% 14.50%

-2 33.00 21.80

-3 15.00 30.50

-4 0.50 7.60

-5 27.00 26.30

1. Calculate the average rate of return for each stock during the past five years.
2. 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?
3. Calculate the standard deviation of returns for each stock and for the portfolio.
4. Calculate the coefficient of variation for each stock and for the portfolio. If you are a risk-averse investor, would you prefer to hold Stock A, Stock B, or the portfolio? Why?
5. 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 Cs Return,

-1 32.00%

-2 11.75

-3 10.75

-4 32.25

-5 6.75

Input these values and calculate the average return, standard deviation, and coefficient of variation for Stock C.

1. Assume that the portfolio now consists of 33.33 percent Stock A, 33.33 percent Stock B, and 33.34 percent Stock C. How does this composition affect the portfolio return, standard deviation, and coefficient of variation versus when 50 percent was invested in A and in B?
2. Make some other changes in the portfolio, making sure that the percentages sum to 100 percent. For example, enter 25 percent for Stock A, 25 percent for Stock B, and 50 percent for Stock C. Notice that remains constant and that s_p changes. Why do these results occur?
3. 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 s_C = s_A = s_B. What does this change indicate about the correlation between Stock C and Stocks A and B?

1. Would you prefer to hold a portfolio consisting only of Stocks A and B or a portfolio that also includes Stock C? If others react similarly, how might this fact affect the stocks prices and rates of return?

rA rB r^p