Better Understanding of XIRR

[W]

I occasionally see an analysis of returns where someone will take the
present value of the future cash flows, and then apply XIRR to those present
values. Since the present value already discounts the cash flow according
to the discount rate, would XIRR in this case simply be a measurement of the
rate of return on money that is already growing at the discount rate?
Using a real example, if XIRR measures 15% IRR after three years on a cash
flow of present values that were each calculated using a 12% discount, then
is XIRR calculating 15% additional growth applied to 12% growth?

 

[BillyBob]

Is there a reason you posted this in a newsgroup dedicated to MS Excel
programming?

I would recommend calling you accountant.

 

[W]

Is there a reason you posted this in a newsgroup dedicated to MS Excel
programming?

XIRR is an Excel function, is it not?

I would recommend calling you accountant.

You would recommend that I call an accountant for help understanding the
meaning of an Excel function?

 

[joeu2004]

XIRR is an Excel function, is it not?

Then wouldn't it make more sense to use m.p.e.worksheet.functions NG?

No matter. Is was a silly comment. Yours __is__ a question about
"Excel programming" insofar as writing formulas in Excel is a form of
programming.

I 'spose BB meant to say "dedicated to VBA programming". That was the
intent of m.p.e.programming a long time ago. But sadly, this NG
became "undedicated" years ago as more and more naive Excel users
started posting Excel-specific questions just about anywhere.

You would recommend that I call an accountant for help understanding the
meaning of an Excel function?

Another silly comment, unless one of your close friends is an
accountant. Other than a friend, no accountant is answer an academic
question.

And in fact, even an accountant might not be able to answer your
questions. You need someone who is well-versed in financial math. My
father was a CPA for 40+ years, and he could not explain IRR.

 

[joeu2004]

I occasionally see an analysis of returns where someone will take the
present value of the future cash flows, and then apply XIRR to those present
values.

Your question is hard to answer since you neglect to say exactly
__how__ those people "apply" the IRR to "those" present values.

Before we get into this too far, let me clarify some terminology. IRR
and MIRR are financial math terms. XIRR is not a financial math
term; there is no such concept.

IRR(), XIRR() and MIRR() are Excel functions that return __an__ IRR.
Each calculates __an__ IRR differently.

My point is: when I use IRR without parentheses, I am referring to
the financial math term. When I use IRR() with parentheses, I am
referring to the Excel function. Generally, when I use the math term
IRR, I am not making a distinction among the various ways it can be
computed.

Similarly for NPV v. NPV() and XNPV(), and PV and FV v. PV() and FV().

Using a real example, if XIRR measures 15% IRR after three years on a cash
flow of present values that were each calculated using a 12% discount

Non sequitur. The IRR __is__ the discount rate for calculating the
present value of the cash flows.

More specifically, the IRR is the discount rate that causes the sum of
the present value of the cash flows (NPV) to equal zero.

The IRR can be interpreted many ways, depending on context. It is not
even limited to financial problems. But I think the context you have
in mind is: the IRR is the average growth rate of an investment.

Use the RATE() function to calculate periodic IRR when there are only
two cash flows (PV and FV) or when the other cash flows ("payment")
are equal and at regular intervals.

Use the IRR() function when the other cash flows are unequal, but at
regular intervals.

Use the XIRR() function when the other cash flows are unequal and at
irregular intervals, or when you want to calculate an annualized IRR
instead of a periodic IRR. But note that the XIRR() result usually is
not equal to the annualized IRR based on the IRR() or RATE() function.

is XIRR calculating 15% additional growth applied to 12% growth?

No. In the context of my example above, the IRR __is__ the average
growth rate for the time frame of the cash flows.

Just to confuse things, you might want to study the MIRR -- both the
financial math term and the Excel MIRR() function. To be honest, I do
not know anything about the MIRR. It has never been useful to me.

But the MIRR does incorporate two different rates, one for negative
cash flows (cost of borrowing) and one for positive cash flows
(investment opportunity). I wonder if that may be the source of your
confusion.

As I understand the MIRR (vaguely), it might be useful for comparing
alternative methods of borrowing and investing. But I do not think
the MIRR number itself has any meaning in the real world -- unlike the
IRR, which is indeed an average growth rate.