Q1: Compute monthly holding period return using Adj. close prices for each stock and S&P 500 index....

Question

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%

book2-sokdtts3.xlsx

Answered Same DayNov 12, 2021

Answer To: Q1: Compute monthly holding period return using Adj. close prices for each stock and S&P 500 index....

Rochak answered on Nov 13 2021

125 Votes

Solution
        Adjusted Price                Return                            Excess Return
    Date    DX    IP    S&P 500        DX    IP    S&P 500    Portfolio 1    Portfolio 2    Rf        DX    IP    Portfolio 1    Portfolio 2    S&P 500        DX    S&P 500        IP    S&P 500        Portfolio 1    S&P 500        Portfolio 2    S&P 500
    11/1/16    12.031538    37.446629    2,198.81                            0.450%                                                                                Your answers
    12/1/16    11.87483    41.200253    2,238.83        -1.30%    10.02%    1.82%    1.33%    4.28%    0.510%        -1.81%    9.51%    0.82%    3.77%    1.31%        -0.0181247687    1.31%        0.0951393032    1.31%        0.82%    1.31%        3.77%    1.31%        Beta of Stock DX    0.3349
    1/1/17    11.989212    43.949005    2,278.87        0.96%    6.67%    1.79%    1.53%    3.25%    0.510%        0.45%    6.16%    1.02%    2.74%    1.28%        0.0045323063    1.28%        0.0616168719    1.28%        1.02%    1.28%        2.74%    1.28%        Beta of Stock IP    0.4237
    2/1/17    12.312273    40.920731    2,363.64        2.69%    -6.89%    3.72%    3.25%    0.54%    0.520%        2.17%    -7.41%    2.73%    0.02%    3.20%        0.0217459744    3.20%        -0.0741042676    3.20%        2.73%    3.20%        0.02%    3.20%        Beta of Portfolio 1    1.0438
    3/1/17    12.725072    39.778091    2,362.72        3.35%    -2.79%    -0.04%    0.30%    -0.86%    0.740%        2.61%    -3.53%    -0.44%    -1.60%    -0.78%        0.0261274405    -0.78%        -0.0353232548    -0.78%        -0.44%    -0.78%        -1.60%    -0.78%        Beta of Portfolio...

SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Rochak · Accepted Answer

