Finding Annualized Mean and Standard Deviation For Portfolio

Question

Say I'm looking at the monthly investment returns of 3 funds:

Date    Index 1     Index 2 Index 3
7/1/2012    0.19%   1.10%   0.26%
8/1/2012    -0.13%  0.22%   0.13%
9/1/2012    -0.11%  -0.12%  0.27%
10/1/2012   -0.25%  -1.31%  0.13%
11/1/2012   0.70%   1.66%   0.63%
12/1/2012   1.09%   1.07%   1.76%
1/1/2013    0.04%   -0.82%  -0.32%
2/1/2013    1.83%   1.41%   0.83%
3/1/2013    0.63%   0.24%   0.47%
4/1/2013    0.45%   0.49%   0.74%
5/1/2013    -0.10%  0.65%   -0.53%
6/1/2013    -0.83%  -0.86%  -0.54%
7/1/2013    1.38%   1.53%   2.06%
8/1/2013    -0.33%  -0.05%  0.05%
9/1/2013    0.27%   -0.14%  0.97%
10/1/2013   -0.63%  -0.35%  0.14%
11/1/2013   -0.14%  -0.75%  -0.74%
12/1/2013   -0.75%  -0.10%  -0.38%
1/1/2014    1.06%   0.33%   8.72%
2/1/2014    0.04%   0.91%   -0.65%

I've put 50% in Index 1, 25% in Index 2 and 25% in Index 3. This is my portfolio. For this portfolio, I want to find the annualized return and the annualized standard deviation for the entire 3 year period here. I'm confused about how exactly to do this.

Do I annualize the returns individually, then take the average of the individual annualized returns, and then use the weighted average to find the portfolio's annualized return? Or is there another way?

Here is an example of what I am trying to implement:

https://docs.google.com/spreadsheets/d/1RT7kJvlO3-rvmSzSUYXgOjS1jabddzAB5l5kKa07d7s/edit?usp=sharing

How do I find the annualized standard deviation for the entire portfolio?

Is there a way to see the best 12 month return for the historical data?

Answer 1

The first step is to convert the returns to regular numbers.

e.g. .19% turns into 1.0019. Below the stack of numbers is the product function. "=PRODUCT(D4:D24)" multiplies each cell in that range. The math of compound returns is not addition, it's multiplication. 2 time units of 10% returns result in 1.1 * 1.1 = 1.21 or 21%, not 10% + 10 % = 20%. And, to exaggerate an important point, A return of +50% and then -50% does not average to break-even, 1.5 * .5 = .75 or a 25% loss over the full time, -13.4% per year, compounded.

Now, you don't have 3 years, not even 2. To take 20 months and annualize it, you raise the return to the 12/20 power. I'd say .6 power, but it might not be obvious where that comes from. The 20th root offers a monthly return, then 12th power of that, yearly. 1.0445^.6= 1.0264 or 2.64%/yr CAGR.

You can repeat this and analyze the data as you wish, but, in my opinion, 20 months of data doesn't offer much in the way of a comparison. If my entire investing life saw the same returns as my best 20 month period, I'd be a billionaire.

As far as STDEV goes, that's just another function you can use in the spreadsheet.