Prepare the following problems in Excel and ensure the formulas are present in the appropriate cells
You are considering an investment in either individual stocks or a portfolio of stocks. The two stocks you are researching, Stock A and Stock B, have the following historical returns:
Year rA rB
2012 20.00% -4.00%
2013 42.00 10.00
2014 20.00 11.00
2015 8.00 40.00
2016 25.00 11.4
a. Calculate the average rate of return for each stock during the 5-year period.
b. Suppose you had held a portfolio consisting of 50% of Stock A and 50% of Stock B. What would have been the realized rate of return on the portfolio in each year? 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. Suppose you are a risk-averse investor. Assuming Stocks A and B are your only choices, would you prefer to hold Stock A, Stock B, or the portfolio? Why?