Please do this financial project for ONLY question four(4) that includes five small parts. I will attach project and one example that maybe help you to do this.

monthly returns Data downloaded from Yahoo Date 8/1/2007 7/2/2007 6/1/2007 5/1/2007 4/2/2007 3/1/2007 2/1/2007 1/3/2007 12/1/2006 11/1/2006 10/2/2006 9/1/2006 8/1/2006 7/3/2006 6/1/2006 5/1/2006 4/3/2006 3/1/2006 2/1/2006 1/3/2006 12/1/2005 11/1/2005 10/3/2005 9/1/2005 8/1/2005 7/1/2005 6/1/2005 5/2/2005 4/1/2005 3/1/2005 2/1/2005 1/3/2005 12/1/2004 11/1/2004 10/1/2004 9/1/2004 8/2/2004 7/1/2004 6/1/2004 5/3/2004 4/1/2004 3/1/2004 2/2/2004 1/2/2004 12/1/2003 11/3/2003 10/1/2003 9/2/2003 8/1/2003 7/1/2003 6/2/2003 5/1/2003 4/1/2003 3/3/2003 2/3/2003 1/2/2003 12/2/2002 11/1/2002 10/1/2002 9/3/2002 8/1/2002 7/1/2002 Open 84.99 84.5 83.56 79.5 75.35 70.9 74.58 76.26 76.78 71.58 67.29 67.85 67.24 61.8 60.4 63.4 61.36 59.59 62.77 56.42 58.37 56.09 63.55 60.1 59.23 57.75 56.45 57 60.1 62.97 51.98 51.02 51.26 49.4 48.45 46.2 46.29 44.53 43.5 42.9 41.9 42.58 41.26 41.02 36.5 36.92 36.92 37.79 35.57 35.95 36.53 35.32 35.25 34.39 34.16 35 35.15 33.8 32.18 34.25 35.25 40.77 High 87.3 93.62 86.58 84.32 80.88 76.35 76.1 76.27 79 77.37 72.33 68.65 71.22 67.94 62.65 64.77 65 61.92 63.08 63.96 60.38 60.26 63.89 65.96 61.34 60.73 61.13 58.65 61.74 64.37 64.04 51.97 52.05 52 50.46 49.79 46.94 46.82 45.53 44.24 43.97 43.4 42.75 41.77 41.13 36.99 38.93 38.5 37.74 36.4 38.45 36.77 36 36.2 35.09 36.6 36.22 35.85 36.5 35.8 37.8 41.1 Low Close 83.61 84.12 80.85 79.05 75.28 69.02 71.18 70.64 74.82 70.27 64.84 63.87 67.01 61.63 56.64 59.15 60.43 58.44 58.6 56.42 55.6 55.84 54.5 60.1 57.76 57.6 56.33 52.78 55.4 57.38 51.93 49.25 48.9 48.25 48.18 46.03 44.2 44.2 43.1 41.59 41.43 39.91 40.05 40.02 36.22 35.05 36.12 36.35 35.12 34.9 35.91 34.99 34.2 33.23 32.32 31.58 34.1 33 32.03 31.18 32.32 29.75 85.84 85.13 83.88 83.17 79.38 75.45 71.68 74.1 76.63 76.81 71.42 67.1 67.67 67.74 61.35 60.91 63.08 60.86 59.37 62.75 56.17 58.03 56.14 63.54 59.9 58.75 57.47 56.2 57.03 59.6 63.31 51.6 51.26 51.25 49.22 48.33 46.1 46.3 44.41 43.25 42.55 41.59 42.17 40.79 41 36.2 36.58 36.6 37.7 35.58 35.91 36.4 35.2 34.95 34.02 34.15 34.94 34.8 33.66 31.9 35.45 36.76 Volume 77591400 28263400 25316000 21117100 23657200 28874700 24060100 26449100 19954400 20701700 21755300 24726500 22894000 22007100 24370100 20876700 19051100 19242900 20284700 21662600 16581900 19671400 25924300 21276100 18555800 17362000 17256700 20132700 21283700 25986600 21245800 12949200 13430100 12241200 12479100 13146900 11732500 10873000 12529300 12400600 11350300 12952800 11209500 12471600 12256100 12066300 11860700 11233600 10534600 12612700 12306700 12119500 11951800 13595200 12288500 13564300 12247800 11234400 15007700 12843600 12063500 19914000 xom's risk and return mean return std dev Adj Close 85.5 84.79 83.55 82.84 78.72 74.83 71.09 73.18 75.67 75.85 70.23 65.98 66.54 66.3 60.04 59.61 61.43 59.27 57.82 60.78 54.41 56.21 54.11 61.24 57.73 56.34 55.12 53.9 54.42 56.87 60.41 49 48.67 48.66 46.49 45.64 43.54 43.47 41.69 40.6 39.69 38.79 39.34 37.81 38.01 33.56 33.67 33.69 34.71 32.53 32.83 33.28 31.95 31.73 30.88 30.79 31.5 31.38 30.15 28.58 31.76 32.72 0.008373629 0.0148414123 0.0085707388 0.0523373984 0.0519844982 0.0526093684 -0.0285597158 -0.0329060394 -0.0023731048 0.0800227823 0.0644134586 -0.0084159904 0.0036199095 0.1042638241 0.0072135548 -0.029627218 0.0364433946 0.0250778277 -0.0487002303 0.1170740673 -0.0320227717 0.0388098318 -0.1164271718 0.0608002772 0.0246716365 0.0221335269 0.0226345083 -0.0095553105 -0.0430807104 -0.0585995696 0.2328571429 0.0067803575 0.0002055076 0.0466767047 0.018624014 0.0482315113 0.001610306 0.0426960902 0.0268472906 0.0229276896 0.0232018561 -0.0139806812 0.0404654853 -0.0052617732 0.1325983313 -0.0032670033 -0.000593648 -0.029386344 0.067015063 -0.0091379836 -0.0135216346 0.041627543 0.0069335014 0.0275259067 0.002923027 -0.0225396825 0.0038240918 0.0407960199 0.0549335199 -0.1001259446 -0.0293398533 monthly annualized 1.718% 22.68% 5.263% 18.23% The mean and std. dev of monthly returns are estimates based on monthly data reported on Column H. annualized return = ( 1+ monthly avg ret)^12 - 1 annualized std dev = sqrt(12) * monthly std dev Data downloaded from Yahoo Date Open High Low Close 8/1/2007 1455.18 1468.38 1439.59 1465.81 7/2/2007 1504.66 1555.9 1454.25 1455.27 6/1/2007 1530.62 1540.56 1484.18 1503.35 5/1/2007 1482.37 1535.56 1476.7 1530.62 4/2/2007 1420.83 1498.02 1416.37 1482.37 3/1/2007 1406.8 1438.89 1363.98 1420.86 2/1/2007 1437.9 1461.57 1389.42 1406.82 1/3/2007 1418.03 1441.61 1403.97 1438.24 12/1/2006 1400.63 1431.81 1385.93 1418.3 11/1/2006 1377.76 1407.89 1360.98 1400.63 10/2/2006 1335.82 1389.45 1327.1 1377.94 9/1/2006 1303.8 1340.28 1290.93 1335.85 8/1/2006 1278.53 1306.74 1261.3 1303.82 7/3/2006 1270.06 1280.42 1224.54 1276.66 6/1/2006 1270.05 1290.68 1219.29 1270.2 5/1/2006 1310.61 1326.7 1245.34 1270.09 4/3/2006 1302.88 1318.16 1280.74 1310.61 3/1/2006 1280.66 1310.88 1268.42 1294.87 2/1/2006 1280.08 1297.57 1253.61 1280.66 1/3/2006 1248.29 1294.9 1245.74 1280.08 12/1/2005 1249.48 1275.8 1246.59 1248.29 11/1/2005 1207.01 1270.64 1201.07 1249.48 10/3/2005 1228.81 1233.34 1168.2 1207.01 9/1/2005 1220.33 1243.13 1205.35 1228.81 8/1/2005 1234.18 1245.86 1201.07 1220.33 7/1/2005 1191.33 1245.15 1183.55 1234.18 6/1/2005 1191.5 1219.59 1188.3 1191.33 5/2/2005 1156.85 1199.56 1146.18 1191.5 4/1/2005 1180.59 1191.88 1136.15 1156.85 3/1/2005 1203.6 1229.11 1163.69 1180.59 2/1/2005 1181.27 1212.44 1180.95 1203.6 1/3/2005 1211.92 1217.8 1163.75 1181.27 12/1/2004 1173.78 1217.33 1173.78 1211.92 11/1/2004 1130.2 1188.46 1127.6 1173.82 10/1/2004 1114.58 1142.05 1090.29 1130.2 9/1/2004 1104.24 1131.54 1099.18 1114.58 8/2/2004 1101.72 1109.68 1060.72 1104.24 7/1/2004 1140.84 1140.84 1078.78 1101.72 6/1/2004 1120.68 1146.34 1113.32 1140.84 5/3/2004 1107.3 1127.74 1076.32 1120.68 4/1/2004 1126.21 1150.57 1107.23 1107.3 3/1/2004 1144.94 1163.23 1087.16 1126.21 2/2/2004 1131.13 1158.98 1124.44 1144.94 1/2/2004 1111.92 1155.38 1105.08 1131.13 12/1/2003 1058.2 1112.56 1053.41 1111.92 11/3/2003 1050.71 1063.65 1031.2 1058.2 10/1/2003 995.97 1053.79 995.97 1050.71 9/2/2003 1008.01 1040.29 990.36 995.97 8/1/2003 990.31 1011.01 960.84 1008.01 7/1/2003 974.5 1015.41 962.1 990.31 6/2/2003 963.59 1015.33 963.59 974.5 5/1/2003 916.92 965.38 902.83 963.59 4/1/2003 848.18 924.24 847.85 916.92 3/3/2003 841.15 895.9 788.9 848.18 2/3/2003 855.7 864.64 806.29 841.15 1/2/2003 879.82 935.05 840.34 855.7 12/2/2002 936.31 954.28 869.45 879.82 11/1/2002 885.76 941.82 872.05 936.31 10/1/2002 815.28 907.44 768.63 885.76 9/3/2002 916.07 924.02 800.2 815.28 8/1/2002 911.62 965 833.44 916.07 7/1/2002 989.82 994.46 775.68 911.62 monthly returns Volume Adj Close monthly return 1.1E+010 1465.81 0.0072426423 3.6E+009 1455.27 -0.0319819071 3.3E+009 1503.35 -0.0178163097 3.1E+009 1530.62 0.0325492286 3.0E+009 1482.37 0.0432906831 3.2E+009 1420.86 0.0099799548 2.9E+009 1406.82 -0.0218461453 3.0E+009 1438.24 0.0140590848 2.5E+009 1418.3 0.0126157515 2.8E+009 1400.63 0.0164666096 2.7E+009 1377.94 0.0315080286 2.6E+009 1335.85 0.0245662745 2.3E+009 1303.82 0.0212742625 2.4E+009 1276.66 0.0050858133 2.6E+009 1270.2 0.000086608 2.6E+009 1270.09 -0.0309169013 2.4E+009 1310.61 0.0121556604 2.3E+009 1294.87 0.0110958412 2.4E+009 1280.66 0.0004530967 2.6E+009 1280.08 0.0254668386 2.1E+009 1248.29 -0.0009523962 2.3E+009 1249.48 0.0351861211 2.5E+009 1207.01 -0.017740741 2.2E+009 1228.81 0.00694894 1.9E+009 1220.33 -0.011222026 2.0E+009 1234.18 0.0359682036 1.9E+009 1191.33 -0.0001426773 2.0E+009 1191.5 0.0299520249 2.2E+009 1156.85 -0.0201085898 1.9E+009 1180.59 -0.0191176471 1.6E+009 1203.6 0.0189033836 1.7E+009 1181.27 -0.0252904482 1.4E+009 1211.92 0.0324581282 1.5E+009 1173.82 0.0385949389 1.6E+009 1130.2 0.0140142475 1.4E+009 1114.58 0.0093639064 1.3E+009 1104.24 0.0022873325 1.5E+009 1101.72 -0.0342905228 1.4E+009 1140.84 0.0179890781 1.5E+009 1120.68 0.0120834462 1.6E+009 1107.3 -0.0167908294 1.5E+009 1126.21 -0.0163589358 1.6E+009 1144.94 0.0122090299 1.7E+009 1131.13 0.0172764228 1.3E+009 1111.92 0.0507654508 1.3E+009 1058.2 0.0071285131 1.5E+009 1050.71 0.0549614948 1.5E+009 995.97 -0.0119443259 1.2E+009 1008.01 0.0178731912 1.5E+009 990.31 0.0162237045 1.6E+009 974.5 0.0113222429 1.6E+009 963.59 0.0508986607 1.5E+009 916.92 0.081044118 1.5E+009 848.18 0.0083576057 1.4E+009 841.15 -0.0170036228 1.5E+009 855.7 -0.0274146985 1.3E+009 879.82 -0.0603325822 1.5E+009 936.31 0.0570696351 1.7E+009 885.76 0.0864488274 1.5E+009 815.28 -0.1100243431 1.4E+009 916.07 0.0048814199 2.0E+009 911.62 0.83% 3.154% 10.44% 10.93% monthly returns Data downloaded from Yahoo Date Open High Low Close 8/1/2007 84.99 87.3 83.61 85.84 7/2/2007 84.5 93.62 84.12 85.13 6/1/2007 83.56 86.58 80.85 83.88 5/1/2007 79.5 84.32 79.05 83.17 4/2/2007 75.35 80.88 75.28 79.38 Volume Adj Close 77591400 85.5 28263400 84.79 25316000 83.55 21117100 82.84 23657200 78.72 3/1/2007 2/1/2007 1/3/2007 12/1/2006 11/1/2006 10/2/2006 9/1/2006 8/1/2006 7/3/2006 6/1/2006 5/1/2006 4/3/2006 3/1/2006 2/1/2006 1/3/2006 12/1/2005 11/1/2005 10/3/2005 9/1/2005 8/1/2005 7/1/2005 6/1/2005 5/2/2005 4/1/2005 3/1/2005 2/1/2005 1/3/2005 12/1/2004 11/1/2004 10/1/2004 9/1/2004 8/2/2004 7/1/2004 6/1/2004 5/3/2004 4/1/2004 3/1/2004 2/2/2004 1/2/2004 12/1/2003 11/3/2003 10/1/2003 9/2/2003 8/1/2003 7/1/2003 6/2/2003 5/1/2003 4/1/2003 3/3/2003 2/3/2003 1/2/2003 12/2/2002 11/1/2002 10/1/2002 9/3/2002 8/1/2002 7/1/2002 28874700 24060100 26449100 19954400 20701700 21755300 24726500 22894000 22007100 24370100 20876700 19051100 19242900 20284700 21662600 16581900 19671400 25924300 21276100 18555800 17362000 17256700 20132700 21283700 25986600 21245800 12949200 13430100 12241200 12479100 13146900 11732500 10873000 12529300 12400600 11350300 12952800 11209500 12471600 12256100 12066300 11860700 11233600 10534600 12612700 12306700 12119500 11951800 13595200 12288500 13564300 12247800 11234400 15007700 12843600 12063500 19914000 70.9 74.58 76.26 76.78 71.58 67.29 67.85 67.24 61.8 60.4 63.4 61.36 59.59 62.77 56.42 58.37 56.09 63.55 60.1 59.23 57.75 56.45 57 60.1 62.97 51.98 51.02 51.26 49.4 48.45 46.2 46.29 44.53 43.5 42.9 41.9 42.58 41.26 41.02 36.5 36.92 36.92 37.79 35.57 35.95 36.53 35.32 35.25 34.39 34.16 35 35.15 33.8 32.18 34.25 35.25 40.77 76.35 76.1 76.27 79 77.37 72.33 68.65 71.22 67.94 62.65 64.77 65 61.92 63.08 63.96 60.38 60.26 63.89 65.96 61.34 60.73 61.13 58.65 61.74 64.37 64.04 51.97 52.05 52 50.46 49.79 46.94 46.82 45.53 44.24 43.97 43.4 42.75 41.77 41.13 36.99 38.93 38.5 37.74 36.4 38.45 36.77 36 36.2 35.09 36.6 36.22 35.85 36.5 35.8 37.8 41.1 69.02 71.18 70.64 74.82 70.27 64.84 63.87 67.01 61.63 56.64 59.15 60.43 58.44 58.6 56.42 55.6 55.84 54.5 60.1 57.76 57.6 56.33 52.78 55.4 57.38 51.93 49.25 48.9 48.25 48.18 46.03 44.2 44.2 43.1 41.59 41.43 39.91 40.05 40.02 36.22 35.05 36.12 36.35 35.12 34.9 35.91 34.99 34.2 33.23 32.32 31.58 34.1 33 32.03 31.18 32.32 29.75 75.45 71.68 74.1 76.63 76.81 71.42 67.1 67.67 67.74 61.35 60.91 63.08 60.86 59.37 62.75 56.17 58.03 56.14 63.54 59.9 58.75 57.47 56.2 57.03 59.6 63.31 51.6 51.26 51.25 49.22 48.33 46.1 46.3 44.41 43.25 42.55 41.59 42.17 40.79 41 36.2 36.58 36.6 37.7 35.58 35.91 36.4 35.2 34.95 34.02 34.15 34.94 34.8 33.66 31.9 35.45 36.76 74.83 71.09 73.18 75.67 75.85 70.23 65.98 66.54 66.3 60.04 59.61 61.43 59.27 57.82 60.78 54.41 56.21 54.11 61.24 57.73 56.34 55.12 53.9 54.42 56.87 60.41 49 48.67 48.66 46.49 45.64 43.54 43.47 41.69 40.6 39.69 38.79 39.34 37.81 38.01 33.56 33.67 33.69 34.71 32.53 32.83 33.28 31.95 31.73 30.88 30.79 31.5 31.38 30.15 28.58 31.76 32.72 0.008373629 0.0148414123 0.0085707388 0.0523373984 0.0519844982 0.0526093684 -0.0285597158 -0.0329060394 -0.0023731048 0.0800227823 0.0644134586 -0.0084159904 0.0036199095 0.1042638241 0.0072135548 -0.029627218 0.0364433946 0.0250778277 -0.0487002303 0.1170740673 -0.0320227717 0.0388098318 -0.1164271718 0.0608002772 0.0246716365 0.0221335269 0.0226345083 -0.0095553105 -0.0430807104 -0.0585995696 0.2328571429 0.0067803575 0.0002055076 0.0466767047 0.018624014 0.0482315113 0.001610306 0.0426960902 0.0268472906 0.0229276896 0.0232018561 -0.0139806812 0.0404654853 -0.0052617732 0.1325983313 -0.0032670033 -0.000593648 -0.029386344 0.067015063 -0.0091379836 -0.0135216346 0.041627543 0.0069335014 0.0275259067 0.002923027 -0.0225396825 0.0038240918 0.0407960199 0.0549335199 -0.1001259446 -0.0293398533 xom sp500 std dev 0.05263 0.03154 stdev^2 var 0.00277 0.00277 0.000995 0.000995 correlation between XOM and S&P 500 rho = 0.4538 =correl() covariance between XOM and S&P 500 covariance = 0.00075330 = rho * STDEV(Xom) * STDEV(SP5 beta of Xom against the S&P 500 method 1 - based on the definition of beta: beta = 0.7570626 =rho * STDEV(Xom) / STDEV(SP5 method 2 - using the Excel function Slope(): beta = 0.7570626 =SLOPE(H3:H63,'sp500-5yr'!H2:H =slope(y's, x's) y's: stock's returns (or excess retur x's: the benchmark index's returns SP500) P500) 2:H62) eturns) ns (or excess returns) Fin 406 - Fall 2015 Security Analysis & Portfolio Management Dr. JingZhi (Jay) Huang Smeal College of Business Penn State University PROJECT 1 (due October 22, Thursday) As indicated on the syllabus, students are strongly encouraged to work on this project as a group (up to six students in each group) and can form a group with students from other sections. Each group needs to hand in one report for the project. The report can be typed. Please turn in a hard copy of the report on the date due and do not email the report. Please also write down the names of all the group members and their section numbers on the cover page of the report. This project consists of five questions as follows. 1. Estimate the correlation between two assets based on real data. (a) Download monthly prices of the following four ETFs: SPY, SDS, SSO, and SHV over the period 08/01/2007-09/15/2011 from Yahoo. (b) Describe briefly what these four ETFs are. (c) Compute their monthly returns using the adj. close prices. (d) Estimate the correlation coefficient between SPY and each of the other three ETFs using Excel function CORREL. Are your results consistent with your intuition? You can refer to Example 6.1 in the textbook (p. 155; p. 152 on the 8e) on how to use this excel function to compute the correlation. You can refer to the following examples posted on Angel under \"Projects\": (i) \"howToGetHistPrcFromYahoo.pdf\" has instructions on how to download historical prices from Yahoo and then calculate returns using the prices. (ii) \"example-correlation-beta-slope.xls\" illustrates how to calculate first monthly returns of XOM and S&P500 from historical prices and then estimate the correlation between the returns of XOM and S&P500 using function CORREL. Things to turn in: A spreadsheet printout that includes (a) a brief description of SPY, SDS, SSO, and SHV; (b) the monthly returns of the four ETFs; and (c) the estimates of the three correlation coefficients. (Note: To save paper, please place all four monthly return series in one spreadsheet before printing.) Finance Departmetn, Smeal College of Business, Penn State University Page 1 2. Construct the efficient frontier of SPY and LQD using real data. (a) Download weekly prices of the SPY and LQD over the period 12/29/2008-01/05/2010 from Yahoo and then compute their weekly returns using the adj. close prices. (b) Estimate first the avg. ret. and std. dev. of these two ETFs, and then their correlation. (c) Annualize the avg. ret. and std. dev. of SYP and LQD obtained in part (c) using weekly data. (Correlation need not be annualized.) You can use the following formulas: annualized return = (1 + weekly ret)^52 - 1; annualized std dev = weekly std dev * sqrt(52). (d) Tabulate the investment opportunity set of these two ETFs. Namely, select, say, 20 different portfolios (of SPY and LQD), with SPY's weight ranging from -0.5 to 1.5 with an incremental of 0.1. Then calculate the exp. ret. and std. dev. for each of the 20 portfolios. Spreadsheet 6.5 in the textbook (p.158; p. 155 on the 8e) provides a similar example. (d) Plot the expected returns vs. the standard deviations to generate a curve (whose upper branch is the efficient frontier). If you want, you can use \"example-efficientFrontier.xls\" posted on Angel as a template. Things to turn in: A spreadsheet printout that includes (a) a brief description of SPY and LQD; (b) the estimates of both weekly and annualized avg. ret. and std. dev. of the two ETFs, and the correlation; (c) the tabulated investment opportunity set and the corresponding portfolio expected returns and standard deviations; and (d) a properly labeled graph of the investment opportunity set. 3. Consider an investor who plans to allocate $10,000 to SPY, LQD, and cash, using the asset allocation model introduced in our class. Use the (annualized) avg. returns, std. devs., and the correlation estimated earlier in Question 2 (part C). The risk free is assumed to be 4%. (a) Determine the tangency portfolio using Eq. (6.10) in the textbook (both 8e and 9e). (b) Consider two investors whose risk aversion coefficients are 8 and 12, respectively. Determine these two investors' optimal complete portfolios. You can use \"example-assetAllocator.xls\" posted on Angel as a template for this exercise. Things to turn in: A spreadsheet printout that includes (a) the annualized avg. ret. and std. dev. of the two ETFs and the correlation coefficient, and the risk-free interest; (b) the tangency portfolio weight on SPY and LQD; (c) the expected return and std. dev. of the tangency portfolio; and (d) two investor's optimal complete portfolios (including both portfolio weights and dollar amounts on the three asset classes). 4. Analyze the performance of the mutual fund LMVTX, once managed by the well-known fund manager Bill Miller. (a) Download monthly prices of the LMVTX and the S&P 500 index (Yahoo ticker ^GSPC) over the period 10/02/2000-09/30/2003 from Yahoo and then compute their monthly returns using the adj. close prices. So the return series will start from Nov. 1, 2000. Finance Departmetn, Smeal College of Business, Penn State University Page 2 (b) Calculate the excess returns of LMVTX and the S&P 500. The data on the risk-free rate are available in \"risk-free-rate.xls\" posted on Angel under the folder \"Projects\" (c) Estimate LMVTX's alpha and beta against the S&P500 using regression in Excel. On how to run regression, you can refer to Example 6.3 in the textbook and/or the example on LMVTX in the lecture notes lec09-10 (see the appendix). (d) Re-estimate the beta parameter using the Excel function SLOPE. You should get the same result as the regression beta coefficient. Note: Function SLOPE has two arguments, namely, slope(y-array, x-array), where the 1st argument represents the y-variable (the fund LMVTX's excess returns here) and the 2nd argument represents the x-variable (the S&P's excess returns here). (e) Replace the S&P500 index by the Russell 2000 index (Yahoo ticker ^RUT), and then redo the regression analysis to obtain the alpha and beta of LMVTX. Note: (i) \"example-correlation-beta-slope.xls\" posted on Angel illustrates how to estimate XOM's beta against the S&P500 using Function SLOPE. (ii) If you do it correctly, your estimates of alpha and beta should be close to (not necessarily the same as) those shown in the lecture notes. Things to turn in: A spreadsheet printout that includes (a) the monthly returns of LMVTX, the S&P500, the Russell 2000, and the risk-free interest; (b) a graph of LMVTX vs. the S&P 500 that includes the regression line, the regression equation and the R-squared; and (c) a graph of LMVTX vs. the Russell 2000 that includes the regression line, the regression equation and the R-squared; 5. Consider two Select Sector SPDRs (ETFs), the Consumer Staples (ticker: XLP) and Consumer Discretionary (ticker: XLY). Which SPDR do you expect to have a higher(lower-) than-average beta (against the S&P 500)? Now estimate these two SPDRs' beta and see if they are consistent with your intuition. (a) Download monthly prices of XLP, XLY and the S&P 500 index (Yahoo ticker ^GSPC) over the past three years from Yahoo and then compute their monthly returns using the adj. close prices. (b) Beta can be estimated using either returns or excess returns. As such, please estimate each SPDR's beta (against the S&P 500) using the Excel function SLOPE. Note: Use slope(y-array, x-array), where the 1st argument represents the y-variable (returns of an ETF) and the 2nd argument represents the x-variable (returns of the S&P 500). Things to turn in: A spreadsheet printout that includes (a) a brief description of XLP and XLY; (b) the monthly returns of XLP, XLY and the S&P 500 index over the past three years; (c) your estimates of XLP's and XLY's betas against the S&P 500; (d) Intuitively, which ETF is expected to have a higher beta? And are your beta estimates consistent with your intuition? Finance Departmetn, Smeal College of Business, Penn State University Page 3 Date 2-Sep-03 1-Aug-03 1-Jul-03 2-Jun-03 1-May-03 1-Apr-03 3-Mar-03 3-Feb-03 2-Jan-03 2-Dec-02 1-Nov-02 1-Oct-02 3-Sep-02 1-Aug-02 1-Jul-02 3-Jun-02 1-May-02 1-Apr-02 1-Mar-02 4-Feb-02 2-Jan-02 3-Dec-01 1-Nov-01 1-Oct-01 4-Sep-01 1-Aug-01 2-Jul-01 1-Jun-01 1-May-01 2-Apr-01 1-Mar-01 1-Feb-01 2-Jan-01 1-Dec-00 1-Nov-00 Risk-free (3m T-bills) 0.0008 0.000775 0.0007 0.000908 0.000917 0.000908 0.000975 0.000958 0.000983 0.001 0.001183 0.001275 0.001367 0.001383 0.001383 0.001425 0.001442 0.00145 0.001433 0.001433 0.001392 0.001442 0.001675 0.001917 0.002733 0.002867 0.002967 0.00295 0.003192 0.003483 0.003933 0.004033 0.004775 0.005008 0.005125