Question

Please show all your work/steps. Thank you.

Calculate the mean, variance, and standard deviation of returns for:

(a) CAT & PG individually, and

(b) an equally-weighted portfolio of the two stocks.

For the portfolio, 1st compute the monthly returns.

Then, compute the mean, variance, and standard deviation calculations with Excel’s Data Analysis “Descriptive Statistics” function, “Summary Statistics” (in the function dialogue box).

Compare the return and risk of the three assets.

Monthly Stock Returns
Year	Month	S&P 500	CAT	PG
2010	Jan	0.02851	0.09223	0.02822
	Feb	0.05880	0.10163	(0.00017)
	Mar	0.01476	0.09005	(0.01001)
	Apr	(0.08198)	(0.10765)	(0.01726)
	May	(0.05388)	(0.01133)	(0.01810)
	Jun	0.06878	0.16893	0.02765
	Jul	(0.04745)	(0.06577)	(0.02444)
	Aug	0.08755	0.20749	0.00505
	Sep	0.03686	0.00441	0.06815
	Oct	(0.00229)	0.07642	(0.03929)
	Nov	0.06530	0.10699	0.05330
	Dec	0.02265	0.04067	(0.01128)
2011	Jan	0.03196	0.06098	(0.00134)
	Feb	(0.00105)	0.08187	(0.02302)
	Mar	0.02850	0.04078	0.06244
	Apr	(0.01350)	(0.08331)	0.03238
	May	(0.01826)	0.00627	(0.05128)
	Jun	(0.02147)	(0.06810)	(0.02479)
	Jul	(0.05679)	(0.07893)	0.03559
	Aug	(0.07176)	(0.18854)	(0.00786)
	Sep	0.10772	0.28633	0.02112
	Oct	(0.00506)	0.03621	0.00905
	Nov	0.00853	(0.07440)	0.03314
	Dec	0.04358	0.20986	(0.04742)
2012	Jan	0.04059	0.04667	0.07256
	Feb	0.03133	(0.06733)	(0.00607)
	Mar	(0.00750)	(0.03120)	(0.04502)
	Apr	(0.06265)	(0.14734)	(0.02125)
	May	0.03955	(0.03103)	(0.01665)
	Jun	0.01260	(0.00180)	0.06276
	Jul	0.01976	0.01322	0.04109
	Aug	0.02424	0.00830	0.03241
	Sep	(0.01979)	(0.00835)	0.00640
	Oct	0.00285	0.00510	0.00852
	Nov	0.00707	0.05735	(0.02778)
	Dec	0.02171	0.04899	0.00913

Answer 1

Monthly Stock Returns						Portfolio Return
Year	Month	S&P 500	CAT	Monthly return CAT = (current month closing price-previous month closing price)*100 Jan 2010 is base Year	Monthly return PG = (current month closing price-previous month closing price)*100 Jan 2010 is base Year	Monthly return PG	Year	Month	Monthly return CAT	Monthly return PG	Portfolio return = (weight of CAT stock* CAT monthly)+(Weigth of OF PG *PG monthly return)
2010	Jan	0.02851	0.09223		0.02822		2010	Jan
	Feb	0.0588	0.10163	10.19%	-0.00017	-100.60%		Feb	10.19%	-100.60%	-0.45205
	Mar	0.01476	0.09005	-11.39%	-0.01001	5788.24%		Mar	-11.39%	5788.24%	28.88421
	Apr	-0.08198	-0.10765	-219.54%	-0.01726	72.43%		Apr	-219.54%	72.43%	-0.73559
	May	-0.05388	-0.01133	-89.48%	-0.0181	4.87%		May	-89.48%	4.87%	-0.42304
	Jun	0.06878	0.16893	-1591.00%	0.02765	-252.76%		Jun	-1591.00%	-252.76%	-9.2188
	Jul	-0.04745	-0.06577	-138.93%	-0.02444	-188.39%		Jul	-138.93%	-188.39%	-1.63662
	Aug	0.08755	0.20749	-415.48%	0.00505	-120.66%		Aug	-415.48%	-120.66%	-2.68071
	Sep	0.03686	0.00441	-97.87%	0.06815	1249.50%		Sep	-97.87%	1249.50%	5.758152
	Oct	-0.00229	0.07642	1632.88%	-0.03929	-157.65%		Oct	1632.88%	-157.65%	7.376138
	Nov	0.0653	0.10699	40.00%	0.0533	-235.66%		Nov	40.00%	-235.66%	-0.97828
	Dec	0.02265	0.04067	-61.99%	-0.01128	-121.16%		Dec	-61.99%	-121.16%	-0.91575
