Which function to calculate a 401k rate of return?

[Guest]

I have a beginning balance and an ending balance, I have the date and amount
of each transaction in this time period. I am trying to calculate the ROI or
rate of return for my investments during this timeframe.

I have found a number of articles on different Excel function, but I have
not been able to get close to what the investment company says is the rate of
return. So the assumption is, I haven't figured it out yet.

Thanks - Dave

 

[Guest]

Have you tried XIRR()?

 

[mdschuetz]

Investment

See if this helps.

In the attachment.

I think I got the Rate of Return right.

(Note: I protected the worksheet, If your deposits are not made at the beginning of the month (if you get paid more than once per month), then you should unprotect the sheet select cell B7, right click, format, protection, uncheck "locked" and save.)

 

[Guest]

Yes that is the one I tried and it appears to overinflate the result.


Is this the best function for this purpose?

Thanks

 

[joeu2004]

Yes that is the one I tried and it appears to overinflate the result.

By how much is it "over-inflated"? Is it just "a little" off (1% or
less)? Or is it "way off" (several percentage points)?

Is this the best function for this purpose?

There is no "best" function or formula. It depends what data you have
as well as what you want to compute. There is no "right" or "wrong";
only "the right tool for the task".

I have a beginning balance and an ending balance, I have the date
and amount of each transaction in this time period.

It is unclear to me what you mean by "this period". Are you referring
to an arbitrary time frame, for example since you started investing in
the 401(k)? Or are you referring to a statement period, for example
for the last month, quarter, half-year, or year?

I am trying to calculate the ROI or rate of return for my
investments during this timeframe.

I have found a number of articles on different Excel function,
but I have not been able to get close to what the investment
company says is the rate of return.

If you are trying to match the APY reported by the custodian of the
account, you need to know what they consider to be the "return on
investment" or "rate of return". There are many different
definitions. Again, there is "right" or "wrong".

If you are talking about the APY reported on a statement, typically it
is not the IRR, taking the timing of contributions into account.
Sometimes it is; but since you say that the results of XIRR() are
"over-inflated" (I'm assuming you mean "way off"), I'll assume a
different approach.

Instead, the APY for a statement period might be computed simply as:
(endingBalance / beginningBalance) ^ t - 1, where beginningBalance and
endingBalance are the balances for the period. "t" is an annualized
time factor, which might be computed in any number of ways. One way:
365 / (endingDate - beginningDate + 1). If that seems "a little"
high, another way is simply 12, 4, 3, 2, or 1 for statements every
month, quarter, third-year, half-year or year respectively.

By the way, some of the formula can be replaced by using the RATE()
function.

Also, it is possible (but unlikely) that the 401(k) statement reports
the simple return for the period, not annualized.

If none of this gets you any closer to the rate of return reported by
the 401(k) custodian, I suggest that you post some real numbers,
including the reported rate of return. Much of the difficulty lies in
intuiting what the 401(k) custodian has chosen to report as the rate
of return. As I said, it varies somewhat.

HTH.

 

[Guest]

Thanks for the response.

I calculated 10%, they calculated 7.5%. Mine is overstated. The ultimate
goal is not to match them, but to compute a rate of return for all of my
investments tied together.

I do have the entries in a spreadsheet, and I want to combine them into one
calculated rate of return. The overall period is 5 years, but that will
expand out as time goes on.

Make sense?

If the XIRR Function is a good approach, I will go with that, but if there
is a more "general" approach or function, I want to calculate in a different
way.

Thanks

Dave

 

[joeu2004]

I calculated 10%, they calculated 7.5%. Mine is overstated. The ultimate
goal is not to match them, but to compute a rate of return for all of my
investments tied together.
[....]
If the XIRR Function is a good approach, I will go with that, but if there
is a more "general" approach or function, I want to calculate in a different
way.

IRR is probably what you want. XIRR() is the Excel function to use
for irregular cash flows at irregular intervals. I would record
contributions as negative, any distributions (probably none) as
positive, and the current FMV or NAV as positive.

I might add that XIRR() provides a compounded rate. That is not
everyone's definition of IRR. (It is mine, however.) That might (or
not) explain the difference between your result and the account
custodian's result.

Just for grins, if the contributions are made at (roughly) regular
intervals, you might try using IRR() multiplied by the number of
contribution periods per year. I wonder if that comes close to the
account custodian's result. I know you said that's not the
objective. But for me, it is comforting to know we are both using the
same numbers, if nothing more.

 

[Fred Smith]

XIRR is the correct function. It should calculate the proper return without any
trouble.

Remember that only cash flows affect the return. Is your problem that you are
including investment transactions in XIRR?

There are two industry standard methods of calculating return on investment. One
is internal rate of return which XIRR uses, the other is Modified Deitz which
the CFA Institute recommends.

Make sure you and "they" are using the same method. If you are, you should be
able to match within at least two decimal places. Common reasons for errors are
that you and "they" are using different investment dates (like you are using the
date you wrote the check, and they are using the date they received it).

 

[mdschuetz]

The calculation for rate of return is simple.

Interest Earned / Total Amount Invested

So If you've earned $1,475.46 in Interest and
you've invested $12,500.00 of your own money.

$1,475.46 / $12,500.00 = .1180368 or 11.80 % Rate of Return

Keep it simple.

Regards,

Marty

 

[Guest]

Thanks to both of you.

I am only entering my contributions? Is the Modifed Deitz method something
that Excel can calculate?

Dave

 

[Fred Smith]

Yes, but it doesn't have a specific function.

You need to calculate the weighted average cash flow, the total return, then
annualize. Google Modified Deitz to get the algorithm.