XIRR vs. IRR

[Jen]

I would like to know which function is more accurate to
use when calculating the IRR for quarterly cash flows. I
know XIRR is to be used for irregular cash flow periods,
but I also heard that it is more accurate. When I compare
the quarterly cash flow result calculated using IRR and
multiplied by 4 to annualize the result, I get a different
number (by about 100 basis points) than if I use the XIRR.
Also, to make sure, both IRR and XIRR compound on an
annual basis correct?

 

[Harlan Grove]

I would like to know which function is more accurate to
use when calculating the IRR for quarterly cash flows. I
know XIRR is to be used for irregular cash flow periods,
but I also heard that it is more accurate. When I compare
the quarterly cash flow result calculated using IRR and
multiplied by 4 to annualize the result, I get a different
number (by about 100 basis points) than if I use the XIRR.
Also, to make sure, both IRR and XIRR compound on an
annual basis correct?

XIRR must use some approximations in order to deal with dates, so if your
cashflows are all quarterly, you'd be better off using IRR to calculate the
quarterly IRR, then converting the result to an annual IRR.

 

[Guest]

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

XIRR must use some approximations in order to deal with dates, so if your
cashflows are all quarterly, you'd be better off using IRR to calculate the
quarterly IRR, then converting the result to an annual IRR.

Is there a more accurate way of annualizing the quarterly
IRR other than multiplying by 4? Thank you.

 

[Harlan Grove]

...
...

Is there a more accurate way of annualizing the quarterly
IRR other than multiplying by 4? Thank you.

You're not annualizing a quarterly IRR by multiplying it by 4. You're converting
it to a nominal annual rate with quarterly compounding. The only (not best,
*ONLY*) way to convert a quarterly IRR, which is an *effective* interest rate,
into an annual IRR, also an *effective* interest rate, is to use the formula

AnnualIRR = (1 + QuarterlyIRR) ^ 4 - 1

 

[Fred Smith]

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

 

[Guest]

Fred,

Pardon my continued confusion. 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.

 

[Fred Smith]

Here's some information which will help you:

1. XIRR returns the effective annual rate. The function can calculate this,
because you give it actual dates, so it has enough information to calculate
the annual rate.

2. IRR (and all other financial functions, for that matter) deal with the
*periodic* interest rate. IRR has no idea whether the values you are passing
it are monthly, quarterly, annually or any other period. All financial
functions (except XIRR) assume the interest and payment frequency are the
same. The interest rate is for the payment period.

3. Things get complicated when the payment period and the compounding
frequency are different. In this case you have to calculate an effective
rate for the payment period, because all financial functions (except XIRR)
need the payment and compounding period to be the same. For example, suppose
the interest rate is 8% compounded annually, but your payments are
quarterly. Now you need to know "What interest rate, compounded quarterly,
will turn $1 into $1.08 in a year?" You can use Excel's Rate function, as in
RATE(4,0,-1,1.08) to get the answer 1.94%.

4. When dealing with different payment and compounding periods you can
*never* simply divide or multiply by the number of periods (eg 8%/4 = 2),
because that ignores the compounding effect.

5. The financial functions simply calculate a rate. You need to know whether
it's effective or nominal. In your case, I suspect IRR will calculate the
effective quarterly rate (eg, 1.94%). You need to turn this into a nominal
annual rate, and the most effective (sorry for the pun) way to do this is
the NOMINAL function.

6. To help you out with why IRR and XIRR are returning "vastly different
numbers" we would need to see what data you are using. Post an example, and
you'll get a response.