UnThreaded | Threaded | Whole Thread (48) | Ignore Thread Prev | Next
Author: Rayvt Big gold star, 5000 posts Top Favorite Fools Add to my Favorite Fools Ignore this person (you won't see their posts anymore) Number: of 75383  
Subject: Re: Poll: Did your 401k recover from the 2008 de Date: 12/27/2010 7:57 PM
Post New | Post Reply | Reply Later | Create Poll . Report this Post | Recommend it!
Recommendations: 0
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.
Post New | Post Reply | Reply Later | Create Poll . Report this Post | Recommend it!
Print the post  
UnThreaded | Threaded | Whole Thread (48) | Ignore Thread Prev | Next

Announcements

The Retire Early Home Page
Discussion on accelerating retirement day.
What was Your Dumbest Investment?
Share it with us -- and learn from others' stories of flubs.
When Life Gives You Lemons
We all have had hardships and made poor decisions. The important thing is how we respond and grow. Read the story of a Fool who started from nothing, and looks to gain everything.
Community Home
Speak Your Mind, Start Your Blog, Rate Your Stocks

Community Team Fools - who are those TMF's?
Contact Us
Contact Customer Service and other Fool departments here.
Work for Fools?
Winner of the Washingtonian great places to work, and "#1 Media Company to Work For" (BusinessInsider 2011)! Have access to all of TMF's online and email products for FREE, and be paid for your contributions to TMF! Click the link and start your Fool career.
Advertisement