1 Normal uuu 1 No Spac... Heading 1 LLU Heading 2 ABDUL ABBDCi AaBbcci AOBbcc Subtitle Subtle Em. Emphasis intense Title Paragraph Styles SPREADSHEET (EXCEL) ASSIGNMENT #2 Stock A and Stock B produced the following returns during the past five years Year 2008 2009 2010 2011 2012 Stock A's Returns, -18.00% 33.00 15.00 0.50 27.00 Stock B's Returns, -14.50% 21.80 30.50 -7.60 26.30 a. Calculate the average rate of return for each stock during the period 2008-2012. 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 from 2008 through 2012? What would have been the average return on the portfolio during this period? c. Calculate the standard deviation of returns for each stock and for the portfolio. d. 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? e. Assume a third stock, Stock C, is available for inclusion in the portfolio. Stock C produced the following returns during the 2008-2012 period: Year 2008 2009 2010 Stock C's Return, 32.00% -11.75 10.75 32.25 2011 AaBbc ABCD AaBbc Aabbcc Normal No Space Heading Heading 2 ab Aabet ADA Take Subtitle Im Em Pa ZUP Year 2008 Stock C's Return, 32005 2009 2010 10.75 32.25 2011 2017 f Calculate (or read from the computer screen) the average return, standard deviation, and coefficient of variation for Stock C. - Assure that the portfolio now consists of 33.33 percent Stock A, 33.33 percent Stock Band 33.33 percent Stock C. How does this composition affect the portfolio return, standard deviation, and coelicient of a versus when 50 percent was invested in A and in E? h. 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 fp remains constant and that ap changes. Why do these results occur? i. 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 Causes the were to search o e a 5 9 W fp remains constant and that Op changes. Why do these results occur? 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 oc=0A = GB. What does this change indicate about the correlation between Stock C and Stocks A and B? 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