Question

The table below shows standard deviations and correlation coefficients for eight stocks from different countries. Calculate the variance of a portfolio with equal investments in each stock. (Use decimals, not percents, in your calculations. Do not round intermediate calculations. Round your answer to 5 decimal places.)

	BHP	BP	Fiat Chrysler	Heineken	Korea Electric	Nestlè	Sony	Tata Motors
BHP	1.00	.26	.42	.40	.17	.40	.12	.34
BP	.26	1.00	.34	.17	.12	.33	.49	.17
Fiat	.42	.34	1.00	.09	.10	.02	.36	.07
Heineken	.40	.17	.09	1.00	.25	.52	.29	.34
Korea Electric	.17	.12	.10	.25	1.00	−.26	.26	.05
Nestlè	.40	.33	.02	.52	−.26	1.00	.27	.00
Sony	.12	.49	.36	.29	.26	.27	1.00	.11
Tata Motors	.34	.17	.07	.34	.05	.00	.11	1.00
Standard deviation (%)	27.80	37.10	51.06	26.04	35.83	17.70	52.84	47.11

Portfolio variance            

Answer 1

Correlation matrix	BHP	BP	Fiat Chrysler	Heineken	Korea Electric	Nestlè	Sony	Tata Motors
BHP	1	0.26	0.42	0.4	0.17	0.4	0.12	0.34
BP	0.26	1	0.34	0.17	0.12	0.33	0.49	0.17
Fiat	0.42	0.34	1	0.09	0.1	0.02	0.36	0.07
Heineken	0.4	0.17	0.09	1	0.25	0.52	0.29	0.34
Korea Electric	0.17	0.12	0.1	0.25	1	-0.26	0.26	0.05
Nestlè	0.4	0.33	0.02	0.52	-0.26	1	0.27	0
Sony	0.12	0.49	0.36	0.29	0.26	0.27	1	0.11
Tata Motors	0.34	0.17	0.07	0.34	0.05	0	0.11	1

Note that this matrix is symmetrical, ie the correlation of BHP and BP is the same as the correlation of BP and BHP. Also, the correlation of an asset with itself is always 1.

Asset	Weight (Wn)	Standard deviation (σn)	wnσn
BHP	0.12500	0.27800	0.03475
BP	0.12500	0.37100	0.04638
Fiat	0.12500	0.51060	0.06383
Heineken	0.12500	0.26040	0.03255
Korea Electric	0.12500	0.35830	0.04479
Nestlè	0.12500	0.17700	0.02213
Sony	0.12500	0.52840	0.06605
Tata Motors	0.12500	0.47110	0.05889

Annual variance =		0.044665	=MMULT(MMULT(TRANSPOSE(E14:E21),C2:J9),E14:E21)
Therefore volatility =		0.211341	=SQRT(annual variance)

Note= When returning multiple results in an array on the worksheet, enter as a multi-cell array formula with control + shift + enter.

This calculation can also be done by using the traditional portfolio variance formula for 8 assets. However, the number of terms involved in such calculation are 36 {(n*(n+1))/2-> for n=8}. There are 24 covariance terms. The general formula is like this-
=sum(Wn^2*σn^2)+ sum(2*Wn*Wn+1*Covar n,n+1)