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?

D E F H K L 1 2 3 4 5 6 7 8 a) GE beta CAPM expected return 9 b) Portfolio beta c) Portfolio vol d) Portfolio vol 10 11 12 13 14 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 B GE return m return 1/1/14 0.4056 0.1423 2/1/14 0.3619 0.0918 3/1/14 0.0137 -0.0808 4/1/14 0.1819 0.1175 5/1/14 0.0373 -0.0865 6/1/14 0.2008 -0.0224 7/1/14 0.0713 -0.0784 8/1/14 0.2232 0.0260 9/1/14 -0.0365 -0.0672 10/1/14 0.3197 0.2053 11/1/14 0.3437 0.1906 12/1/14 0.2137 0.1507 1/1/15 0.3336 0.2079 2/1/15 0.2106 0.0989 3/1/15 0.2549 0.0293 4/1/15 0.4513 0.1344 5/1/15 0.2673 -0.0202 6/1/15 0.0370 0.0188 7/1/15 0.4997 0.2250 8/1/15 0.3143 0.1626 9/1/15 0.3122 0.0815 10/1/15 0.1311 0.0949 11/1/15 0.1866 -0.0115 12/1/15 0.1256 0.0963 1/1/16 0.3045 0.1432 2/1/16 0.2790 0.2160 3/1/16 0.0658 0.0293 4/1/16 0.2183 0.0238 5/1/16 0.0262 -0.0566 6/1/16 0.3233 0.2005 7/1/16 0.3223 0.2146 8/1/16 0.2001 0.0841 9/1/16 0.2642 -0.0079 10/1/16 0.1833 -0.0731 11/1/16 0.4858 0.2225 12/1/16 0.1686 0.0075 1/1/17 0.0494 -0.0838 2/1/17 0.1254 0.0092 3/1/17 0.1413 -0.0826 4/1/17 0.2765 0.0579 5/1/17 0.1456 -0.0476 6/1/17 0.1555 0.1030 7/1/17 0.0872 -0.0566 8/1/17 0.1303 0.0991 9/1/17 -0.0841 -0.0801 10/1/17 0.3781 0.1785 11/1/17 0.0781 0.0473 12/1/17 0.3501 0.1994 1/1/18 0.0854 -0.0323 2/1/18 0.3644 0.2340 3/1/18 0.1535 -0.0248 4/1/18 0.3465 0.0446 5/1/18 0.1686 -0.0007 6/1/18 0.3775 0.1659 7/1/18 0.2714 0.1808 8/1/18 0.1507 0.0903 9/1/18 0.1417 0.0612 10/1/18 0.3573 0.1468 11/1/18 0.2599 0.1121 12/1/18 0.5581 0.2250 rf 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 GE excess return 0.3956 0.3519 0.0037 0.1719 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.2083 0.0162 0.3133 0.3123 0.1901 0.2542 0.1733 0.4758 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.1586 44 -0.0901 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 0.0394 0.1154 0.1313 0.2665 0.1356 0.1455 0.0772 0.1203 -0.0941 0.3681 0.0681 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 0.1685 0.0373 0.1894 -0.0423 0.2240 -0.0348 0.0346 -0.0107 0.1559 0.1708 0.0803 0.0512 0.1368 0.1021 0.2150 60 61 62 D E F H K L 1 2 3 4 5 6 7 8 a) GE beta CAPM expected return 9 b) Portfolio beta c) Portfolio vol d) Portfolio vol 10 11 12 13 14 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 B GE return m return 1/1/14 0.4056 0.1423 2/1/14 0.3619 0.0918 3/1/14 0.0137 -0.0808 4/1/14 0.1819 0.1175 5/1/14 0.0373 -0.0865 6/1/14 0.2008 -0.0224 7/1/14 0.0713 -0.0784 8/1/14 0.2232 0.0260 9/1/14 -0.0365 -0.0672 10/1/14 0.3197 0.2053 11/1/14 0.3437 0.1906 12/1/14 0.2137 0.1507 1/1/15 0.3336 0.2079 2/1/15 0.2106 0.0989 3/1/15 0.2549 0.0293 4/1/15 0.4513 0.1344 5/1/15 0.2673 -0.0202 6/1/15 0.0370 0.0188 7/1/15 0.4997 0.2250 8/1/15 0.3143 0.1626 9/1/15 0.3122 0.0815 10/1/15 0.1311 0.0949 11/1/15 0.1866 -0.0115 12/1/15 0.1256 0.0963 1/1/16 0.3045 0.1432 2/1/16 0.2790 0.2160 3/1/16 0.0658 0.0293 4/1/16 0.2183 0.0238 5/1/16 0.0262 -0.0566 6/1/16 0.3233 0.2005 7/1/16 0.3223 0.2146 8/1/16 0.2001 0.0841 9/1/16 0.2642 -0.0079 10/1/16 0.1833 -0.0731 11/1/16 0.4858 0.2225 12/1/16 0.1686 0.0075 1/1/17 0.0494 -0.0838 2/1/17 0.1254 0.0092 3/1/17 0.1413 -0.0826 4/1/17 0.2765 0.0579 5/1/17 0.1456 -0.0476 6/1/17 0.1555 0.1030 7/1/17 0.0872 -0.0566 8/1/17 0.1303 0.0991 9/1/17 -0.0841 -0.0801 10/1/17 0.3781 0.1785 11/1/17 0.0781 0.0473 12/1/17 0.3501 0.1994 1/1/18 0.0854 -0.0323 2/1/18 0.3644 0.2340 3/1/18 0.1535 -0.0248 4/1/18 0.3465 0.0446 5/1/18 0.1686 -0.0007 6/1/18 0.3775 0.1659 7/1/18 0.2714 0.1808 8/1/18 0.1507 0.0903 9/1/18 0.1417 0.0612 10/1/18 0.3573 0.1468 11/1/18 0.2599 0.1121 12/1/18 0.5581 0.2250 rf 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 GE excess return 0.3956 0.3519 0.0037 0.1719 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.2083 0.0162 0.3133 0.3123 0.1901 0.2542 0.1733 0.4758 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.1586 44 -0.0901 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 0.0394 0.1154 0.1313 0.2665 0.1356 0.1455 0.0772 0.1203 -0.0941 0.3681 0.0681 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 0.1685 0.0373 0.1894 -0.0423 0.2240 -0.0348 0.0346 -0.0107 0.1559 0.1708 0.0803 0.0512 0.1368 0.1021 0.2150 60 61 62