3
$\begingroup$

I am currently trying to calculate a vector for the mean annual returns of 4 different asset classes along with their 4x4 covariance matrix in excel. However, I am having problems since the data I have been supplied is quarterly returns (as calculated from the relevant stock indicies). I know that quarterly and annual returns are linked by the following equation $$r_{Annual,x}=\prod_{q=1}^{4}(1+r_{q,x})-1$$ Where $r_{i}$ is the return for the $i^{th}$ quarter and $x$ denotes that this is the $x^{th}$ year. What I have done is calculate $(1+r_{i,j})^4-1$ where $r_{i,j}$ is the $j^{th}$ quarterly return observation for the $i^{th}$ asset class (I have 4 asset class).

This results in 4 new vectors where each quarterly return has been converted to an effective annual return. I then apply the mean and covariance functions to these converted values to get the mean vector and covariance matrix.

However I get the feeling this is wrong. Any feedback would be greatly appreciated and if you could provide a worked example with the following (quarterly return) data simulated in excel that would be great.

Asset 1 Asset 2 Asset 3 Asset 4
1.98%   2.99%   1.91%   3.24%
-1.22%  -1.87%  1.18%   2.35%
5.00%   6.10%   1.08%   4.46%
2.45%   -0.10%  1.75%   3.06%
5.70%   3.70%   1.47%   2.10%
8.29%   1.96%   1.51%   0.62%
-3.36%  -1.25%  1.11%   2.72%
-2.01%  0.21%   1.49%   0.90%
-4.99%  2.44%   1.48%   2.02%
4.67%   -0.29%  1.48%   1.51%
0.86%   2.92%   1.54%   2.38%
7.52%   -0.75%  1.43%   3.36%
-1.24%  3.11%   1.20%   1.93%
-4.25%  1.41%   1.35%   0.84%
3.89%   -0.25%  0.86%   1.46%
0.67%   4.13%   1.45%   2.30%
3.93%   1.53%   1.43%   1.35%
-5.00%  -0.63%  1.50%   3.35%
3.47%   2.99%   1.50%   3.06%
7.76%   3.61%   0.98%   3.79%
-8.26%  1.03%   1.18%   2.90%
5.45%   1.57%   1.05%   3.38%
-2.65%  2.25%   1.44%   1.45%
0.76%   7.50%   1.52%   1.79%
4.55%   -2.72%  1.31%   1.82%
1.32%   8.70%   1.36%   1.24%
-3.02%  -1.43%  1.52%   3.92%
2.05%   1.20%   1.94%   2.50%
7.37%   0.29%   1.64%   2.73%
-0.66%  2.36%   1.75%   2.68%
-6.86%  -1.40%  1.35%   2.62%
13.55%  6.03%   1.30%   1.33%
-2.23%  5.26%   1.44%   1.48%
-4.26%  0.45%   1.61%   2.93%
6.75%   1.70%   1.08%   1.26%
-0.84%  -2.16%  0.89%   2.45%
-0.66%  4.03%   1.66%   2.98%
0.15%   1.94%   1.21%   3.13%
-4.74%  0.26%   1.44%   2.35%
1.29%   -0.76%  0.96%   1.52%
13.22%  0.37%   1.74%   4.11%
-2.37%  0.41%   1.25%   2.71%
3.16%   1.27%   1.22%   2.54%
1.56%   4.54%   0.87%   3.55%
0.97%   2.18%   1.52%   1.75%
3.32%   2.41%   1.64%   2.12%
-1.80%  -2.07%  1.12%   0.77%
7.16%   1.87%   1.61%   3.68%
10.65%  4.89%   1.25%   2.47%
5.47%   4.26%   1.33%   1.94%
0.31%   5.53%   1.32%   3.89%
3.59%   4.55%   1.46%   2.27%
7.24%   1.50%   1.67%   1.32%
3.06%   0.87%   1.78%   3.43%
7.43%   3.92%   1.58%   3.05%
11.29%  2.17%   1.47%   2.76%
11.85%  3.64%   1.59%   1.57%
1.68%   -1.25%  1.48%   2.37%
9.93%   -0.53%  1.95%   1.76%
-9.09%  -3.53%  1.32%   3.30%
-3.09%  3.00%   1.41%   2.86%
2.99%   1.49%   1.34%   1.97%
0.28%   3.81%   1.30%   2.27%
9.23%   6.06%   1.17%   2.44%
6.74%   7.60%   1.52%   1.66%
0.48%   5.72%   1.61%   1.25%
-2.28%  0.96%   1.30%   2.69%
4.16%   -1.94%  1.68%   2.95%
7.21%   4.77%   1.59%   1.40%
-3.97%  -0.84%  1.56%   2.11%
2.48%   1.46%   1.22%   3.60%
5.89%   1.65%   1.83%   1.62%
-2.73%  4.73%   1.37%   1.99%
9.12%   -0.52%  1.29%   2.89%
1.29%   3.89%   1.35%   2.47%
4.18%   -1.76%  1.58%   3.85%
-9.78%  2.66%   1.09%   1.63%
10.73%  -1.80%  1.57%   3.11%
3.37%   -0.03%  1.67%   1.57%
11.17%  5.92%   1.26%   2.04%
7.13%   3.02%   1.79%   2.29%
13.12%  -0.75%  1.25%   2.72%
-2.73%  1.45%   1.04%   3.61%
-8.38%  -1.19%  1.16%   2.15%
4.63%   0.53%   1.33%   3.00%
1.93%   4.88%   1.39%   3.65%
-6.75%  0.74%   0.95%   2.40%
5.43%   0.75%   1.33%   2.76%
-4.68%  3.02%   0.90%   2.38%
4.99%   4.99%   1.66%   0.84%
-0.44%  3.68%   1.51%   1.84%
13.18%  3.82%   1.69%   2.88%
8.05%   2.02%   0.79%   2.50%
-3.39%  1.93%   1.15%   2.65%
21.79%  3.30%   1.36%   2.12%
-5.96%  6.49%   1.58%   3.43%
1.43%   -1.17%  0.89%   0.57%
10.21%  4.70%   1.27%   1.69%
-13.53% 7.99%   1.31%   2.62%
-5.25%  -0.46%  1.08%   3.75%
$\endgroup$
  • $\begingroup$ Hi @user135784. As you wrote down the equation already it seems to me like you solved your question already: Just apply your formula to blocks of $4$ quarterly observations will give you vector of annual observations. Excel then provides an easy way to obtain mean and covariance. $\endgroup$ – muffin1974 Nov 2 '15 at 9:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.