Question

		Stock Fund	Bond Fund
Scenario	Probability	Rate of Return	Rate of Return
Recession	0.35	−9.0%	5.0%
Normal	0.40	6.0%	7.5%
Boom	0.25	28.0%	−6.5%

Calculate the correlation of the combined portfolio. Use the excel spreadsheet template.

Answer 1

We know the formula for correlation r = cov( x, y)/(σx * σy)

Standard Deviation formula is (ΣP(X-x̅)2)^1/2

Here X = Stock fund

Y = Bond fund

And cov ( x, y) is P(X-x̅)*(y-ȳ )

And expected return is ΣPx ie x̅ and ȳ respectively

Let us construct an excel table as below.

Particulars	P	X	Y	P(X-x̅)2	P(y-ȳ )2	P(X-x̅)*(y-ȳ )
Recession	0.35	-0.090	0.050	0.008140	0.000123	-0.0010
Normal	0.4	0.060	0.075	0.000003	0.000766	-0.00004375
Boom	0.25	0.280	-0.065	0.011827	0.002316	-0.0052

Expected return x̅ = 0.0625 and ȳ = 0.0313

Standard Deviation is (ΣP(x-x̅)²)^1/2 = 0.14131 and (ΣP(y-ȳ )²)^1/2 = 0.05661

Covariance is -0.006278.

Now the correlation coefficient is -0.006278/(0.14131*0.05661) = -0.7848

Hence the correlation is -0.7848. that means they are negatively correlated to each other and they move opposite to each other.