No. of 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.
Print the post  


The Retirement Investing Board
This is the board for all discussions related to Investing for and during retirement. To keep the board relevant and Foolish to everyone, please avoid making any posts pertaining to political partisanship. Fool on and Retire on!
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.
Contact Us
Contact Customer Service and other Fool departments here.
Work for Fools?
Winner of the Washingtonian great places to work, and Glassdoor #1 Company to Work For 2015! 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.