MORE XIRR vs. IRR help

[Guest]

I had posted this yesterday and did not recieve a
response - I included prior questions for reference...

What I'm trying to do is
use the most accurate formula to calculate the return for
a series of quarterly payments that are being compounded
annually. Why do I get such vastly different numbers when
i calculate the IRR and annualize it vs. just the regular
xirr? Does the XIRR compound in every period?

Also does the IRR and XIRR formula give the effective or
nominal result? Thank you very much.

-----Original Message-----
Suppose IRR returned a (quarterly) rate of 2%. When you try to multiply by
four to get the annual rate, you get 8%, which would turn $100 into $108 in
a year.

But if you invest $100 at 2% quarterly, you get more than $108 after a year,
because of the compounding effect. You actually get 100* (1.02)^4 or $108.24.
Therefore the effective annual interest rate is 8.24%.

This, by the way, should be the same as XIRR, which automatically calculates
the annual interest rate. It won't be exact, because IRR would assume all
deposits are made 91.25 days apart, which of course couldn't be the case
with XIRR. However, XIRR and IRR should be within 5 bps of each other. If
not, then your dates are probably out.

There are several ways to convert from a nominal (eg, quarterly) to an
effective rate (eg, annual). Harlan gave you one. The EFFECT function is
another. I like to use the FV function because it helps me think through
"how much money would I have after a year if I invested a dollar at this
rate?"

--
Regards,
Fred
Please reply to newsgroup, not e-mail





.

..

 

[Harlan Grove]

I had posted this yesterday and did not recieve a response . . .

...

You did receive many responses, but maybe they're all over your head, or you
don't really mean what you wrote above.

If your cashflows are regularly spaced, then IRR is likely to be more accurate
than XIRR. If the cashflows were quarterly, IRR will return an effective (i.e.,
compound interest) *QUARTERLY* rate, but XIRR *ALWAYS* returns an effective
*ANNUAL* rate. If the resulting rates are 'far' from zero in either direction,
then attempting to annualize the IRR result by multiplying it by 4 *SHOULD*
produce a resulting rate that isn't close to the XIRR result because 4 times an
effective quarterly rate *IS* *NOT* an effective annual rate.

YOU NEED TO LEARN THIS!

If the correctly annualized IRR result and the XIRR result are still far apart,
then are there multiple sign changes in your cashflows? If there are, then there
are likely to be multiple IRR/XIRR solutions (IRR/XIRR rates are just zeros of
polynomials). It's not inconceivable that in such circumstances that IRR and
XIRR could return different rates from multiple, equally valid results. The only
way to confirm this is using the resulting rates along with the cashflows in NPV
for IRR and XNPV for XIRR. If both NPV/XNPV results are zero, then you have two
distinct but equally valid IRR rates. The economically and mathematically obtuse
may offer 'guidance' for selecting between them, but either would be as valid
and meaningful as the other.

 

[Guest]

Thanks. I get it now.

-----Original Message-----
... response . . .
...

You did receive many responses, but maybe they're all over your head, or you
don't really mean what you wrote above.

If your cashflows are regularly spaced, then IRR is likely to be more accurate
than XIRR. If the cashflows were quarterly, IRR will return an effective (i.e.,
compound interest) *QUARTERLY* rate, but XIRR *ALWAYS* returns an effective
*ANNUAL* rate. If the resulting rates are 'far' from zero in either direction,
then attempting to annualize the IRR result by multiplying it by 4 *SHOULD*
produce a resulting rate that isn't close to the XIRR result because 4 times an
effective quarterly rate *IS* *NOT* an effective annual rate.

YOU NEED TO LEARN THIS!

If the correctly annualized IRR result and the XIRR result are still far apart,
then are there multiple sign changes in your cashflows? If there are, then there
are likely to be multiple IRR/XIRR solutions (IRR/XIRR rates are just zeros of
polynomials). It's not inconceivable that in such circumstances that IRR and
XIRR could return different rates from multiple, equally valid results. The only
way to confirm this is using the resulting rates along with the cashflows in NPV
for IRR and XNPV for XIRR. If both NPV/XNPV results are zero, then you have two
distinct but equally valid IRR rates. The economically and mathematically obtuse
may offer 'guidance' for selecting between them, but either would be as valid
and meaningful as the other.