Hello and thank you for your question.
I know of two methods to get the result you're looking for. Excel's
IRR (internal rate of return) function will provide the answer. But I
find it easier use Excel to weight each contribution by how long it
has been in the fund, which allows you to compute the answer directly.
Here's one person's explanation of the first method, using the excel
IRR function. I don't think the writer has it exactly right, though,
because the method seems to assume that the 401(k) contributions go in
on the same date every year:
"Fortunately, the Excel spreadsheet software in the Windows program of
everyone's computer offers a calculator for determining how well you
have been doing with your 401(k) investments. ... 'IRR' is one of the
options the computer will calculate. You start by lining up the
contribution amounts (as negative numbers) on an Excel spreadsheet in
a row of cells for the years you have been a participant. In the last
cell, insert as a positive number the total value of your account as
of the end of [the period]. Move to an empty cell, go to the "paste
function" icon and click 'IRR.' Enter the line of cells for which you
want the calculation performed (i.e. A1:L1) and click 'OK.'"
You're Smarter Than You Think
By Stephen J. Butler
http://www.buyandhold.com/bh/en/retirement/retirement_perspectives/article_0046.html
So if you were to adapt Mr. Butler's approach to contributions that
are staggered over time, then instead of his "row of cells for the
years you have been a participant" you would have to set up 365 rows
for each year, put the negative amount of each contribution next to
the corresponding date, and put '0' into each date that had no
contribution. That way the IRR function will give each contribution
the proper weight.
But luckily for you and me, I have located a ready-made Excel
spreadsheet that uses what I'm calling the second method, one that
eliminates the empty cells by weighting each contribution by how long
it has been in the fund:
Internal Rate of Return Calculator
http://www.plandesign.com/401k_IRR_Estimator.XLS
So all you need to do is download ('save as') the above spreadsheet to
your computer and then edit it to match your personal account data.
Google search terms used:
"internal rate" return "401(k)" excel
irr "401 k" filetype:xls
If you find any of this answer unclear, please feel free to Request
Clarification. I would appreciate it if you would hold off on rating
my Answer until I have an opportunity to respond.
Sincerely,
richard-ga |
Request for Answer Clarification by
sfilzen-ga
on
30 Sep 2002 18:14 PDT
Hi Richard,
Thanks for your fast response.
Test droved the 401k_IRR_Estimator spreadsheet and found a problem.
Assuming I am using it correctly, the spreadsheet is unable to
calculate a correct compound rate of return. In my test, started with
a 100,000 balance on 1/1/2000 and finished with a 200,000 balance on
12/31/2005 with no monthly contributions. The speadsheet calculated
the return to be 100%. The correct result is 15% compounded annually.
Please investigate and advise.
Glenn
|
Clarification of Answer by
richard-ga
on
30 Sep 2002 19:08 PDT
Hello again:
Apparently the person who created that spreadsheet was thinking of a
one-year investment. But it's easy to fix.
Put this formula in the IRR answer box in place of what's there:
=RATE(B7/365,0,C53,-C54)
If you enter 0 for each contribution and withdrawal, a fund that goes
from 100,000 to 200,000 over the six-year period shows an interest
rate of 12.24% per annum.
That's the correct answer, because
(1.1224)*(1.1224)*(1.1224)*(1.1224)*(1.1224)*(1.1224)= 2.00
Let me know if you have any problem with this
richard-ga
|
