Add a column in your Excel worksheet with the average return across stocks for each ...

Question

Add a column in your Excel worksheet with the average return across stocks for each month. This is the monthly return to an equally weighted portfolio of these 12 stocks. Compute the mean and standard deviation of monthly returns for the equally weighted portfolio. Double check that the average return on this equally weighted portfolio is equal to the average return of all of the individual stocks. Convert these monthly statistics to annual statistics (as described in Step 3) for interpretation.

corpo-qmq2ra0i-xvmtgw44.csv 2022-corporate-assignment-zblty2lo-cxa11l2u.pdf

Prateek · Accepted Answer

1. The companies provided in the list are used to determine the mean monthly return and standard deviation. Following are the mean monthly returns for individual stocks:
a. MSFT: 1.95%
b. JPM: 1.20%
c. IBM: -0.12%
d. HSY: 1.01%
e. EBAY: 1.09%
f. GIS: 0.58%
g. DE: 1.37%
h. CAT: 0.95%
i. BA: 0.92%
j. ADM: 1.07%
The annual mean return is determined by multiplying the mean monthly return by 12 for each stock. Since the portfolio is equally weighted, the average annual return of each stock is taken to determine the annual average portfolio return. 
Similarly, the standard deviation of each stock is computed. Following are the monthly SDs for individual stocks:
a. MSFT: 5.97%
b. JPM: 6.34%
c. IBM: 6.16%
d. HSY: 5.02%
e. EBAY: 8.22%
f. GIS: 4.86%
g. DE: 6.96%
h. CAT: 7%
i. BA: 9.98%
j. ADM: 6.24%
Once the monthly SD is determined, same is multiplied by the square root of 12 to compute the annual SD for each stock. Finally, the average is taken to compute the portfolio SD.
Table below shows the computation of annual SD and mean for the portfolio as well as individual stocks.
	Date
	MSFT
	Return
	JPM
	Return
	IBM
	Return
	HSY
	Return
	EBAY
	Return
	GIS
	Return
	DE
	Return
	CAT
	Return
	BA
	Return
	ADM
	Return
	01-06-2012
	30.59
	 
	35.73
	 
	186.979
	 
	72.03
	 
	17.68098
	 
	38.54
	 
	80.87
	 
	84.91
	 
	74.3
	 
	29.52
	 
	01-07-2012
	29.47
	-4%
	36
	1%
	187.3614
	0%
	71.74
	0%
	18.64478
	5%
	38.7
	0%
	76.82
	-5%
	84.21
	-1%
	73.91
	-1%
	26.09
	-12%
	01-08-2012
	30.82
	5%
	37.14
	3%
	186.2811
	-1%
	71.82
	0%
	19.97896
	7%
	39.33
	2%
	75.11
	-2%
	85.33
	1%
	71.4
	-3%
	26.75
	3%
	01-09-2012
	29.76
	-3%
	40.48
	9%
	198.327
	6%
	70.89
	-1%
	20.35774
	2%
	39.85
	1%
	82.47
	10%
	86.04
	1%
	69.6
	-3%
	27.18
	2%
	01-10-2012
	28.54
	-4%
	41.68
	3%
	185.9751
	-6%
	68.85
	-3%
	20.31145
	0%
	40.08
	1%
	85.44
	4%
	84.81
	-1%
	70.44
	1%
	26.84
	-1%
	01-11-2012
	26.62
	-7%
	41.08
	-1%
	181.7113
	-2%
	73.27
	6%
	22.23064
	9%
	40.99
	2%
	84.05
	-2%
	85.24
	1%
	74.28
	5%
	26.7
	-1%
	01-12-2012
	26.71
	0%
	43.97
	7%
	183.1262
	1%
	72.22
	-1%
	21.46465
	-3%
	40.42
	-1%
	86.42
	3%
	89.61
	5%
	75.36
	1%
	27.39
	3%
	01-01-2013
	27.45
	3%
	47.05
	7%
	194.1396
	6%
	79.45
	10%
	23.5564
	10%
	41.94
	4%
	94.06
	9%
	98.39
	10%
	73.87
	-2%
	28.53
	4%
	01-02-2013
	27.8
	1%
	48.92
	4%
	191.9981
	-1%
	83.34
	5%
	23.02609
	-2%
	46.25
	10%
	87.83
	-7%
	92.37
	-6%
	76.9
	4%
	31.86
	12%
	01-03-2013
	28.61
	3%
	47.46
	-3%
	203.9197
	6%
	87.53
	5%
	22.81986
	-1%
	49.31
	7%
	85.98
	-2%
	86.97
	-6%
	85.85
	12%
	33.73
	6%
	01-04-2013
	33.1
	16%
	49.01
	3%
	193.6329
	-5%
	89.16
	2%
	22.04966
	-3%
	50.42
	2%
	89.3
	4%
	84.67
	-3%
	91.41
	6%
	33.94
	1%
	01-05-2013
	34.9
	5%
	54.59
	11%
	198.8719
	3%
	89.11
	0%
	22.76936
	3%
	47.08
	-7%
	87.11
	-2%
	85.8
	1%
	99.02
	8%
	32.23
	-5%
	01-06-2013
	34.54
	-1%
	52.79
	-3%
	182.7056
	-8%
	89.28
	0%
	21.76768
	-4%
	48.53
	3%
	81.25
	-7%
	82.49
	-4%
	102.44
	3%
	33.91
	5%
	01-07-2013
	31.84
	-8%
	55.73
	6%
	186.4627
	2%
	94.87
	6%
	21.75505
	0%
	52
	7%
	83.07
	2%
	82.91
	1%
	105.1
	3%
	36.47
	8%
	01-08-2013
	33.4
	5%
	50.53
	-9%
	174.2543
	-7%
	91.95
	-3%
	21.03956
	-3%
	49.32
	-5%
	83.64
	1%
	82.54
	0%
	103.92
	-1%
	35.21
	-3%
	01-09-2013
	33.28
	0%
	51.69
	2%
	177.0363
	2%
	92.5
	1%
	23.48485
	12%
	47.92
	-3%
	81.39
	-3%
	83.4
	1%
	117.5
	13%
	36.84
	5%
	01-10-2013
	35.41
	6%
	51.54
	0%
	171.3289
	-3%
	99.24
	7%
	22.18434
	-6%
	50.42
	5%
	81.84
	1%
	83.36
	0%
	130.5
	11%
	40.9
	11%
	01-11-2013
	38.13
	8%
	57.22
	11%
	171.7782
	0%
	96.89
	-2%
	21.26263
	-4%
	50.43
	0%
	84.24
	3%
	84.6
	1%
	134.25
	3%
	40.25
	-2%
	01-12-2013
	37.41
	-2%
	58.48
	2%
	179.3212
	4%
	97.23
	0%
	23.09343
	9%
	49.91
	-1%
	91.33
	8%
	90.81
	7%
	136.49
	2%
	43.4
	8%
	01-01-2014
	37.84
	1%
	55.36
	-5%
	168.9101
	-6%
	99.4
	2%
	22.39057
	-3%
	48.02
	-4%
	85.96
	-6%
	93.91
	3%
	125.26
	-8%
	39.48
	-9%
	01-02-2014
	38.31
	1%
	56.82
	3%
	177.0268
	5%
	105.82
	6%
	24.73485
	10%
	50.03
	4%
	85.93
	0%
	96.97
	3%
	128.92
	3%
	40.6
	3%
	01-03-2014
	40.99
	7%
	60.71
	7%
	184.0249
	4%
	104.4
	-1%
	23.24916
	-6%
	51.82
	4%
	90.8
	6%
	99.37
	2%
	125.49
	-3%
	43.39
	7%
	01-04-2014
	40.4
	-1%
	55.98
	-8%
	187.8298
	2%
	96.24
	-8%
	21.81397
	-6%
	53.02
	2%
	93.34
	3%
	105.4
	6%
	129.02
	3%
	43.73
	1%
	01-05-2014
	40.94
	1%
	55.57
	-1%
	176.2524
	-6%
	97.34
	1%
	21.35101
	-2%
	54.93
	4%
	91.17
	-2%
	102.23
	-3%
	135.25
	5%
	44.94
	3%
	01-06-2014
	41.7
	2%
	57.62
	4%
	173.2983
	-2%
	97.37
	0%
	21.06902
	-1%
	52.54
	-4%
	90.55
	-1%
	108.67
	6%
	127.23
	-6%
	44.11
	-2%
	01-07-2014
	43.16
	4%
	57.67
	0%
	183.2409
	6%
	88.15
	-9%
	22.23485
	6%
	50.15
	-5%
	85.11
	-6%
	100.75
	-7%
	120.48
	-5%
	46.4
	5%
	01-08-2014
	45.43
	5%
	59.45
	3%
	183.8432
	0%
	91.42
	4%
	23.35859
	5%
	53.38
	6%
	84.09
	-1%
	109.07
	8%
	126.8
	5%
	49.86
	7%
	01-09-2014
	46.36
	2%
	60.24
	1%
	181.4818
	-1%
	95.43
	4%
	23.83418
	2%
	50.45
	-5%
	81.99
	-2%
	99.03
	-9%
	127.38
	0%
	51.1
	2%
	01-10-2014
	46.95
	1%
	60.48
	0%
	157.1702
	-13%
	95.91
	1%
	22.09596
	-7%
	51.96
	3%
	85.54
	4%
	101.41
	2%
	124.91
	-2%
	47
	-8%
	01-11-2014
	47.81
	2%
	60.16
	-1%
	155.0382
	-1%
	100.28
	5%
	23.09764
	5%
	52.75
	2%
	86.62
	1%
	100.6
	-1%
	134.36
	8%
	52.68
	12%
	01-12-2014
	46.45
	-3%
	62.58
	4%
	153.3843
	-1%
	103.93
	4%
	23.61953
	2%
	53.33
	1%
	88.47
	2%
	91.53
	-9%
	129.98
	-3%
	52
	-1%
	01-01-2015
	40.4
	-13%
	54.38
	-13%
	146.5679
	-4%
	102.21
	-2%
	22.3064
	-6%
	52.48
	-2%
	85.19
	-4%
	79.97
	-13%
	145.37
	12%
	46.63
	-10%
	01-02-2015
	43.85
	9%
	61.28
	13%
	154.

Add a column in your Excel worksheet with the average return across stocks for each month. This is the monthly return to an equally weighted portfolio of these 12 stocks. Compute the mean and standard...

Solution

Answer To This Question Is Available To Download

Related Questions & Answers

Submit New Assignment