Rate of Return
Year Ursula Co Orion Inc Portfolio
2008 -3.00% -14.00%
2009 -10.00% 10.00%
2010 16.00% -5.00%
2011 13.00% 39.00%
2012 34.00% 20.00%
1. Use Excel to calculate the annual returns on a portfolio with 50% invested in
each stock above.
2. Use the spreadsheet (XY graph feature) to graph the returns for the two stocks
and the portfolio. Save the plot in a separate chart in the spreadsheet.
Please take care to label the graph and axes and make clear which data series
is which on the chart.
3. Interpret your graph. What effect does forming a portfolio appear to have on
the volatility of the returns relative to the stocks individually?
4. Use the spreadsheet function to calculate the average (or expected) return for the
two stocks and the portfolio.
5. Use the spreadsheet function to calculate the sample standard deviation of the
return on the portfolio.
6. Standard deviation measures stand-alone (or total risk.) What two types of risk are
included in the stand-alone risk?
7. How does the standard deviation of the portfolio returns compare to the standard
deviation of the individual stock returns? Is this surprising? Explain.
8. Would most investors prefer to own one of the stocks indivdually or the portfolio
of the two stocks? Explain.
9. The beta coefficients for both firms are provided below:
Ursula Co Orion Inc
Beta 1.4 0.8
a. Which stock is riskier based on the beta?
b. Which stock is riskier based on the standard deviation you calculated
in #1 above?
c. Which stock is riskier to a well diversified investor? Explain.
10. If T-bills yield 2% and the historical risk premium on the market is 8.2%, what is the
expected rate of return on each stock according to the CAPM?
11. What is the beta of a portfolio invested half in each stock above?
12. If T-bills yield 2% and the historical risk premium on the market is 8.2%, what is the
expected return on the portfolio based on the CAPM?