2011	Jan	0.03196	0.06098	49.94%	-0.00134	-88.12%	2011	Jan	49.94%	-88.12%	-0.19091
	Feb	-0.00105	0.08187	34.26%	-0.02302	1617.91%		Feb	34.26%	1617.91%	8.260838
	Mar	0.0285	0.04078	-50.19%	0.06244	-371.24%		Mar	-50.19%	-371.24%	-2.10716
	Apr	-0.0135	-0.08331	-304.29%	0.03238	-48.14%		Apr	-304.29%	-48.14%	-1.76217
	May	-0.01826	0.00627	-107.53%	-0.05128	-258.37%		May	-107.53%	-258.37%	-1.82948
	Jun	-0.02147	-0.0681	-1186.12%	-0.02479	-51.66%		Jun	-1186.12%	-51.66%	-6.18891
	Jul	-0.05679	-0.07893	15.90%	0.03559	-243.57%		Jul	15.90%	-243.57%	-1.13831
	Aug	-0.07176	-0.18854	138.87%	-0.00786	-122.08%		Aug	138.87%	-122.08%	0.083925
	Sep	0.10772	0.28633	-251.87%	0.02112	-368.70%		Sep	-251.87%	-368.70%	-3.10285
	Oct	-0.00506	0.03621	-87.35%	0.00905	-57.15%		Oct	-87.35%	-57.15%	-0.72252
	Nov	0.00853	-0.0744	-305.47%	0.03314	266.19%		Nov	-305.47%	266.19%	-0.1964
	Dec	0.04358	0.20986	-382.07%	-0.04742	-243.09%		Dec	-382.07%	-243.09%	-3.1258
2012	Jan	0.04059	0.04667	-77.76%	0.07256	-253.02%	2012	Jan	-77.76%	-253.02%	-1.65388
	Feb	0.03133	-0.06733	-244.27%	-0.00607	-108.37%		Feb	-244.27%	-108.37%	-1.76317
	Mar	-0.0075	-0.0312	-53.66%	-0.04502	641.68%		Mar	-53.66%	641.68%	2.940097
	Apr	-0.06265	-0.14734	372.24%	-0.02125	-52.80%		Apr	372.24%	-52.80%	1.597224
	May	0.03955	-0.03103	-78.94%	-0.01665	-21.65%		May	-78.94%	-21.65%	-0.50293
	Jun	0.0126	-0.0018	-94.20%	0.06276	-476.94%		Jun	-94.20%	-476.94%	-2.85568
	Jul	0.01976	0.01322	-834.44%	0.04109	-34.53%		Jul	-834.44%	-34.53%	-4.34486
	Aug	0.02424	0.0083	-37.22%	0.03241	-21.12%		Aug	-37.22%	-21.12%	-0.2917
	Sep	-0.01979	-0.00835	-200.60%	0.0064	-80.25%		Sep	-200.60%	-80.25%	-1.40428
	Oct	0.00285	0.0051	-161.08%	0.00852	33.13%		Oct	-161.08%	33.13%	-0.63976
	Nov	0.00707	0.05735	1024.51%	-0.02778	-426.06%		Nov	1024.51%	-426.06%	2.992267
	Dec	0.02171	0.04899	-14.58%	0.00913	-132.87%		Dec	-14.58%	-132.87%	-0.73721
		Average return	Using average function in MS excel	-107.96%		143.92%			Average return	Using average function in MS excel	0.179829
		variance		2553.18%		11048.36%			variance		3466.64%
		standard deviation = standard deviation using STDEVP function in MS excel		5.05		10.51			standard deviation = standard deviation using STDEVP function in MS excel		5.89