Question 4 (EXCEL): Estimation of Expected Return and Variance/Covariance Matrix ONLY ANSWER PART...

Question

Question 4 (EXCEL): Estimation of Expected Return and Variance/Covariance Matrix ONLY ANSWER PART 3 1) Go to Yahoo Finance on the Internet (http://finance.yahoo.com/O. Retrieve 37 months ofmonthly...

Question 4 (EXCEL): Estimation of Expected Return and Variance/Covariance Matrix

ONLY ANSWER PART 3

1) Go to Yahoo Finance on the Internet (http://finance.yahoo.com/O. Retrieve 37 months ofmonthly price data (from December 2011 to December 2014) for the following stocks: BSX, BXP, SAM and SPY. This can be located by clicking "historical prices which will be located on left of the page under quotes. You can click on "download to spreadsheet," and this will save the data as an Excel spreadsheet. Calculate 36 monthly returns for each stock from this data. The adjusted close reflects dividends and stock splits, so you can assume that there are no dividends and use it to calculate returns. You should use Excel. Consolidate all returns in the same spreadsheet. This can be accomplished by copying and pasting across spreadsheets. 2) What is the arithmetic average return for each stock? What is the geometric average return for each stock? What is each stock's return standard deviation? Construct a return variance-covariance matrix for the four securities. Use Excel's "Data Analysis" function (located in the "Tools" menu) If you do not see "Data Analysis click on "Add Ins" and install the "Analysis Toolpak." 3) What is the return standard deviation of a portfolio that only holds BSX? Construct a second portfolio of that has equal weights in BSX and BXP. Third, construct a portfolio that has equal weights of BSX, BXP, and SAM. Fourth, construct a portfolio with equal weights in BSX, BXP SAM, and SPY. Report the standard deviation of each portfolio

Answered 129 days AfterMay 14, 2022

Prince · Accepted Answer

Summary
	Month	SPY	SAM	BSX	BXP
	1	5.30%	-7.84%	11.61%	5.04%
	2	4.34%	-5.53%	4.36%	-2.40%			SPY	SAM	BSX	BXP
	3	2.77%	12.98%	-3.86%	3.39%		Arithmetic average return	1.59%	3.13%	2.90%	1.05%
	4	-0.23%	-3.25%	4.68%	3.65%		Geometric average return	Not Possible as Return are in -ve	Not Possible as Return are in -ve	Not Possible as Return are in -ve	Not Possible as Return are in -ve
	5	-6.01%	1.48%	-8.31%	-4.91%		Standard Deviation	2.65%	8.69%	8.67%	3.97%
	6	3.52%	15.40%	-1.22%	5.29%
	7	1.71%	-10.98%	-8.82%	2.88%		Matrix
	8	2.51%	-4.35%	4.45%	1.11%			SPY	SAM	BSX	BXP
	9	1.99%	8.68%	6.30%	-1.36%		SPY	0.0007009179
	10	-1.30%	-3.92%	-10.45%	-3.42%		SAM	-0.0000537244	0.0075515972
	11	0.57%	5.01%	7.78%	-3.45%		BSX	0.0009380962	-0.0007113021	0.0075134571
	12	0.18%	19.01%	3.43%	3.10%		BXP	0.0002321892	-0.0000132536	0.0006298686	0.0015725427
	13	5.86%	4.38%	30.37%	0.12%
	14	1.28%	10.75%	-1.07%	-1.33%		Q3
	15	3.34%	2.71%	5.68%	-2.71%	1.00	Standard deviation of a portfolio that only holds BSX	2.90%
	16	2.37%	6.06%	-4.10%	8.98%	2.00	Portfolio of that has equal weights in BSX and BXP.
	17	2.36%	-9.73%	23.36%	-2.60%		Correlation of BSX and BXP	0.1832436143
	18	-1.85%	11.65%	0.32%	-1.04%		Weight of BSX	50.00%
	19	5.72%	4.89%	17.80%	2.05%		Weight of BXP	50.00%
	20	-3.00%	18.36%	-3.11%	-4.16%
	21	2.66%	15.28%	10.96%	4.29%		Standard Dev	5.09%
	22	5.14%	-5.99%	-0.43%	-2.58%
	23	2.96%	6.62%	-0.94%	-3.87%	3.00	Portfolio that has equal weights of BSX, BXP, and SAM
	24	2.04%	-1.23%	3.80%	0.88%		Weight of BSX	33.33%
	25	-3.00%	-13.85%	12.56%	10.78%		Weight of BXP	33.33%
	26	4.55%	13.65%	-3.18%	4.02%		Weight of SAM	33.33%
	27	0.39%	3.37%	3.21%	1.87%		Correlation of BSX and BXP	0.1832436143
	28	1.14%	0.54%	-6.73%	2.87%		Correlation of BSX and SAM	-0.0944310357
	29	2.32%	-12.86%	1.74%	3.02%		Correlation of BXP and SAM	-0.0038460311
	30	1.58%	4.25%	-0.47%	-2.07%
	31	-0.87%	-1.40%	0.08%	1.63%		Standard Dev	4.28%
	32	3.95%	0.26%	-0.78%	1.65%
	33	-1.84%	0.35%	-6.86%	-4.66%	4.00	Portfolio with equal weights in BSX, BXP SAM, and SPY
	34	2.83%	12.28%	12.45%	10.12%		Weight of BSX	25.00%
	35	2.75%	5.61%	-3.09%	2.28%		Weight of BXP	25.00%
	36	-0.80%	10.10%	2.95%	-0.73%		Weight of SAM	25.00%
							Weight of SPY	25.00%
							Correlation of BSX and BXP	0.1832436143
							Correlation of BSX and SAM	-0.0944310357
							Correlation of BSX and SPY	0.4087839943
							Correlation of BXP and SAM	-0.0038460311
							Correlation of BXP and SPY	0.2211602897
							Correlation of SAM and SPY	-0.0233517164
							Standard Dev	10.14%
SPY
	Date	Open	High	Low	Close	Adj Close	Volume	Return
	12/1/11	$124.85	$127.26	$120.03	$125.50	$101.79	3,848,636,100.00
	1/1/12	$127.76	$133.40	$126.43	$131.32	$107.19	2,883,821,400.00	5.30%		Particular	Aomunt
	2/1/12	$132.29	$138.19	$132.13	$137.02	$111.84	2,894,582,000.00	4.34%		Arithmetic average return	1.59%
	3/1/12	$137.31	$141.83	$134.36	$140.81	$114.94	3,057,516,200.00	2.77%		Geometric average return	It is not possible has some of the returns are negative
	4/1/12	$140.64	$142.21	$135.76	$139.87	$114.67	3,035,644,100.00	-0.23%		Standard Deviation	0.026474854
	5/1/12	$139.79	$141.66	$129.55	$131.47	$107.78	4,004,156,100.00	-6.01%
	6/1/12	$129.41	$136.27	$127.14	$136.10	$111.58	3,644,886,200.00	3.52%
	7/1/12	$136.48	$139.34	$132.60	$137.71	$113.48	2,864,743,300.00	1.71%			Return
	8/1/12	$138.70	$143.09	$135.58	$141.16	$116.33	2,413,590,900.00	2.51%		Return	0.0007009179
	9/1/12	$141.04	$148.11	$140.13	$143.97	$118.64	2,391,233,500.00	1.99%
	10/1/12	$144.52	$147.16	$140.39	$141.35	$117.10	2,719,915,500.00	-1.30%
	11/1/12	$141.65	$143.72	$134.70	$142.15	$117.77	3,032,769,100.00	0.57%
	12/1/12	$142.80	$145.58	$139.54	$142.41	$117.98	2,889,875,900.00	0.18%			Return	Return
	1/1/13	$145.11	$150.94	$144.73	$149.70	$124.90	2,587,140,200.00	5.86%		Return	0.0007009179
	2/1/13	$150.65	$153.28	$148.73	$151.61	$126.50	2,581,459,300.00	1.28%		Return	-0.0000537244	0
	3/1/13	$151.09	$156.85	$150.41	$156.67	$130.72	2,330,972,300.00	3.34%
	4/1/13	$156.59	$159.72	$153.55	$159.68	$133.82	2,907,035,

Question 4 (EXCEL): Estimation of Expected Return and Variance/Covariance Matrix ONLY ANSWER PART 3 1) Go to Yahoo Finance on the Internet (http://finance.yahoo.com/O. Retrieve 37 months ofmonthly...

Answer To: Question 4 (EXCEL): Estimation of Expected Return and Variance/Covariance Matrix ONLY ANSWER PART 3...

Answer To This Question Is Available To Download

Related Questions & Answers

Submit New Assignment