Question

Following the three-stock example in Section D to calculate the variances and volatilities of the following portfolios with equal weights:

e. 6-stock portfolio consisted of Dell, Delta Air, AA, GM, Ford and Anheuser.

            f. All 7-stock portfolio.

		Microsoft	Dell	Delta Air	AA	GM	Ford	Anheuser
	Std.Dev	42%	54%	50%	72%	33%	37%	18%
		Correlations:
Microsoft	42%	1	65%	27%	19%	22%	6%	-7%
Dell	54%	65%	1	19%	18%	32%	32%	10%
Delta Air	50%	27%	19%	1	69%	31%	38%	19%
AA	72%	19%	18%	69%	1	35%	58%	11%
GM	33%	22%	32%	31%	35%	1	64%	11%
Ford	37%	6%	32%	38%	58%	64%	1	10%
Anheuser	18%	-7%	10%	19%	11%	11%	10%	1

Answer 1

Formula used in cell B20:

=((B13*$B$5)^2)+(($B$6*B14)^2)+(($B$7*B15)^2)+(($B$8*B16)^2)+(($B$9*B17)^2)+(($B$10*B18)^2)+(2*B13*B14*$D$6*$B$5*$B$6)+(2*B13*B15*$D$7*$B$5*$B$7)+(2*B13*B16*$D$8*$B$5*$B$8)+(2*B13*B17*$D$9*$B$5*$B$9)+(2*B13*B18*$D$10*$B$5*$B$10)+(2*B14*B15*$E$7*$B$6*$B$7)+(2*B14*B16*$B$6*$B$8*$E$8)+(2*B14*B17*$E$9*$B$6*$B$9)+(2*B14*B18*$E$10*$B$6*$B$10)+(2*B15*B16*$F$8*$B$7*$B$8)+(2*B15*B17*$F$9*$B$7*$B$9)+(2*B15*B18*$F$10*$B$7*$B$10)+(2*B16*B17*$G$9*$B$8*$B$9)+(2*B16*B18*$G$10*$B$8*$B$10)+(2*B17*B18*$H$10*$B$9*$B$10)

Formula used in cell B21:  =SQRT(B20)

Formula used in cell B32:

=((B24*B4)^2)+((B25*$B$5)^2)+(($B$6*B26)^2)+(($B$7*B27)^2)+(($B$8*B28)^2)+(($B$9*B29)^2)+(($B$10*B30)^2)+(2*B24*B25*B4*B5*C5)+(2*B24*B26*B4*B6*C6)+(2*B24*B27*B4*B7*C7)+(B24*B28*B4*B8*C8)+(2*B24*B29*B4*B9*C9)+(2*B24*B30*B4*B10*C10)+(2*B25*B26*$D$6*$B$5*$B$6)+(2*B25*B27*$D$7*$B$5*$B$7)+(2*B25*B28*$D$8*$B$5*$B$8)+(2*B25*B29*$D$9*$B$5*$B$9)+(2*B25*B30*$D$10*$B$5*$B$10)+(2*B26*B27*$E$7*$B$6*$B$7)+(2*B26*B28*$B$6*$B$8*$E$8)+(2*B26*B29*$E$9*$B$6*$B$9)+(2*B26*B30*$E$10*$B$6*$B$10)+(2*B27*B28*$F$8*$B$7*$B$8)+(2*B27*B29*$F$9*$B$7*$B$9)+(2*B27*B30*$F$10*$B$7*$B$10)+(2*B28*B29*$G$9*$B$8*$B$9)+(2*B28*B30*$G$10*$B$8*$B$10)+(2*B29*B30*$H$10*$B$9*$B$10)

Formula used in cell B33:  =SQRT(B32)