The Motley Fool Discussion Boards
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 78168|
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%
is (200 - 15)