estimating average annual return from annual data

[MnD]

I have a spreadsheet of 26 years of data that is my lifetime combined 401-K and rollover IRA performance from 1986-2012.
For each year I have only three observed data values and two computed values in each row of the 26 row spreadsheet:

Observed:
Year starting balance, annual contributions, year ending balance
Computed:
annual investment gain = ending balance-contributions-beginning balance
annual % return = (annual investment gain /((annual starting balance +(0.5*annual contributions))

It starts at zero balance in year 1 and I have never withdrawn any funds.

Is their a way to come up with a single 26-year annual average performance measure like mutual funds and ETF's report?
The arithmetic average of the 26 annual annual % return values is 8.28%, but I suspect that is not the correct way to go about it.

[Stevee]

Hi, I am a newbie here, and I don't purport to know how mutual funds and ETFs report return (or, for that matter, whether various fund companies report realistically or on any kind of uniform basis).
However, I do think you want to look at a geometric mean return rather than an arithmetic average return.

The geometric mean will always be smaller than the arithmetic mean.
I do not see any validity in the arithmetic average in this situation.

Steve

[sscritic]

So start with 1+gain for each year, e.g., 1.06 for a year you were up 6%.
Multiply your 26 numbers together. Take the 1/26 power of your answer. Subtract 1.

What that means, I am not sure, as I am assuming your balance was small in those first years but large in the latter.

There is another method. Use XIRR in excel. Assume all your contributions were made on July 1. Ignore all other values other than your starting value of zero and your value as of the close today. I know it's not the end of the year, but it's close.

[#Cruncher]

You can get an approximation using Excel's IRR function. Here is an example of how you could do it. I've simplified your layout to cover just five years with $100 added each year and a $10 gain each year. (1)

Code: Select all

  Col:    A      B       C       D       E        F (2)       G
  Row   Year   Start    Add     End   $ Gain    % Gain       Date
  ---   ----   -----    ---     ---   ------    ------    ---------
   2      1       0   [ 100 ]   110      10     20.00%	[ 7/01/2000 ]
   3      2     110   [ 100 ]   220      10      6.25%	[ 7/01/2001 ]
   4      3     220   [ 100 ]   330      10      3.70%	[ 7/01/2002 ]
   5      4     330   [ 100 ]   440      10      2.63%	[ 7/01/2003 ]
   6      5     440   [ 100 ]   550      10      2.04%	[ 7/01/2004 ]
   7                  [-550 ]                            [12/31/2004 ]
   8              IRR: 3.19%	  Arithmetic Avg: 6.93% (3)
   9             XIRR: 3.82%      Geometric Avg: 6.73% (4)

I copied the negation of the final ending balance to just below the last Add amount. Below that I entered the following formula: =IRR(C2:C7). One way to interpret this IRR, or Internal Rate of Return, is as the interest rate on a savings account. If I deposited $100 at the beginning of each year for the next five years in a savings account earning 3.19%, at the end of five years my account balance would be $550.

The IRR overstates the return because it assumes investments are made at the beginning of the year instead of at mid-year or spread over the year as does the original poster. The XIRR function handles this because it takes a list of dates as as well as a list of amounts as parameters. In the example above it is 3.82% and is calculated with the formula: =XIRR(C2:C7, G2:G7). Over a period of 26 years, I expect the difference between IRR and XIRR would be less.

Both the arithmetic and geometric average exceed the results of IRR and XIRR because they overemphasize the large return in year one when little was invested.

1. I used the same contribution and gain each year for simplicity. However, the IRR and XIRR functions don't require them to be the same.
2. Calculated using the original posters formula: % Gain = $ Gain / (Start + 0.5 X Add)
3. Simple average of the five yearly gains: 6.93% = (0.2 + 0.0625 + 0.037 + 0.0263 + 0.0204) / 5
4. Calculated as sscritic suggests above: 6.73% = (1.2 X 1.0625 X 1.037 X 1.0263 X 1.0204) ^ (1/5) - 1

[smalldata]

You can get an approximation using Excel's IRR function. Here is an example of how you could do it. I've simplified your layout to cover just five years with $100 added each year and a $10 gain each year. (1)

Code: Select all

  Col:    A      B       C       D       E        F (2)       G
  Row   Year   Start    Add     End   $ Gain    % Gain       Date
  ---   ----   -----    ---     ---   ------    ------    ---------
   2      1       0   [ 100 ]   110      10     20.00%	[ 7/01/2000 ]
   3      2     110   [ 100 ]   220      10      6.25%	[ 7/01/2001 ]
   4      3     220   [ 100 ]   330      10      3.70%	[ 7/01/2002 ]
   5      4     330   [ 100 ]   440      10      2.63%	[ 7/01/2003 ]
   6      5     440   [ 100 ]   550      10      2.04%	[ 7/01/2004 ]
   7                  [-550 ]                            [12/31/2004 ]
   8              IRR: 3.19%	  Arithmetic Avg: 6.93% (3)
   9             XIRR: 3.82%      Geometric Avg: 6.73% (4)

I copied the negation of the final ending balance to just below the last Add amount. Below that I entered the following formula: =IRR(C2:C7). One way to interpret this IRR, or Internal Rate of Return, is as the interest rate on a savings account. If I deposited $100 at the beginning of each year for the next five years in a savings account earning 3.19%, at the end of five years my account balance would be $550.

The IRR overstates the return because it assumes investments are made at the beginning of the year instead of at mid-year or spread over the year as does the original poster. The XIRR function handles this because it takes a list of dates as as well as a list of amounts as parameters. In the example above it is 3.82% and is calculated with the formula: =XIRR(C2:C7, G2:G7). Over a period of 26 years, I expect the difference between IRR and XIRR would be less.

Both the arithmetic and geometric average exceed the results of IRR and XIRR because they overemphasize the large return in year one when little was invested.

1. I used the same contribution and gain each year for simplicity. However, the IRR and XIRR functions don't require them to be the same.
2. Calculated using the original posters formula: % Gain = $ Gain / (Start + 0.5 X Add)
3. Simple average of the five yearly gains: 6.93% = (0.2 + 0.0625 + 0.037 + 0.0263 + 0.0204) / 5
4. Calculated as sscritic suggests above: 6.73% = (1.2 X 1.0625 X 1.037 X 1.0263 X 1.0204) ^ (1/5) - 1

So in terms of comparing to a mutual fund, what's the correct formula to use? The geometric average is what I would have intuitively used, and after looking up the IRR http://en.wikipedia.org/wiki/Internal_rate_of_return it seems reasonable as well. The OP was asking about comparing this to fund performance, and from this http://www.bogleheads.org/forum/viewtopic.php?t=16717 it sounds like the fund performance is based on APY and not APR? (Probably one could just approximate it as continuously compounded, and convert by e^r - 1.)

[aquifer]

If you just take the current balance minus the total of your contributions you'll have the gain. If you divide that by the ending balance and divide by your 26 years won't that give you an average annual return? That's more of a question than an answer because I'm interested in knowing how to do this.

[#Cruncher]

Is their a way to come up with a single 26-year annual average performance measure like mutual funds and ETF's report?

So in terms of comparing to a mutual fund, what's the correct formula to use?

I don't know that there is one since the return reported by a mutual fund only reflects an initial investment at the beginning of the period. It does not reflect investments in subsequent years.

For example the Vanguard Total Stock Market Index Fund Investor Shares (VTSMX) Price & Performance page reports a 15.80% "Average annual returns—updated monthly as of 11/30/2012" for one year. This is computed as follows using data from the Distributions page:

Code: Select all

   Date      Div $ / Sh   Price / Sh   Shs Reinvest   Cum # Shs    $ Value
----------   ----------   ----------   ------------   ---------    -------
11/30/2011                   31.21                      32.041    1,000.00 
12/20/2011      0.167        30.89         0.173        32.214      995.10 
03/23/2012      0.140        34.93         0.129        32.343    1,129.75 
06/22/2012      0.158        33.17         0.154        32.497    1,077.94 
09/21/2012      0.167        36.34         0.149        32.647    1,186.38 
11/30/2012                   35.47                      32.647    1,157.98
                                                 1 year return -->  15.80%

$1,000 invested in the fund on 11/30/2011 would have bought 32.041 shares at the current price of $31.21 per share. Reinvesting dividends one would have ended with 32.647 shares on 11/30/2012. These were worth $1,158 at the current price of $35.47 per share. This is a 15.80% increase in value.

If the period is more than 1 year then the following formula is used:

Code: Select all

annual return = (End Value / Start Value) ^ (1 / # of years) - 1

For example, the Price & Performance page shows a 3 year annual return of 11.79%. If I expanded the above table I expect it would show that $1,000 invested on 11/30/2009 would have grown -- with dividends reinvested -- to about $1,397 since 11.79% = 1.397 ^ (1/3) - 1.

[smalldata]

Thanks! That concrete example explained to me exactly how a fund calculates its return rate, and I was able to duplicate your calculation. If what the OP wanted is to calculate the rate at which a dollar invested in their portfolio 26 years ago would have grown, it seems like the geometric average of the yearly return rates should be used. That would at least be an apples to apples comparison of the return rate provided by a mutual fund.

If you just take the current balance minus the total of your contributions you'll have the gain. If you divide that by the ending balance and divide by your 26 years won't that give you an average annual return? That's more of a question than an answer because I'm interested in knowing how to do this.

I don't think that's correct. Suppose you start with $100, and find that it grows to $200 three years later with no contributions. You want the average rate to work out so that $100 * (1+R)^3 = $200. That's an average rate of 26%.

[#Cruncher]

So in terms of comparing to a mutual fund, what's the correct formula to use?

I don't know that there is one since the return reported by a mutual fund only reflects an initial investment at the beginning of the period. It does not reflect investments in subsequent years.

However, as i thought about this I realized one could compare to a mutual fund by taking its annual % returns and applying them against one's own annual contributions. For example, here are the annual returns 1996 - 2011 of the Vanguard Total Stock Market Index Fund Investor Shares (VTSMX) (1) applied against a hypothetical $100 per year contribution.

Code: Select all

Year      % Gain       Investment      Value
----     --------      ----------      -----
1996       20.96%       [   100 ]        121
1997       30.99%       [   100 ]        289
1998       23.26%       [   100 ]        480
1999       23.81%       [   100 ]        718
2000      (10.57%)      [   100 ]        732
2001      (10.97%)      [   100 ]        740
2002      (20.96%)      [   100 ]        664
2003       31.35%       [   100 ]      1,004
2004       12.52%       [   100 ]      1,242
2005        5.98%       [   100 ]      1,422
2006       15.51%       [   100 ]      1,758
2007        5.49%       [   100 ]      1,960
2008      (37.04%)      [   100 ]      1,297
2009       28.70%       [   100 ]      1,798
2010       17.09%       [   100 ]      2,223
2011        0.96%       [   100 ]      2,345
                        [-2,345 ]
                    IRR:  4.36%

In this case $100 contributed at the beginning of each year starting in 1996 would have grown to $2,345 at the end of 2011. (2) To compare this fund's performance to one's own portfolio performance, replace the dummy $100 annual contributions with what one actually invested each year. (3) Then one can compare the resulting ending value and IRR to that of one's own portfolio to see which did better.

One could also do a similar comparison against an historical index. For instance Historical Annual Returns for the S&P 500 Index - Updated Through 2011 lists the S&P 500 annual returns going back to 1926.

1) The 1996 figure is no longer shown on the Vanguard page. I'd pulled it a year ago.
2) This produces a 4.36% IRR as calculated in my first post above.
3) If one's portfolio goes back earlier than 1996, add its value at 12/31/1995 to the 1996 investment.

[MnD]

Thanks #Cruncher and others!

XIRR with my data and your example (used July 1 for each annual contribution date) returned 6.20% versus an arithmetic average of 25 annual returns of 8.28%.
My actual time series was 1987-2012 (I made a mistake in my original post) so a nice even 25 year analysis (with one trading day still out).

I'll have to look for some long-term annual data back to 1987 to see how I did against some kind of benchmark per your 2nd example.
Over the years I've varied between 60/40 to 75/25 stocks/bonds+cash - so perhaps Vanguard Wellington if I can find the annual data that far back.....