Q1: Compute monthly holding period return using Adj. close prices for each stock and S&P 500 index. Q2: Estimate beta of DX based on the index model regressions using scatter plot. Q3: Estimate beta...

Q1: Compute monthly holding period return using Adj. close prices for each stock and S&P 500 index. Q2: Estimate beta of DX based on the index model regressions using scatter plot. Q3: Estimate beta of IP based on the index model regressions using scatter plot. Q4: Estimate beta of portfolio 1: 90% in ^GSPC and 10% in DX. Q5: Estimate beta of portfolio 2: 70% in ^GSPC and 30% in IP. Note: Fill the grey cells for beta values, show calculation procedure and the scatter plots. You can insert additional cells if needed..


Sheet1 Adjusted PriceReturnExcess Return DateDXIPS&P 500DXIPS&P 500Portfolio 1Portfolio 2RfDXIPPortfolio 1Portfolio 2S&P 500 11/1/1612.03153837.4466292,198.810.450%Your answers 12/1/1611.8748341.2002532,238.83-1.30%10.02%1.82%0.510%-1.81%9.51%1.31%Beta of Stock DX 1/1/1711.98921243.9490052,278.870.96%6.67%1.79%0.510%0.45%6.16%1.28%Beta of Stock IP 2/1/1712.31227340.9207312,363.642.69%-6.89%3.72%0.520%2.17%-7.41%3.20%Beta of Portfolio 1 3/1/1712.72507239.7780912,362.723.35%-2.79%-0.04%0.740%2.61%-3.53%-0.78%Beta of Portfolio 2 4/1/1712.41995542.2769512,384.20-2.40%6.28%0.91%0.800%-3.20%5.48%0.11% 5/1/1712.63299841.4231032,411.801.72%-2.02%1.16%0.890%0.83%-2.91%0.27% 6/1/1713.07496944.7435912,423.413.50%8.02%0.48%0.980%2.52%7.04%-0.50% 7/1/1712.72507443.4552692,470.30-2.68%-2.88%1.93%1.070%-3.75%-3.95%0.86% 8/1/1713.50954342.5779382,471.656.16%-2.02%0.05%1.010%5.15%-3.03%-0.96% 9/1/1713.73627345.311392,519.361.68%6.42%1.93%1.030%0.65%5.39%0.90% 10/1/1713.22612745.670252,575.26-3.71%0.79%2.22%1.070%-4.78%-0.28%1.15% 11/1/1713.87189545.1439322,647.584.88%-1.15%2.81%1.230%3.65%-2.38%1.58% 12/1/1713.58128346.6116072,673.61-2.09%3.25%0.98%1.320%-3.41%1.93%-0.34% 1/1/1812.91656650.569662,823.81-4.89%8.49%5.62%1.410%-6.30%7.08%4.21% 2/1/1811.96272847.9390112,713.83-7.38%-5.20%-3.89%1.570%-8.95%-6.77%-5.46% 3/1/1813.17489343.3326262,640.8710.13%-9.61%-2.69%1.700%8.43%-11.31%-4.39% 4/1/1813.07553941.8160252,648.05-0.75%-3.50%0.27%1.760%-2.51%-5.26%-1.49% 5/1/1813.23617443.3893972,705.271.23%3.76%2.16%1.860%-0.63%1.90%0.30% 6/1/1813.338342.6040382,718.370.77%-1.81%0.48%1.900%-1.13%-3.71%-1.42% 7/1/1813.58342143.9538152,816.291.84%3.17%3.60%1.960%-0.12%1.21%1.64% 8/1/1813.46201241.8350682,901.52-0.89%-4.82%3.03%2.030%-2.92%-6.85%1.00% 9/1/1813.3990140.5807082,913.98-0.47%-3.00%0.43%2.130%-2.60%-5.13%-1.70% 10/1/1812.15991637.4514962,711.74-9.25%-7.71%-6.94%2.250%-11.50%-9.96%-9.19% 11/1/1813.0310238.136782,760.177.16%1.83%1.79%2.330%4.83%-0.50%-0.54% 12/1/1812.36110433.6902392,506.85-5.14%-11.66%-9.18%2.370%-7.51%-14.03%-11.55% 1/1/1913.42095939.5918692,704.108.57%17.52%7.87%2.370%6.20%15.15%5.50% 2/1/1913.73091438.247942,784.492.31%-3.39%2.97%2.390%-0.08%-5.78%0.58% 3/1/1913.84346539.0350762,834.400.82%2.06%1.79%2.400%-1.58%-0.34%-0.61% 4/1/1914.00649539.4906352,945.831.18%1.17%3.93%2.380%-1.20%-1.21%1.55% 5/1/1912.7536434.9856192,752.06-8.94%-11.41%-6.58%2.350%-11.29%-13.76%-8.93% 6/1/1913.08268336.9725952,941.762.58%5.68%6.89%2.170%0.41%3.51%4.72% 7/1/1912.86777337.4761472,980.38-1.64%1.36%1.31%2.100%-3.74%-0.74%-0.79% 8/1/1911.26325833.370932,926.46-12.47%-10.95%-1.81%1.950%-14.42%-12.90%-3.76% 9/1/1911.90645936.1436082,976.745.71%8.31%1.72%1.890%3.82%6.42%-0.17% 10/1/1913.14947837.7511413,037.5610.44%4.45%2.04%1.650%8.79%2.80%0.39% 11/1/1913.87813440.0500913,140.985.54%6.09%3.40%1.540%4.00%4.55%1.86% 12/1/1914.04668140.2497983,230.781.21%0.50%2.86%1.540%-0.33%-1.04%1.32% 1/1/2014.90676235.5911333,225.526.12%-11.57%-0.16%1.520%4.60%-13.09%-1.68% 2/1/2014.42373932.3047182,954.22-3.24%-9.23%-8.41%1.520%-4.76%-10.75%-9.93% 3/1/208.87204927.5399042,584.59-38.49%-14.75%-12.51%0.290%-38.78%-15.04%-12.80% 4/1/2012.32772330.3000852,912.4338.95%10.02%12.68%0.140%38.81%9.88%12.54% 5/1/2011.20086130.1231463,044.31-9.14%-0.58%4.53%0.130%-9.27%-0.71%4.40% 6/1/2012.59618431.6467633,100.2912.46%5.06%1.84%0.160%12.30%4.90%1.68% 7/1/2013.73151831.2692663,271.129.01%-1.19%5.51%0.130%8.88%-1.32%5.38% 8/1/2014.19854332.5994873,500.313.40%4.25%7.01%0.100%3.30%4.15%6.91% 9/1/2013.7454436.9536323,363.00-3.19%13.36%-3.92%0.110%-3.30%13.25%-4.03% 10/1/2014.92566639.8796543,269.968.59%7.92%-2.77%0.100%8.49%7.82%-2.87% 11/1/2016.17848645.1027573,621.638.39%13.10%10.75%0.090%8.30%13.01%10.66% 12/1/2016.47355745.8206863,756.071.82%1.59%3.71%0.090%1.73%1.50%3.62% 1/1/2116.82543846.3644183,714.242.14%1.19%-1.11%0.080%2.06%1.11%-1.19% 2/1/2117.66667645.756183,811.155.00%-1.31%2.61%0.040%4.96%-1.35%2.57% 3/1/2117.89386650.3546913,972.891.29%10.05%4.24%0.030%1.26%10.02%4.21% 4/1/2119.22723854.0146454,181.177.45%7.27%5.24%0.020%7.43%7.25%5.22% 5/1/2118.73202558.764214,204.11-2.58%8.79%0.55%0.020%-2.60%8.77%0.53% 6/1/2117.99815457.5616464,297.50-3.92%-2.05%2.22%0.040%-3.96%-2.09%2.18% 7/1/2116.94270554.2286874,395.26-5.86%-5.79%2.27%0.050%-5.91%-5.84%2.22% 8/1/2117.37041356.4162334,522.682.52%4.03%2.90%0.050%2.47%3.98%2.85% 9/1/2117.02801752.9545444,307.54-1.97%-6.14%-4.76%0.040%-2.01%-6.18%-4.80%