## estimating average annual return from annual data

### estimating average annual return from annual data

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.

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.

### Re: estimating average annual return from annual data

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

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

### Re: estimating average annual return from annual data

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.

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.

### Re: estimating average annual return from annual data

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)

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.

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)

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.

- 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.
- Calculated using the original posters formula: % Gain = $ Gain / (Start + 0.5 X Add)
- Simple average of the five yearly gains: 6.93% = (0.2 + 0.0625 + 0.037 + 0.0263 + 0.0204) / 5
- Calculated as sscritic suggests above: 6.73% = (1.2 X 1.0625 X 1.037 X 1.0263 X 1.0204) ^ (1/5) - 1

### Re: estimating average annual return from annual data

#Cruncher wrote: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)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.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)

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.

- 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.
- Calculated using the original posters formula: % Gain = $ Gain / (Start + 0.5 X Add)
- Simple average of the five yearly gains: 6.93% = (0.2 + 0.0625 + 0.037 + 0.0263 + 0.0204) / 5
- 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.)

### Re: estimating average annual return from annual data

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.

### Re: estimating average annual return from annual data

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

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.smalldata wrote:So in terms of comparing to a mutual fund, what's the correct formula to use?

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%

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`

### Re: estimating average annual return from annual data

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.

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%.

ericd67 wrote: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%.

### Re: estimating average annual return from annual data

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.#Cruncher wrote: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.smalldata wrote:So in terms of comparing to a mutual fund, what's the correct formula to use?

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%

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.

### Re: estimating average annual return from annual data

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.....

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.....

Return to “Investing - Help with Personal Investments”

### Who is online

Users browsing this forum: Cloudy, Coloradoso, Euphonium, Frenchy1011, Gill, Jack FFR1846, Maxdog, MotoTrojan, remedy214, sean.mcgrath, shakeandbake, timwri, walkindude, Yahoo [Bot] and 108 guests