The Motley Fool Discussion Boards

Previous Page

Investing/Strategies / Retirement Investing


Subject:  Re: Poll: Did your 401k recover from the 2008 de Date:  12/27/2010  7:57 PM
Author:  Rayvt Number:  67974 of 88065

The formula I use to calculate my rate of return in my spreadsheet is as follows :

=100*(annual market value change/(starting balance +(personal contributions+employer match)/2))

This is the way I account for the fact that the contributions are made throughout the year - I count the return on half the contributions.

Accurately computing your return is surprisingly easy to get wrong. Variations of the "Beardstown Ladies" problem occur all the time--especially for people who are not a guru in arcane financial formulas.

If you are using an Excel spreadsheet you can easily get the accurate rate of return with the XIRR function. There'e really no excuse to not use XIRR.

However, a reasonably SWAG is a bit different from your forumula.
return = ((ending_value - (total_contributions/2)) / ((starting_value + (total_contributions/2)). What this does is say that your ending value is too large by 1/2 the total contribution and the starting value is too small by 1/2 the contribution, and adjusts those values accordingly. In essense, it does the math as if half of your total contribution went in at the start and half went in at the end.

So when you run the math to compute your returns, obviously your ending value is not 200, but is 200 - 15 (since 15 of it is the money that you just put in). And your starting value is obviously not 150, but is 150 + 15 (since you started with 150 and immediately put in another 15).

Lumping the contributions in with either the final value or starting value will greatly distort the math.

For example: end = 200, start = 150, total contributions = 30.
Lumping at the start (your formula?):
is (200 / (150 + 15)) or (200 / 165) or 1.212 = 21%

But from your description, you formula would seem to be: (200 - 150) / (150 + 30/2) or (50 / 165) or 30.3%.

Lumping at the end:
is ((200 - 15) / 150) or (185 / 150) or 1.233 = 24%

Distributed lumping:
is (200 - 15) / (150 + 15) or (185 / 165) or 1.121 = 12%

I plugged numbers into excel, starting value 150, contribute 2.5 (30 / 12) at the beginning of each month, ending value 200. XIRR of that is 1.1205 or 12%

If I change the monthly contribution to the 15th, the XIRR is 1.121.

Hmmmm, wow.....I knew your formula was wrong (based on my own expererience) but I didn't realise it was **that** wrong.
What you think is a 30% return is actually 12%.

For those who don't know, the "Beardstown Ladies" problem was that they didn't do ANY adjustment for the contributions, so they used:
(200 / 100) or 2.00 = 100%. No wonder they thought they were financial geniuses.
Copyright 1996-2018 trademark and the "Fool" logo is a trademark of The Motley Fool, Inc. Contact Us