IRR & XIRR - Different Results

[Alex Morgan]

I have calculated the IRR & XIRR for the following cashflows and would expect
the results to be the same, as the cashflows are for constant periods.

As the formulas give different answers I checked the results using Solver
NPV=0 which gives a rate which agrees to the IRR result.

I would expect the XIRR to be more accurate as this includes the dates of
the cashflows unlike IRR, but am unsure if that is the case.

31/05/2005 -9,375,000
30/06/2005 -
31/07/2005 -750,000
31/08/2005 -
30/09/2005 -2,438,881
31/10/2005 -9,349,619
30/11/2005 106,250
31/12/2005 -8,147,500
31/01/2006 -5,893,750
28/02/2006 3,106,250
31/03/2006 181,128
30/04/2006 3,256,200
31/05/2006 -9,011,402
30/06/2006 2,386,293
31/07/2006 2,760,688
31/08/2006 -1,846,470
30/09/2006 -6,268,750
31/10/2006 -7,280,390
30/11/2006 106,250
31/12/2006 3,673,533

IRR -42.308%
XIRR -99.860%

NPV £0.20
rate -42.308%

Formulas:

=IRR(B4:B23,-.4)
=XIRR(B4:B23,A4:A23,-.4)
=NPV(B29,B4:B23)

where B4:B23 = cashflow movements
where A4:A23 = dates
where B29 = rate

Any help to explain why these differences occur would be much appreciated.

Many Thanks

Alex

 

[Dave F]

This is a pretty good analysis of the differences between XIRR and IRR:
http://exceltips.vitalnews.com/Pages/T0521_Using_the_XIRR_Function.html

Essentially, XIRR should be used when income and payments are made at
different periods.

See also: http://office.microsoft.com/en-us/excel/HA011136321033.aspx

Dave

 

[Harlan Grove]

I have calculated the IRR & XIRR for the following cashflows and would expect
the results to be the same, as the cashflows are for constant periods. ....
I would expect the XIRR to be more accurate as this includes the dates of
the cashflows unlike IRR, but am unsure if that is the case.

31/05/2005 -9,375,000
30/06/2005 - ....
IRR -42.308%
XIRR -99.860% ....
Any help to explain why these differences occur would be much appreciated.

In a nutshell, IRR returns a periodic rate, whatever the period is,
while XIRR always returns an effective annual rate. Since your
cashflows are monthly, IRR returns an effective monthly rate. If you
want it on the same basis as XIRR, put IRR's result on an annual basis
using

=(1+IRR(...))^12-1

which also returns -99.86%.

 

[joeu2004]

I have calculated the IRR & XIRR for the following cashflows and would expect
the results to be the same, as the cashflows are for constant periods.
[....]
I would expect the XIRR to be more accurate as this includes the dates of
the cashflows unlike IRR, but am unsure if that is the case.
[....]
Any help to explain why these differences occur would be much appreciated.

As others have noted, the reason for the big difference is because
Excel's IRR() always gives a period rate (monthly, in your case),
whereas Excel's XIRR() always gives an annualized rate.

You can annualize a monthly IRR in one of two ways: simply
12*IRR(...); or (1 + IRR(...))^12 - 1. The latter will be closer to
the XIRR result; and that is the method that I prefer. But many
people -- academicians and practitioners alike -- simply do the former
(multiply by 12).

But even if the regular periods were annual, IRR and XIRR would have
slightly different results because, as you say, XIRR takes the actual
number of days into account. Whether or not XIRR is "more accurate"
depends on your point of view. In my view, an answer is "accurate" if
it agrees with "everyone else's", no matter how IRR is computed (with
a calculator, for example). Most people rely on an IRR-like
computation for "regular" intervals. For example, Jan-to-Jan periods
are usually considered to be "regular", whether or not they include
leap years. (Note that if you use XIRR, there is no such thing as a
"regular" interval, unless it is daily.)

 

[joeu2004]

PS....

Whether or not XIRR is "more accurate" depends on your point of view.
[....]
Most people rely on an IRR-like computation for "regular" intervals.

But of course, always use XIRR for truly irregular intervals. And if
you use XIRR, the corresponding net present value function is XNPV(),
not NPV().

 

[Harlan Grove]

You can annualize a monthly IRR in one of two ways: simply
12*IRR(...); or (1 + IRR(...))^12 - 1. The latter will be closer to
the XIRR result; and that is the method that I prefer. But many
people -- academicians and practitioners alike -- simply do the former
(multiply by 12).

Misleading and wrong. XIRR's result is always an annual EFFECTIVE
rate. The only way to convert a monthly effective IRR rate to an
annual EFFECTIVE rate is by using the (1+IRR(...))^12-1 formula.
Simply multiplying by 12 gets you the nominal annual rate compounded
monthly. Maybe there are practitioners and academics who can't tell an
effective rate from a nominal rate (or a hole in the ground or their
backside), but they won't match XIRR's result by multiplying IRR's
result by 12.

But even if the regular periods were annual, IRR and XIRR would have
slightly different results because, as you say, XIRR takes the actual
number of days into account. . . .

....

The difference should be well less than a single basis point when
there's more than 12 months, especially when the first and last
Februaries are 3 or more months from the beginning and end of the
cashflow. Negligible compared to confusing nominal and effective rates.

 

[joeu2004]

Misleading and wrong.

I do not consider any part of my response wrong or misleading. It is
a fact that many, if not most, academicians simply multiply by 12.
They make no distinction between "nominal" and "effective" IRR. They
also simply divide an annual IRR by 12 (to get a monthly rate) without
qualification (e.g. "only if that is the nominal rate"). Don't shoot
the messenger! Frankly, I am shocked and dismayed. You and I are in
"violent agreement" that that is wrong. I was trying to be polite by
stating my "preference". In fact, I believe I posted a very long
article in these newsgroups about why an "effective" IRR is the only
reasonable number to use for IRR. An IRR's only purpose in life is
for use in compound calculations.

 

[ryanpoth]

It is a fact that many, if not most, academicians simply multiply by 12.
They make no distinction between "nominal" and "effective" IRR. They
also simply divide an annual IRR by 12 (to get a monthly rate) without
qualification (e.g. "only if that is the nominal rate").

Which goes a long way toward explaining why they are in academia and
not business

 

[joeu2004]

Which goes a long way toward explaining why they are in academia and
not business

Point well taken. But what disappoints me is that I see this "12*IRR"
formula in texts that are intended to teach MBAs how to make these
computations and do the analysis. In other words, this is what CFAs
learn :-(.

Having said that, I should say that most CFAs simply rely on
professional software to do the computation, and I really have no idea
how such software annualizes IRRs. But if they use a financial
calculator, they are on their own to perform the annualization. They
will probably do it the way they were taught.

Enough said! The point is: "12*IRR" is not uncommon, and I wanted to
bring it to the OP's attention so that he/she was aware of the
potential for differences in methodology.

 

[Dave F]

CFAs....ughhh.

In the middle of that program right now.

Dave

 

[Alex Morgan]

Many thanks for all your comments, think you've all answered my questions.

My intention was not to cause arguments!!