how do I calculate my 401K return?

October 10, 2006 11:20 AM

I've been scratching my head this whole morning, trying to figure out how my 401K is doing, my own account per se. The personalized rate of return on my statement takes into consideration other factors that don't really apply to me, and thus I think it's not a true indicator of my account performance.
I tried to find an MS Excel function to do this; so far, I have not had any luck. What I want to find out is the average return per week or per quarter given these known numbers on my statement:

1. a beginning balance in dollars (B)
2. an ending balance (E)
3. number of weeks contributed in this quarter (N)
4. weekly contribution amount (W)
5. total quarterly investment additions (W x N)

FYI, the contribution is made on a weekly basis for a same amount. It's 100% vested and is invested in one plan. What is the formula to calculate this weekly return? Thanks a lot.
posted by dy to work & money (4 comments total)

A Rate of Return function should be based expressely on the numbers themselves, and not outside factors.

RoR is ridiculously difficult to calculate. The common method (which goes by the name of "Newton's Method") of calculating it is to make a guess, run it through some calculations, and come up with a difference between that guess and the actual performance. You then narrow the guess down, and recurse that method until you come within a level of precision with which you're comfortable. On the financial apps that I've developed, the common level of comfort precision is 1/10000th of a percentage.

Here's what my resident accountant gave me when I was originally setting up the system:

Let:
* BegBal = the beginning balance. BegBal is a positive number.

* BegDate = the date of BegBal

* C1, C2, ..., Cn = the cash flows. Cash flows include financial transactions such as contributions, withdrawals, loan payments, and loans. Transactions that increase the investment balance (for example, contributions and loan payments) are positive. Transactions that decrease it (for example, withdrawals and loans) are negative.

* d1, d2, ..., dn = the dates of the cash flows

* EndBal = the ending balance. EndBal is a positive number. EndBal equals BegBal plus (or minus) cash flows C1, C2, ..., Cn plus investment gains and/or losses.

* EndDate = the date of EndBal

Then, the rate is changed successively until the two sides of the following equation are equal (within 0.00000001).

EndBal/(1+rate)^(EndDate-BegDate) = BegBal + sum(C(i)/(1+rate)(d(i)-BegDate)

The expression (EndDate - BegDate) is the number of days between the ending and beginning balances. The expression (d(i) - BegDate) is the number of days between the ith cash flow and the beginning balance.

This process solves for the compound daily rate of return, which is the dailyrate. Our final step is to compute actualrate, which is the account performance during the investment period selected.

Let:
days = the number of days in the investment period,
where days = (EndDate - BegDate)

Then:
Actualrate = (1+dailyrate)^days - 1 [ the exponent is days, not days - 1]
posted by thanotopsis at 12:14 PM on October 10, 2006

What you are probably looking for, as a "true" measure of your investment's performance, is an IRR calculation. If you want to evaluate the projected performance of the investment into the future, you bring back projected future investments (negative flows) and returns (positive flows) to today's dollars, using an NPV calculation. Whatever investment alternative offers you the higher NPV for equivalent risk, all other considerations being equal, is the preferable investment.
posted by paulsc at 12:14 PM on October 10, 2006

Are you certain that your broker's calculation is somehow wrong? What reason do you have for thinking so? Do you think it is too low, or too high?

Your question suggests a bit of confusion between contributions and returns. Your contributions have nothing to do with the returns you get, except that they cloud the issue and make calculating returns more difficult.

The industry uses a formula called "Modified Dietz" (<-- Google me) to calculate individual rates of return. You can also use it, but if there is activity in your account (such as weekly contributions), you may not have enough information on your statement to correctly calculate the IRR for your account.
posted by jellicle at 12:18 PM on October 10, 2006

The calculation thanotopsis describes is built into Excel. Do a search for "XIRR" on the AskMeFi page for similar conversations. Also search the Help in Excel for the XIRR function. To use XIRR you will probably need to install the Analysis Toolkit package from your Excel install disk as described in Help.

What I describe below is a bit confusing to put into words, but this link provides a good example at the bottom of the page.

Set up two columns with the date in the first and the dollar amount in the second. Your first entry should be the date and balance at the end of the previous quarter. Add additional entries with the date and amount of each contribution in the current quarter. At the end of the quarter, put an entry with the date and the current balance as a negative number. This is because XIRR calculates your investment return as if you withdrew all the money at the end. Now calculate the XIRR over this range, starting with the old date and balance and ending with the new date and negative balance.

Note that XIRR gives the annualized return. In other words, it calculates your return over the selected period and extrapolates it as if you earned the same rate for an entire year. This number will be much larger than your actual return for just one quarter. To convert the XIRR result to a quarterly result, just raise the resulting percentage (plus 1) to the power of the fraction of a year (then subtract 1 to get percent again). In this case you would raise the XIRR result to the 1/4 power. It is even better to have Excel compute the fraction of a year by subtracting the starting date from the ending date and dividing by 365 (or 365.25 by some people's reckoning to account for leap years). That way you can compute the return over any arbitrary timespan.

To start the next quarter, put an entry right below your last, negative, entry that is the same date but with a positive value. This entry cancels out the previous negative entry. So it is as if you withdrew all the money on the last day of the quarter, calculated the return, then deposited the same amount at the end of the day.

To do a 6-month return, just change the range of the XIRR function to span from the beginning balance to the 6-month ending balance. This will span across your quarterly negative and postive balances, but since they are equal and opposite on the same day, it is as if they weren't there.
posted by JackFlash at 1:28 PM on October 10, 2006 [1 favorite has favorites]