On Oct 24, 12:42 pm, Dave wrote:
Have you tried XIRR()?

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".

Previously you wrote:
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.