You are an associate at Schoenberg Financial Advisory. You are considering the common stocks of General Electric (GE), as well as the risk-free asset (rf) and the stock market index (m).
You know the following information about the assets:
-
The monthly returns of GE, rf and m are given in the template file.
-
You use the sample average for the expected return on the market portfolio and for the risk-free rate. To calculate sample variance of the monthly returns, use Excel function VAR.S().To calculate sample standard deviation of the monthly returns, use Excel function STDEV.S().
Assume that you can borrow and lend at the risk-free rate and the Capital Asset Pricing Model (CAPM) accurately describes expected returns on assets.
a) Use linear regression to estimate GEs beta in the sample. What is the beta? What is expected return on GEs stock as implied by CAPM? Hint: the market risk premium is the historical average of market excess return in the sample.
Currently you have a portfolio with $200,000 invested in GEs stock, $100,000 in the market portfolio m, and $100,000 in the riskless asset rf.
-
b) What is the beta of your current portfolio?
-
c) What is the standard deviation of your current portfolios excess return?
-
d) Find an efficient portfolio (one that has no diversifiable risk) that has the same expected return
as your current portfolio, but the lowest standard deviation possible. What is the standard deviation of this efficient portfolios excess return?
E F G . 1 2 3 GE excess retum 0.3956 0.3519 0.0037 0.1719 4 a) GE beta CAPM expected return 5 6 7 8 9 10 11 12 13 b) Portfolio beta c) Portfolio vol 14 d) Portfolio vol 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 A B C D GE return m retum rf 1/1/14 0.4056 0.1423 0.0100 2/1/14 0.3619 0.0918 0.0100 3/1/14 0.0137 -0.0808 0.0100 4/1/14 0.1819 0.1175 0.0100 5/1/14 0.0373 -0.0865 0.0100 6/1/14 0.2008 -0.0224 0.0100 7/1/14 0.0713 -0.0784 0.0100 8/1/14 0.2232 0.0260 0.0100 9/1/14 -0.0365 -0.0672 0.0100 10/1/14 0.3197 0.2053 0.0100 11/1/14 0.3437 0.1906 0.0100 12/1/14 0.2137 0.1507 0.0100 1/1/15 0.3336 0.2079 0.0100 2/1/15 0.2106 0.0989 0.0100 3/1/15 0.2549 0.0293 0.0100 4/1/15 0.4513 0.1344 0.0100 5/1/15 0.2673 -0.0202 0.0100 6/1/15 0.0370 0.0188 0.0100 7/1/15 0.4997 0.2250 0.0100 8/1/15 0.3143 0.1626 0.0100 9/1/15 0.3122 0.0815 0.0100 10/1/15 0.1311 0.0949 0.0100 11/1/15 0.1866 -0.0115 0.0100 12/1/15 0.1256 0.0963 0.0100 1/1/16 0.3045 0.1432 0.0100 2/1/16 0.2790 0.2160 0.0100 3/1/16 0.0658 0.0293 0.0100 4/1/16 0.2183 0.0238 0.0100 5/1/16 0.0262 -0.0566 0.0100 6/1/16 0.3233 0.2005 0.0100 7/1/16 0.3223 0.2146 0.0100 8/1/16 0.2001 0.0841 0.0100 9/1/16 0.2642 -0.0079 0.0100 10/1/16 0.1833 -0.0731 0.0100 11/1/16 0.4858 0.2225 0.0100 12/1/16 0.1686 0.0075 0.0100 1/1/17 0.0494 -0.0838 0.0100 2/1/17 0.1254 0.0092 0.0100 3/1/17 0.1413 -0.0826 0.0100 4/1/17 0.2765 0.0579 0.0100 5/1/17 0.1456 -0.0476 0.0100 0.1555 0.1030 0.0100 7/1/17 0.0872 -0.0566 0.0100 8/1/17 0.130 0.0991 0.0100 9/1/17 -0.0841 -0.0801 0.0100 10/1/17 0.3781 0.1785 0.0100 11/1/17 0.0781 0.0473 0.0100 12/1/17 0.3501 0.1994 0.0100 1/1/18 0.0854 -0.0323 0.0100 2/1/18 0.3644 0.2340 0.0100 3/1/18 0.1535 -0.0248 0.0100 4/1/18 0.3465 0.0446 0.0100 5/1/18 0.1686 -0.0007 0.0100 6/1/18 0.3775 0.1659 0.0100 7/1/18 0.2714 0.0100 8/1/18 0.1507 0.0903 0.0100 9/1/18 0.1417 0.0612 0.0100 10/1/18 0.3573 0.1468 0.0100 11/1/18 0.2599 0.1121 0.0100 12/1/18 0.5581 0.2250 0.0100 0.0273 0.1908 0.0613 0.2132 -0.0465 0.3097 0.3337 0.2037 0.3236 0.2006 0.2449 0.4413 0.2573 0.0270 0.4897 0.3043 0.3022 0.1211 0.1766 0.1156 0.2945 0.2690 0.0558 0.2013 0.0162 0.3133 0.3123 0.1901 0.2542 0.1733 0.4758 0.1586 0.0394 0.1154 0.1313 0.2665 0.1356 0.1455 0.0772 0.1203 -0.0942 0.3681 0.068 0.3401 0.0754 0.3544 0.1435 0.3365 0.1586 0.3675 0.2614 0.1407 0.1317 0.3473 0.2499 0.5481 m excess return 0.1323 0.0818 -0.0908 0.1075 -0.0965 -0.0324 -0.0884 0.0160 -0.0772 0.1953 0.1806 0.1407 0.1979 0.0889 0.0193 0.1244 -0.0302 0.0088 0.2150 0.1526 0.0715 0.0849 -0.0215 0.0863 0.1332 0.2060 0.0193 0.0138 -0.0666 0.1905 0.2046 0.0741 -0.0179 -0.0831 0.2125 -0.0025 -0.0938 -0.0008 -0.0926 0.0479 -0.0576 0.0930 -0.0666 0.0891 -0.0901 0.1685 0.0373 0.1894 -0.0423 0.2240 -0.0348 0.0346 -0.0107 0.1559 0.1708 0.0803 0.0512 6/1/17 0.1808 0.1368 0.1021 0.2150 65 66 67 E F G . 1 2 3 GE excess retum 0.3956 0.3519 0.0037 0.1719 4 a) GE beta CAPM expected return 5 6 7 8 9 10 11 12 13 b) Portfolio beta c) Portfolio vol 14 d) Portfolio vol 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 A B C D GE return m retum rf 1/1/14 0.4056 0.1423 0.0100 2/1/14 0.3619 0.0918 0.0100 3/1/14 0.0137 -0.0808 0.0100 4/1/14 0.1819 0.1175 0.0100 5/1/14 0.0373 -0.0865 0.0100 6/1/14 0.2008 -0.0224 0.0100 7/1/14 0.0713 -0.0784 0.0100 8/1/14 0.2232 0.0260 0.0100 9/1/14 -0.0365 -0.0672 0.0100 10/1/14 0.3197 0.2053 0.0100 11/1/14 0.3437 0.1906 0.0100 12/1/14 0.2137 0.1507 0.0100 1/1/15 0.3336 0.2079 0.0100 2/1/15 0.2106 0.0989 0.0100 3/1/15 0.2549 0.0293 0.0100 4/1/15 0.4513 0.1344 0.0100 5/1/15 0.2673 -0.0202 0.0100 6/1/15 0.0370 0.0188 0.0100 7/1/15 0.4997 0.2250 0.0100 8/1/15 0.3143 0.1626 0.0100 9/1/15 0.3122 0.0815 0.0100 10/1/15 0.1311 0.0949 0.0100 11/1/15 0.1866 -0.0115 0.0100 12/1/15 0.1256 0.0963 0.0100 1/1/16 0.3045 0.1432 0.0100 2/1/16 0.2790 0.2160 0.0100 3/1/16 0.0658 0.0293 0.0100 4/1/16 0.2183 0.0238 0.0100 5/1/16 0.0262 -0.0566 0.0100 6/1/16 0.3233 0.2005 0.0100 7/1/16 0.3223 0.2146 0.0100 8/1/16 0.2001 0.0841 0.0100 9/1/16 0.2642 -0.0079 0.0100 10/1/16 0.1833 -0.0731 0.0100 11/1/16 0.4858 0.2225 0.0100 12/1/16 0.1686 0.0075 0.0100 1/1/17 0.0494 -0.0838 0.0100 2/1/17 0.1254 0.0092 0.0100 3/1/17 0.1413 -0.0826 0.0100 4/1/17 0.2765 0.0579 0.0100 5/1/17 0.1456 -0.0476 0.0100 0.1555 0.1030 0.0100 7/1/17 0.0872 -0.0566 0.0100 8/1/17 0.130 0.0991 0.0100 9/1/17 -0.0841 -0.0801 0.0100 10/1/17 0.3781 0.1785 0.0100 11/1/17 0.0781 0.0473 0.0100 12/1/17 0.3501 0.1994 0.0100 1/1/18 0.0854 -0.0323 0.0100 2/1/18 0.3644 0.2340 0.0100 3/1/18 0.1535 -0.0248 0.0100 4/1/18 0.3465 0.0446 0.0100 5/1/18 0.1686 -0.0007 0.0100 6/1/18 0.3775 0.1659 0.0100 7/1/18 0.2714 0.0100 8/1/18 0.1507 0.0903 0.0100 9/1/18 0.1417 0.0612 0.0100 10/1/18 0.3573 0.1468 0.0100 11/1/18 0.2599 0.1121 0.0100 12/1/18 0.5581 0.2250 0.0100 0.0273 0.1908 0.0613 0.2132 -0.0465 0.3097 0.3337 0.2037 0.3236 0.2006 0.2449 0.4413 0.2573 0.0270 0.4897 0.3043 0.3022 0.1211 0.1766 0.1156 0.2945 0.2690 0.0558 0.2013 0.0162 0.3133 0.3123 0.1901 0.2542 0.1733 0.4758 0.1586 0.0394 0.1154 0.1313 0.2665 0.1356 0.1455 0.0772 0.1203 -0.0942 0.3681 0.068 0.3401 0.0754 0.3544 0.1435 0.3365 0.1586 0.3675 0.2614 0.1407 0.1317 0.3473 0.2499 0.5481 m excess return 0.1323 0.0818 -0.0908 0.1075 -0.0965 -0.0324 -0.0884 0.0160 -0.0772 0.1953 0.1806 0.1407 0.1979 0.0889 0.0193 0.1244 -0.0302 0.0088 0.2150 0.1526 0.0715 0.0849 -0.0215 0.0863 0.1332 0.2060 0.0193 0.0138 -0.0666 0.1905 0.2046 0.0741 -0.0179 -0.0831 0.2125 -0.0025 -0.0938 -0.0008 -0.0926 0.0479 -0.0576 0.0930 -0.0666 0.0891 -0.0901 0.1685 0.0373 0.1894 -0.0423 0.2240 -0.0348 0.0346 -0.0107 0.1559 0.1708 0.0803 0.0512 6/1/17 0.1808 0.1368 0.1021 0.2150 65 66 67