Solution
		Adjusted Price				Return							Excess Return
	Date	DX	IP	S&P 500		DX	IP	S&P 500	Portfolio 1	Portfolio 2	Rf		DX	IP	Portfolio 1	Portfolio 2	S&P 500		DX	S&P 500		IP	S&P 500		Portfolio 1	S&P 500		Portfolio 2	S&P 500
	11/1/16	12.031538	37.446629	2,198.81							0.450%																				Your answers 
	12/1/16	11.87483	41.200253	2,238.83		-1.30%	10.02%	1.82%	1.33%	4.28%	0.510%		-1.81%	9.51%	0.82%	3.77%	1.31%		-0.0181247687	1.31%		0.0951393032	1.31%		0.82%	1.31%		3.77%	1.31%		Beta of Stock DX	0.3349
	1/1/17	11.989212	43.949005	2,278.87		0.96%	6.67%	1.79%	1.53%	3.25%	0.510%		0.45%	6.16%	1.02%	2.74%	1.28%		0.0045323063	1.28%		0.0616168719	1.28%		1.02%	1.28%		2.74%	1.28%		Beta of Stock IP	0.4237
	2/1/17	12.312273	40.920731	2,363.64		2.69%	-6.89%	3.72%	3.25%	0.54%	0.520%		2.17%	-7.41%	2.73%	0.02%	3.20%		0.0217459744	3.20%		-0.0741042676	3.20%		2.73%	3.20%		0.02%	3.20%		Beta of Portfolio 1	1.0438
	3/1/17	12.725072	39.778091	2,362.72		3.35%	-2.79%	-0.04%	0.30%	-0.86%	0.740%		2.61%	-3.53%	-0.44%	-1.60%	-0.78%		0.0261274405	-0.78%		-0.0353232548	-0.78%		-0.44%	-0.78%		-1.60%	-0.78%		Beta of Portfolio 2	0.8790
	4/1/17	12.419955	42.276951	2,384.20		-2.40%	6.28%	0.91%	0.49%	2.52%	0.800%		-3.20%	5.48%	-0.31%	1.72%	0.11%		-0.0319776246	0.11%		0.0548200081	0.11%		-0.31%	0.11%		1.72%	0.11%
	5/1/17	12.632998	41.423103	2,411.80		1.72%	-2.02%	1.16%	1.10%	0.20%	0.890%		0.83%	-2.91%	0.21%	-0.69%	0.27%		0.0082532828	0.27%		-0.0290965369	0.27%		0.21%	0.27%		-0.69%	0.27%
	6/1/17	13.074969	44.743591	2,423.41		3.50%	8.02%	0.48%	0.73%	2.74%	0.980%		2.52%	7.04%	-0.25%	1.76%	-0.50%		0.0251854405	-0.50%		0.0703602912	-0.50%		-0.25%	-0.50%		1.76%	-0.50%
	7/1/17	12.725074	43.455269	2,470.30		-2.68%	-2.88%	1.93%	1.28%	0.49%	1.070%		-3.75%	-3.95%	0.21%	-0.58%	0.86%		-0.0374606753	0.86%		-0.0394934422	0.86%		0.21%	0.86%		-0.58%	0.86%
	8/1/17	13.509543	42.577938	2,471.65		6.16%	-2.02%	0.05%	0.66%	-0.57%	1.010%		5.15%	-3.03%	-0.35%	-1.58%	-0.96%		0.0515475	-0.96%		-0.0302892894	-0.96%		-0.35%	-0.96%		-1.58%	-0.96%
	9/1/17	13.736273	45.31139	2,519.36		1.68%	6.42%	1.93%	1.71%	3.28%	1.030%		0.65%	5.39%	0.68%	2.25%	0.90%		0.0064829511	0.90%		0.0538987876	0.90%		0.68%	0.90%		2.25%	0.90%
	10/1/17	13.226127	45.67025	2,575.26		-3.71%	0.79%	2.22%	1.40%	1.79%	1.070%		-4.78%	-0.28%	0.33%	0.72%	1.15%		-0.0478386038	1.15%		-0.002780137	1.15%		0.33%	1.15%		0.72%	1.15%
	11/1/17	13.871895	45.143932	2,647.58		4.88%	-1.15%	2.81%	2.73%	1.62%	1.230%		3.65%	-2.38%	1.50%	0.39%	1.58%		0.0365251776	1.58%		-0.0238243074	1.58%		1.50%	1.58%		0.39%	1.58%
	12/1/17	13.581283	46.611607	2,673.61		-2.09%	3.25%	0.98%	0.58%	1.66%	1.320%		-3.41%	1.93%	-0.74%	0.34%	-0.34%		-0.0341496972	-0.34%		0.0193110139	-0.34%		-0.74%	-0.34%		0.34%	-0.34%
	1/1/18	12.916566	50.56966	2,823.81		-4.89%	8.49%	5.62%	4.00%	6.48%	1.410%		-6.30%	7.08%	2.59%	5.07%	4.21%		-0.0630436086	4.21%		0.0708156091	4.21%		2.59%	4.21%		5.07%	4.21%
	2/1/18	11.962728	47.939011	2,713.83		-7.38%	-5.20%	-3.89%	-3.85%	-4.29%	1.570%		-8.95%	-6.77%	-5.42%	-5.86%	-5.46%		-0.0895460981	-5.46%		-0.0677203023	-5.46%		-5.42%	-5.46%		-5.86%	-5.46%
	3/1/18	13.174893	43.332626	2,640.87		10.13%	-9.61%	-2.69%	-1.14%	-4.76%	1.700%		8.43%	-11.31%	-2.84%	-6.46%	-4.39%		0.0843284762	-4.39%		-0.1130884445	-4.39%		-2.84%	-4.39%		-6.46%	-4.39%
	4/1/18	13.075539	41.816025	2,648.05		-0.75%	-3.50%	0.27%	0.14%	-0.86%	1.760%		-2.51%	-5.26%	-1.62%	-2.62%	-1.49%		-0.0251411618	-1.49%		-0.0525990559	-1.49%		-1.62%	-1.49%		-2.62%	-1.49%
	5/1/18	13.236174	43.389397	2,705.27		1.23%	3.76%	2.16%	1.85%	2.64%	1.860%		-0.63%	1.90%	-0.01%	0.78%	0.30%		-0.0063148468	0.30%		0.0190260536	0.30%		-0.01%	0.30%		0.78%	0.30%
	6/1/18	13.3383	42.604038	2,718.37		0.77%	-1.81%	0.48%	0.46%	-0.20%	1.900%		-1.13%	-3.71%	-1.44%	-2.10%	-1.42%		-0.0112843263	-1.42%		-0.0371002515	-1.42%		-1.44%	-1.42%		-2.10%	-1.42%
	7/1/18	13.583421	43.953815	2,816.29		1.84%	3.17%	3.60%	3.07%	3.47%	1.960%		-0.12%	1.21%	1.11%	1.51%	1.64%		-0.0012227705	1.64%		0.012081903	1.64%		1.11%	1.64%		1.51%	1.64%
	8/1/18	13.462012	41.835068	2,901.52		-0.89%	-4.82%	3.03%	2.33%	0.67%	2.030%		-2.92%	-6.85%	0.30%	-1.36%	1.00%		-0.0292380282	1.00%		-0.0685039386	1.00%		0.30%	1.00%		-1.36%	1.00%
	9/1/18	13.39901	40.580708	2,913.98		-0.47%	-3.00%	0.43%	0.30%	-0.60%	2.130%		-2.60%	-5.13%	-1.83%	-2.73%	-1.70%		-0.0259799839	-1.70%		-0.0512834579	-1.70%		-1.83%	-1.70%		-2.73%	-1.70%
	10/1/18	12.159916	37.451496	2,711.74		-9.25%	-7.71%	-6.94%	-6.48%	-7.17%	2.250%		-11.50%	-9.96%	-8.73%	-9.42%	-9.19%		-0.1149765337	-9.19%		-0.0996108281	-9.19%		-8.73%	-9.19%		-9.42%	-9.19%
	11/1/18	13.03102	38.13678	2,760.17		7.16%	1.83%	1.79%	2.15%	1.80%	2.330%		4.83%	-0.50%	-0.18%	-0.53%	-0.54%		0.048337337	-0.54%		-0.0050020928	-0.54%		-0.18%	-0.54%		-0.53%	-0.54%
	12/1/18	12.361104	33.690239	2,506.85		-5.14%	-11.66%	-9.18%	-7.86%	-9.92%	2.370%		-7.51%	-14.03%	-10.23%	-12.29%	-11.55%		-0.0751093294	-11.55%		-0.1402945578	-11.55%		-10.23%	-11.55%		-12.29%	-11.55%
	1/1/19	13.420959	39.591869	2,704.10		8.57%	17.52%	7.87%	7.15%	10.76%	2.370%		6.20%	15.15%	4.78%	8.39%	5.50%		0.062041128	5.50%		0.1514732898	5.50%		4.78%	5.50%		8.39%	5.50%
	2/1/19	13.730914	38.24794	2,784.49		2.31%	-3.39%	2.97%	2.61%	1.06%	2.390%		-0.08%	-5.78%	0.22%	-1.33%	0.58%		-0.0008051526	0.58%		-0.0578445708	0.58%		0.22%	0.58%		-1.33%	0.58%
	3/1/19	13.843465	39.035076	2,834.40		0.82%	2.06%	1.79%	1.52%	1.87%	2.400%		-1.58%	-0.34%	-0.88%	-0.53%	-0.61%		-0.0158030948	-0.61%		-0.0034201727	-0.61%		-0.88%	-0.61%		-0.53%	-0.61%
	4/1/19	14.006495	39.490635	2,945.83		1.18%	1.17%	3.93%	3.26%	3.10%	2.380%		-1.20%	-1.21%	0.88%	0.72%	1.55%		-0.0120233241	1.55%		-0.0121294963	1.55%		0.88%	1.55%		0.72%	1.55%
	5/1/19	12.75364	34.985619	2,752.06		-8.94%	-11.41%	-6.58%	-6.16%	-8.03%	2.350%		-11.29%	-13.76%	-8.51%	-10.38%	-8.93%		-0.1129481453	-8.93%		-0.1375780846	-8.93%		-8.51%	-8.93%		-10.38%	-8.93%
	6/1/19	13.082683	36.972595	2,941.76		2.58%	5.68%	6.89%	5.77%	6.53%	2.170%		0.41%	3.51%	3.60%	4.36%	4.72%		0.0040999285	4.72%		0.0350940787	4.72%		3.60%	4.72%		4.36%	4.72%
	7/1/19	12.867773	37.476147	2,980.38		-1.64%	1.36%	1.31%	0.89%	1.33%	2.100%		-3.74%	-0.74%	-1.21%	-0.77%	-0.79%		-0.0374270586	-0.79%		-0.0073803988	-0.79%		-1.21%	-0.79%		-0.77%	-0.79%
	8/1/19	11.263258	33.37093	2,926.46		-12.47%	-10.95%	-1.81%	-2.69%	-4.55%	1.950%		-14.42%	-12.90%	-4.64%	-6.50%	-3.76%		-0.1441925167	-3.76%		-0.

Sun	Mon	Tue	Wed	Thu	Fri	Sat
30	31	1	2	3	4	5
6	7	8	9	10	11	12
13	14	15	16	17	18	19
20	21	22	23	24	25	26
27	28	29	30	1	2	3

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...

Answer To: Q1: Compute monthly holding period return using Adj. close prices for each stock and S&P 500 index....

Answer To This Question Is Available To Download

Related Questions & Answers

Submit New Assignment