I need help putting the below assignment together in excel. I the solutions to be in excel so that I can cross reference with my notes.

Thanks

FIN 5620-EVT: Jack De Jong: Quantitative Problem Set #3: Due: Tuesday, June 9, 2015 by 6:00 PM: Name: Problem: Points Questions Index Models: (50 points) 1 (10) Avg. Return 2 (5) Excess Ret. Apple: 3 (4) Alpha (4) Beta (2) R-Squared 4 (5) Systematic Firm-specific Total Risk Exxon: 5 (4) Alpha (4) Beta (2) R-Squared 6 (5) Systematic Firm-specific Total Risk Your Answers: Apple Exxon S&P 500 T-Bills 0.025 0.009 0.012 0.056 (0.044) (0.031) (0.047) Measure Inference & Brief Explanation Measure Inference & Brief Explanation Apple & Exxon 7 (5) Covariance Correlation CAPM and APT: (50 points) 1 (5) Risk Aversion 2 (10) Exp. Return 3 (5) Correlation 4 (10) Alpha 5 (10) Exp. Return 6 (10) Arbitrage Date Open High Low Close Volume Adj Close 5/1/2015 87.85 90.09 84.77 85.2 10615700 85.2 4/1/2015 85.7 88.67 83.81 87.37 12648500 86.6533 3/2/2015 88.34 88.52 82.68 85 15618800 84.30273 2/2/2015 87.85 93.45 87.5 88.54 15231200 87.8137 1/2/2015 92.25 93.05 86.03 87.42 17474900 86.05514 12/1/2014 90.36 95.33 86.19 92.45 18730200 91.00661 11/3/2014 96.56 97.2 90.11 90.54 12509200 89.12643 10/1/2014 94.11 96.89 86.91 96.71 15634100 94.51769 9/2/2014 99.43 99.59 93.62 94.05 11782000 91.918 8/1/2014 98.62 100.43 97.63 99.46 9349000 97.20535 7/1/2014 101.15 104.76 98.93 98.94 9439300 96.0282 6/2/2014 100.53 104.61 99.59 100.68 10404500 97.71699 5/1/2014 101.96 103.45 100.32 100.53 9113600 97.5714 4/1/2014 97.65 102.57 96.24 102.41 11660300 98.72579 3/3/2014 95.76 98.85 93.01 97.68 12765400 94.16595 2/3/2014 91.92 97.25 89.25 96.27 13996500 92.80667 1/2/2014 100.6 101.22 91.7 92.16 13058500 88.21971 12/2/2013 93.27 101.74 92.92 101.2 13228500 96.8732 11/1/2013 89.74 96 88.27 93.48 12856000 89.48328 10/1/2013 85.99 90.96 84.79 89.62 13341600 85.20853 9/3/2013 87.7 89.87 85.61 86.04 12800900 81.80476 8/1/2013 92.69 93.1 86.39 87.16 12914100 82.86963 7/1/2013 90.39 95.49 89.96 93.75 11462900 88.52339 6/3/2013 90.53 92.27 88.02 90.35 15737500 85.31294 5/1/2013 88.52 93.5 87.48 90.47 13364200 85.42625 4/1/2013 90.13 90.99 85.02 88.99 14132700 83.45146 3/1/2013 89.07 90.98 87.9 90.11 14126500 84.50175 2/1/2013 90.44 90.5 87.61 89.55 13249100 83.97662 1/2/2013 87.79 91.93 87.24 89.97 13213700 83.83488 12/3/2012 88.55 90.37 84.7 86.55 15112600 80.64809 11/1/2012 90.25 92.05 85.06 88.14 14342700 82.12967 10/1/2012 91.79 93.67 89.68 91.17 12188600 84.42447 9/4/2012 87.33 92.57 86.52 91.45 14037000 84.68375 8/1/2012 87.02 88.91 85.5 87.3 11399500 80.84081 7/2/2012 85.44 87.85 82.83 86.85 15548900 79.90482 6/1/2012 77.87 85.7 77.13 85.57 24899800 78.72718 5/1/2012 86.46 87.52 78.41 78.63 17847200 72.34216 4/2/2012 86.6 87.67 81.88 86.34 15598400 78.89211 3/1/2012 86.47 87.25 84.05 86.73 15994500 79.24847 2/1/2012 84.23 87.83 83.19 86.5 15949600 79.03831 1/3/2012 85.97 87.94 83.46 83.74 18038500 76.09987 12/1/2011 80.2 85.63 78.66 84.76 18567500 77.02682 11/1/2011 75.82 80.53 73.9 80.44 22774900 73.10095 10/3/2011 72.05 82.2 69.21 78.09 25943600 70.54502 9/1/2011 74.15 74.98 67.93 72.63 27687600 65.61256 8/1/2011 80.82 81.18 67.03 74.02 34928400 66.86826 7/1/2011 81.19 85.41 79.76 79.79 20103800 71.60788 6/1/2011 83.55 83.65 76.72 81.38 21103900 73.03483 5/2/2011 88.1 88.13 79.42 83.47 20355400 74.91051 4/1/2011 84.72 88 82.38 87.98 16353700 78.51274 3/1/2011 86.41 86.5 78.8 84.13 19767400 75.07702 2/1/2011 81.14 88.23 81.04 85.53 24636800 76.32637 1/3/2011 73.72 80.82 73.64 80.68 24194700 71.62082 12/1/2010 70.38 73.69 70.38 73.12 19295900 64.9097 11/1/2010 66.72 71.9 66.63 69.56 24468700 61.74943 10/1/2010 62.32 66.81 61.8 66.49 21413900 58.65482 9/1/2010 60.04 62.44 59.72 61.79 21372500 54.50867 8/2/2010 60.64 62.99 58.05 59.11 22846100 52.14449 7/1/2010 56.98 61.88 55.94 59.68 30611600 52.27327 6/1/2010 60.38 64.5 56.92 57.07 38748500 49.98719 5/3/2010 68.11 68.22 58.46 60.46 40034400 52.95646 Date Open High Low Close Volume Adj Close 5/1/2015 2087.3799 2134.72 2067.9299 2107.3899 3.30E+009 2107.3899 4/1/2015 2067.6299 2125.9199 2048.3799 2085.51 3.52E+009 2085.51 3/2/2015 2105.23 2117.52 2039.6899 2067.8899 3.64E+009 2067.8899 2/2/2015 1996.67 2119.5901 1980.9 2104.5 3.81E+009 2104.5 1/2/2015 2058.8999 2072.3601 1988.12 1994.99 4.09E+009 1994.99 12/1/2014 2065.78 2093.5501 1972.5601 2058.8999 3.79E+009 2058.8999 11/3/2014 2018.21 2075.76 2001.01 2067.5601 3.48E+009 2067.5601 10/1/2014 1971.4399 2018.1899 1820.66 2018.0501 4.26E+009 2018.0501 9/2/2014 2004.07 2019.26 1964.04 1972.29 3.36E+009 1972.29 8/1/2014 1929.8001 2005.04 1904.78 2003.37 2.88E+009 2003.37 7/1/2014 1962.29 1991.39 1930.67 1930.67 3.21E+009 1930.67 6/2/2014 1923.87 1968.17 1915.98 1960.23 3.16E+009 1960.23 5/1/2014 1884.39 1924.03 1859.79 1923.57 3.19E+009 1923.57 4/1/2014 1873.96 1897.28 1814.36 1883.95 3.59E+009 1883.95 3/3/2014 1857.6801 1883.97 1834.4399 1872.34 3.58E+009 1872.34 2/3/2014 1782.6801 1867.92 1737.92 1859.45 3.88E+009 1859.45 1/2/2014 1845.86 1850.84 1770.45 1782.59 3.81E+009 1782.59 12/2/2013 1806.5501 1849.4399 1767.99 1848.36 3.20E+009 1848.36 11/1/2013 1758.7 1813.5501 1746.2 1805.8101 3.26E+009 1805.8101 10/1/2013 1682.41 1775.22 1646.47 1756.54 3.50E+009 1756.54 9/3/2013 1635.95 1729.86 1633.41 1681.5501 3.47E+009 1681.5501 8/1/2013 1689.42 1709.67 1627.47 1632.97 3.07E+009 1632.97 7/1/2013 1609.78 1698.78 1604.57 1685.73 3.27E+009 1685.73 6/3/2013 1631.71 1654.1899 1560.33 1606.28 4.00E+009 1606.28 5/1/2013 1597.5501 1687.1801 1581.28 1630.74 3.66E+009 1630.74 4/1/2013 1569.1801 1597.57 1536.03 1597.57 3.67E+009 1597.57 3/1/2013 1514.6801 1570.28 1501.48 1569.1899 3.59E+009 1569.1899 2/1/2013 1498.11 1530.9399 1485.01 1514.6801 3.85E+009 1514.6801 1/2/2013 1426.1899 1509.9399 1426.1899 1498.11 3.80E+009 1498.11 12/3/2012 1416.34 1448 1398.11 1426.1899 3.48E+009 1426.1899 11/1/2012 1412.2 1434.27 1343.35 1416.1801 3.59E+009 1416.1801 10/1/2012 1440.9 1470.96 1403.28 1412.16 3.59E+009 1412.16 9/4/2012 1406.54 1474.51 1396.5601 1440.67 3.86E+009 1440.67 8/1/2012 1379.32 1426.6801 1354.65 1406.58 3.18E+009 1406.58 7/2/2012 1362.33 1391.74 1325.41 1379.32 3.66E+009 1379.32 6/1/2012 1309.87 1363.46 1266.74 1362.16 4.10E+009 1362.16 5/1/2012 1397.86 1415.32 1291.98 1310.33 4.16E+009 1310.33 4/2/2012 1408.47 1422.38 1357.38 1397.91 3.92E+009 1397.91 3/1/2012 1365.9 1419.15 1340.03 1408.47 3.98E+009 1408.47 2/1/2012 1312.45 1378.04 1312.45 1365.6801 4.14E+009 1365.6801 1/3/2012 1258.86 1333.47 1258.86 1312.41 4.19E+009 1312.41 12/1/2011 1246.91 1269.37 1202.37 1257.6 3.67E+009 1257.6 11/1/2011 1251 1277.5501 1158.66 1246.96 4.29E+009 1246.96 10/3/2011 1131.21 1292.66 1074.77 1253.3001 4.87E+009 1253.3001 9/1/2011 1219.12 1229.29 1114.22 1131.42 5.10E+009 1131.42 8/1/2011 1292.59 1307.38 1101.54 1218.89 4.94E+009 1218.89 7/1/2011 1320.64 1356.48 1282.86 1292.28 4.31E+009 1292.28 6/1/2011 1345.2 1345.2 1258.07 1320.64 4.11E+009 1320.64 5/2/2011 1365.21 1370.58 1311.8001 1345.2 4.11E+009 1345.2 4/1/2011 1329.48 1364.5601 1294.7 1363.61 4.04E+009 1363.61 3/1/2011 1328.64 1332.28 1249.0501 1325.83 4.05E+009 1325.83 2/1/2011 1289.14 1344.07 1289.14 1327.22 3.18E+009 1327.22 1/3/2011 1257.62 1302.67 1257.62 1286.12 4.82E+009 1286.12 12/1/2010 1186.6 1262.6 1186.6 1257.64 3.76E+009 1257.64 11/1/2010 1185.71 1227.08 1173 1180.5501 4.35E+009 1180.5501 10/1/2010 1143.49 1196.14 1131.87 1183.26 4.43E+009 1183.26 9/1/2010 1049.72 1157.16 1049.72 1141.2 3.99E+009 1141.2 8/2/2010 1107.53 1129.24 1039.7 1049.33 4.08E+009 1049.33 7/1/2010 1031.1 1120.95 1010.91 1101.6 4.70E+009 1101.6 6/1/2010 1087.3001 1131.23 1028.33 1030.71 5.24E+009 1030.71 5/3/2010 1188.58 1205.13 1040.78 1089.41 6.63E+009 1089.41 Date Open High Low Close Volume Adj Close 5/1/2015 126.1 132.97 123.36 130.28 49919200 130.28 4/1/2015 124.82 134.53999 123.1 125.15 51396700 124.62942 3/2/2015 129.25 130.28 121.63 124.43 53669600 123.91241 2/2/2015 118.05 133.60001 116.08 128.46001 63063900 127.92565 1/2/2015 111.39 120 104.63 117.16 69450400 116.214 12/1/2014 118.81 119.25 106.26 110.38 50669200 109.48875 11/3/2014 108.22 119.75 107.72 118.93 44485400 117.96971 10/1/2014 100.59 108.04 95.18 108 61078100 106.66544 9/2/2014 103.06 103.74 96.14 100.75 75388300 99.50504 8/1/2014 94.9 102.9 93.28 102.5 46746200 101.23341 7/1/2014 93.52 99.44 92.57 95.6 49637900 93.95135 6/2/2014 633.95996 651.26001 89.65 92.93 59839500 91.32739 5/1/2014 592 644.17004 580.33002 633 74996300 88.86911 4/1/2014 537.76001 599.42999 511.32999 590.08997 82044000 82.38467 3/3/2014 523.42004 549 522.81 536.73999 61552000 74.93627 2/3/2014 502.61002 551.19 499.29999 526.23999 82267500 73.47033 1/2/2014 555.67999 560.20001 493.54999 500.60004 109889900 69.47478 12/2/2013 558 575.14001 538.79999 561.02002 86672400 77.86005 11/1/2013 524.02002 558.33002 512.38 556.06995 69291000 77.17307 10/1/2013 478.44998 539.25 478.27994 522.70001 88189400 72.12082 9/3/2013 493.10004 507.91998 447.21997 476.74997 111138700 65.78075 8/1/2013 455.74997 513.74005 453.25998 487.22003 94666300 67.22538 7/1/2013 402.69 457.34003 401.21997 452.52997 77966700 62.02938 6/3/2013 450.72998 454.42999 388.87003 396.52997 94963100 54.35332 5/1/2013 444.46002 465.75 418.90002 449.72998 111725300 61.64557 4/1/2013 441.89999 445.24997 385.10004 442.78003 132443500 60.29384 3/1/2013 438 469.94998 418.99997 442.65997 120246400 60.27749 2/1/2013 459.10999 484.93997 437.66003 441.39999 127191400 60.10592 1/2/2013 553.82001 555 435.00003 455.48999 160032800 61.66519 12/3/2012 593.64996 594.59003 501.23001 532.17004 159903000 72.0463 11/1/2012 598.21997 603 505.75 585.27997 158585300 79.23643 10/1/2012 671.15997 676.75 587.69995 595.31995 150628900 80.22923 9/4/2012 665.75995 705.07001 656 667.10004 128080400 89.90278 8/1/2012 615.90997 680.87 600.25 665.23999 93636100 89.65211 7/2/2012 584.73004 619.87 570 610.76001 111571100 81.95814 6/1/2012 569.15997 590 548.5 584 98539600 78.3672 5/1/2012 584.89996 596.76001 522.17993 577.73004 131795600 77.52583 4/2/2012 601.82996 644 555 583.97998 201314100 78.36452 3/1/2012 548.16998 621.45001 516.21997 599.54999 185402000 80.45387 2/1/2012 458.40997 547.61005 453.98001 542.44 154007000 72.79025 1/3/2012 409.39996 458.24002 409 456.48004 90644200 61.25525 12/1/2011 382.54001 409.09 377.68002 404.99997 77216600 54.34711 11/1/2011 397.41003 407.99997 363.32001 382.19998 111543300 51.28757 10/3/2011 380.37 426.69998 354.23996 404.77997 161034800 54.31759 9/1/2011 385.82001 422.85999 366.48004 381.32001 149108200 51.16949 8/1/2011 397.77997 399.5 353.02002 384.82999 181143300 51.6405 7/1/2011 335.95001 404.49997 334.20001 390.48001 141081600 52.39867 6/1/2011 348.87 352.13004 310.49997 335.67004 108940900 45.0437 5/2/2011 349.74002 351.83002 329.42001 347.82999 87259700 46.67545 4/1/2011 351.10999 355.13004 320.15997 350.13 128252100 46.98409 3/1/2011 355.47 361.66998 326.25998 348.50998 125874700 46.7667 2/1/2011 341.29999 364.89999 337.71997 353.20999 127618700 47.39739 1/3/2011 325.64001 348.60001 324.83997 339.32001 140234700 45.53349 12/1/2010 315.26999 326.65997 314.89005 322.56 81440200 43.28446 11/1/2010 302.22003 321.30002 297.75998 311.14999 119166000 41.75335 10/1/2010 286.14999 319 277.77002 300.97998 150774800 40.38863 9/1/2010 247.47002 294.73004 246.28003 283.74997 149087000 38.07653 8/2/2010 260.43997 264.28003 235.56 243.09998 113748900 32.62169 7/1/2010 254.3 265.98999 239.59998 257.25 191879900 34.52048 6/1/2010 259.69 279.00998 242.19998 251.53003 197621600 33.75292 5/3/2010 263.84 267.88 199.25003 256.87997 236228300 34.47083 1. Q1: Index Models: Download 61 months (May 2010 to May 2015) of monthly data for the S&P 500 index (symbol = ^GSPC). Download 61 months (May 2010 to May 2015) of Apple Inc. data and 61 months (May 2010 to May 2015) of Exxon Mobil Corporation data. Download 60 months (June 2010 to May 2015) of the 13 week Tbill rate (symbol = ^IRX). Be sure to use endofmonth data! Construct the following on a spreadsheet: 1. Calculate 60 months of returns for the S&P 500 index, Apple and Exxon. (Please compute simple monthly returns not continuously compounded returns.) Use June 2010 to May 2015. Note this means you need price data for May 2010. On the answer sheet report the average monthly returns for the S&P 500 index, Apple and Exxon, as well as the average monthly riskfree rate. 2. Calculate excess returns for the S&P 500 index, Apple and Exxon. Note you must divide the annualized risk free rate (^IRX) by 1200 to approximate the monthly rate in in decimal form. On the answer sheet report the average monthly excess returns for the S&P 500 index, Apple and Exxon. 3. Regress excess Apple returns on the excess S&P 500 index returns and report, on the answer sheet, , , the r square and whether and are different from zero at the 10% level of significance. Briefly explain your inference. 4. Use equation 8.10 to decompose total risk for Apple into systematic risk and firmspecific risk. That is, calculate total risk, systematic risk and firmspecific risk for Apple. 5. Regress excess Exxon returns on the excess S&P 500 index returns and report, on the answer sheet, , , the r square and whether , are different from zero at the 10% level of significance. Briefly explain your inference. 6. Use equation 8.10 to decompose total risk for Exxon into systematic risk and firmspecific risk. That is, calculate total risk, systematic risk and firmspecific risk for Exxon. 7. Use equation 8.10 to estimate the covariance and correlation of Apple and Exxon excess returns. 50 points QUESTION 2 1. Q2: CAPM and APT: 1. The expected rate of return on the market portfolio is 8.50% and the risk-free rate of return is 1.25%. The standard deviation of the market portfolio is 17.50%. What is the representative investor's average degree of risk aversion? 2. Stock A has a beta of 1.95 and a standard deviation of return of 44%. Stock B has a beta of 3.75 and a standard deviation of return of 65%. Assume that you form a portfolio that is 65% invested in Stock A and 35% invested in Stock B. Using the information in question 1, according to CAPM, what is the expected rate of return on your portfolio? 3. Using the information in questions 1 and 2, what is your best estimate of the correlation between stocks A and B? 4. Your forecasting model projects an expected return of 18.50% for Stock A and an expected return of 34.00% for Stock B. Using the information in questions 1 and 2 and your forecasted expected returns, what is your best estimate of the alpha of your portfolio when using CAPM to determine a fair level of expected return? 5. A different analyst uses a two-factor APT model to evaluate expected returns and risk. The risk premiums on the factor 1 and factor 2 portfolios are 4.75% and 2.15%, respectively, while the risk-free rate of return remains at 1.25%. According to this APT analyst, your portfolio formed in question 2 has a beta on factor 1 of 3.85 and a beta on factor 2 of 3.25. According to APT, what is the expected return on your portfolio if no arbitrage opportunities exist? 6. Now assume that your forecasting model of question 4 accurately projects the expected return of Stocks A and B and therefore your portfolio, and that the APT model of question 5 describes the fair rate of return for your portfolio. Do any arbitrage opportunities exist? If yes, would you invest long or short in your portfolio constructed in question 2