Question

Suppose you build a portfolio of 25% in A and 75% in B, calculate expected returns, volatility, and a 95% confidence interval for the portfolio.

Stock A	Stock B
2011	10.00%	6.00%
2012	7.00%	2.00%
2013	15.00%	5.00%
2014	-5.00%	1.00%
2015	8.00%	-2.00%

Answer 1

we calculate average returns for each stock using the AVERAGE function in Excel as below :

we calculate standard deviation for each stock using the STDEV function in Excel as below :

For a portfolio with 25% weight in A, and 75% weight in B,

expected return = (25% * 7.00%) + (75% * 2.40%) = 3.55%

Expected variance for a two-asset portfolio σ_p² = w₁²σ₁² + w₂²σ₂² + 2w₁w₂Cov_1,2

where σ_p² = variance of the portfolio

w₁ = weight of Asset 1

w₂ = weight of Asset 2

σ₁² = variance of Asset 1

σ₂² = variance of Asset 2

Cov_1,2 = covariance of returns between Asset 1 and Asset 2

Cov_1,2 = ρ_1,2 * σ₁ * σ_2, where ρ_1,2 = correlation of returns between Asset 1 and Asset 2

covariance between A and B is calculated using the COVAR function in Excel as below :

variance of portfolio = (0.25)²*(0.066)² + (0.75)²*(0.0287)² + (2)*(0.25)*(0.75)*(0.00088)

variance of portfolio = 0.001066

standard deviation = 0.001066

standard deviation = 0.0326, or 3.26%

95% confidence interval has a Z-value of 1.960

confidence interval = mean + (z-value)*(standard deviation / number of observations)

confidence interval = 3.55 +/- (1.960)*(3.26/5)

confidence interval = 3.55 +/- 1.278

confidence interval = 2.272 to 4.828