XIRR

[Steven]

Help! I'm using the XIRR function for a series of
cashflows. It appears that instead of calculating IRR
back to the earliest cash flow date, it is calculating to
the latest cash flow date. I have no idea why this is
happening when the earliest date is the first one in the
list.

Helppppp!

Thanks,
Frusterated in Toronto

 

[Kevin Stecyk]

Steven,

My guess is that it is performing correctly. Can you give us your XIRR
input data and why you think it is incorrect?

Regards,
Kevin

 

[Steven]

sure - here it is - it comes to a -2.74 but when I change
the cash inflow dates (make them later) i would expect the
irr to decrease and it is increasing..

November 1, 1999 (156,956.00)
September 20, 2000 (2,000,000.00)
July 6, 2001 (2,000,000.00)
June 30, 2002 (2,000,000.00)
August 15, 2000 (1,726,519.00)
September 26, 2001 (1,612,099.00)
November 8, 2000 (1,500,001.00)
October 2, 2002 (1,500,000.00)
September 15, 2001 (1,115,000.00)
December 6, 2000 (1,000,015.00)
July 31, 2000 (1,000,000.00)
January 26, 2001 (1,000,000.00)
March 2, 2002 (1,000,000.00)
December 31, 2000 (750,000.00)
June 15, 2001 (750,000.00)
August 31, 2001 (750,000.00)
June 30, 2000 (675,675.68)
September 27, 2000 (665,114.73)
December 31, 2000 (500,000.00)
March 15, 2001 (500,000.00)
May 3, 2000 (470,869.00)
March 15, 2000 (450,000.00)
March 3, 2000 (375,000.00)
May 1, 2000 (337,837.84)
June 15, 2002 (315,000.00)
September 30, 2000 (250,000.00)
September 30, 2000 (250,000.00)
November 15, 2003 87,969.92
July 31, 2003 145,556.43
October 15, 2001 479,733.17
November 15, 2004 3,056,405.00
May 15, 2003 19,393,391.00

 

[Kevin Stecyk]

Hi Steven,

Good news and bad news. Good news is that I believe I found your problem.
Bad news is you have more work ahead of you.

I sorted your data so it is easier to examine. It will help with my
explanation in a second too.

01-Nov-1999 -156,956.00
03-Mar-2000 -375,000.00
15-Mar-2000 -450,000.00
01-May-2000 -337,837.84
03-May-2000 -470,869.00
30-Jun-2000 -675,675.68
31-Jul-2000 -1,000,000.00
15-Aug-2000 -1,726,519.00
20-Sep-2000 -2,000,000.00
27-Sep-2000 -665,114.73
30-Sep-2000 -250,000.00
30-Sep-2000 -250,000.00
08-Nov-2000 -1,500,001.00
06-Dec-2000 -1,000,015.00
31-Dec-2000 -750,000.00
31-Dec-2000 -500,000.00
26-Jan-2001 -1,000,000.00
15-Mar-2001 -500,000.00
15-Jun-2001 -750,000.00
06-Jul-2001 -2,000,000.00
31-Aug-2001 -750,000.00
15-Sep-2001 -1,115,000.00
26-Sep-2001 -1,612,099.00
15-Oct-2001 479,733.17
02-Mar-2002 -1,000,000.00
15-Jun-2002 -315,000.00
30-Jun-2002 -2,000,000.00
02-Oct-2002 -1,500,000.00
15-May-2003 19,393,391.00
31-Jul-2003 145,556.43
15-Nov-2003 87,969.92
15-Nov-2004 3,056,405.00

Total -1,487,031.73

XIRR -2.74%





Look at 15-Oct-2001. Notice that there is a change of sign before it and
after it? Well for every change in sign, you have another potential root or
IRR (XIRR) value. So you from neg to pos to neg to pos, so you have 3
changes in sign. What IRR and XIRR are effectively doing is solving a
polynomial equation (through trial and error). Descartes Rule (search the
net if interested) states that for every change in sign, you have at least
one root. So you have at least 3 potential answers, and I wouldn't
necessarily trust the -2.74% you got originally. It might or might not be
correct. What worse is that some of these roots might contain imaginary
numbers. By an imaginary number, I mean numbers similar to the answer of
square root of negative 1.

Normally when you have a postive IRR (or XIRR), I would suggest creating a
data table of NPV values using varying discount rates (say 0-50% in 0.25%
increments or something like that). Then look to where NPV=0. You have
your different "real" positive (as opposed to imaginery or negative values)
roots or IRR values. But in your case, you have a negative IRR. I don't
believe XNPV accepts negative rate values.

Here's something interesting. Multiply your cash values by -1 so that your
total is now positive 1,487,031.73. And use XIRR(values, dates, -10%).
Surprisingly you get -2.74% (no change!). If I change to XIRR (values,
dates, 5%), I get 2.98E-09 or very nearly 0%.

I am not sure how you want to attempt to tackle your question. It is
conceivable that there is no correct IRR value because the true answer is an
imaginary answer and all the rest of the roots are simply bogus values. If
I had this problem (luckily I don't), I might be inclined to change the
15-Oct-2001 to a negative value (say -100,000) and then add
(479,733.17+100,000) to the 15 May 2003 value. Although I am no longer
correct, I have eliminated some changes in sign (and thus some roots).

One last word of caution, I am not exactly sure of how XIRR in Excel works.
Is the very first value 0 internally? So if you make the change I proposed
in the prior paragraph, do you have 0 followed by a bunch of negative
values and then some positive values? I don't know.

I didn't solve your problem, but at least I was able to explain your problem
and why it exists.

Regards,
Kevin

 

[Harlan Grove]

...
...

. . . What IRR and XIRR are effectively doing is solving a polynomial
equation (through trial and error). . . .

Not really trial & error. XIRR uses iterative techniques that converge except
for pathological cashflows. Using an initial guess of -50% (yes, *negative* 50%)
usually forces [X]IRR to give numeric results.

. . . Descartes Rule (...) states that for every change in sign, you have at
least one root. . . .

All polynomials of Nth order have N roots, some possibly complex, some possibly
nonunique. Descartes's "vrayes racines" translates as *true* roots, which must
be further translated into current terminology as positive real roots.

Here's something interesting. Multiply your cash values by -1 so that your
total is now positive 1,487,031.73. And use XIRR(values, dates, -10%).
Surprisingly you get -2.74% (no change!). If I change to XIRR (values,
dates, 5%), I get 2.98E-09 or very nearly 0%.

If you try to give undeserved meaning to the [X]IRR result, this may seem
interesting. Mathematically, it's rather obvious. As you state, the [X]IRR is
the solution of a polynomial, but it's a polynomial of the form

a[n] x^n + a[n-1] x^(n-1) + . . . + a[1] x + a[0] = 0

Negating both sides of the equation gives

-a[n] x^n - a[n-1] x^(n-1) - . . . - a[1] x - a[0] = 0

Obviously doing this would have no affect on the value of x.

I am not sure how you want to attempt to tackle your question. It is
conceivable that there is no correct IRR value because the true answer is an
imaginary answer and all the rest of the roots are simply bogus values. . . .

*NO*, *NO*, *NO*!!!

There is no [X]IRR if there are no sign changes. That there would be complex
roots for the polynomial is irrelevant - [X]IRR should be an interest rate, and
interest rates *MUST* be real valued.

If there are any sign changes, there are real roots. And if there are real
roots, all of them are *EQUALLY* valid (despite the fact that there are snake
oil salesmen disguised as economics PhDs who claim otherwise).

. . . If I had this problem (luckily I don't), I might be inclined to change
the 15-Oct-2001 to a negative value (say -100,000) and then add
(479,733.17+100,000) to the 15 May 2003 value. Although I am no longer
correct, I have eliminated some changes in sign (and thus some roots).

...

Gee, why not take this to it's logical extreme and dump all the values into just
two dates? That'd make the calculations elementary, and even eliminate the need
for the IRR function. Of course, the result would have lost even the tenuous
connection to anything meaningful that IRR possesses, but WTH.

 

[Kevin Stecyk]

Harlan,

You raise great points. Let's go through them.

<<Not really trial & error. XIRR uses iterative techniques that converge
except
for pathological cashflows. Using an initial guess of -50% (yes, *negative*
50%)
usually forces [X]IRR to give numeric results.>>

Yes, better stated, thank you.



<< Negating both sides of the equation gives

-a[n] x^n - a[n-1] x^(n-1) - . . . - a[1] x - a[0] = 0

Obviously doing this would have no affect on the value of x.>>


Very true.

<< If there are any sign changes, there are real roots. And if there are
real
roots, all of them are *EQUALLY* valid (despite the fact that there are
snake
oil salesmen disguised as economics PhDs who claim otherwise).>>

Mathematically valid yes, but still incorrect for our purposes.

I am too lazy to get my finance texts out to dig up an example. But
sometimes you simply must disregard a real root of the equation.

BTW, not interesting in debating this one. Believe what you will.

<< Gee, why not take this to it's logical extreme and dump all the values
into just
two dates? That'd make the calculations elementary, and even eliminate the
need
for the IRR function. Of course, the result would have lost even the
tenuous
connection to anything meaningful that IRR possesses, but WTH.>>

Because that would be just plain stupid.

Regards,
Kevin

...
..

. . . What IRR and XIRR are effectively doing is solving a polynomial
equation (through trial and error). . . .

Not really trial & error. XIRR uses iterative techniques that converge except
for pathological cashflows. Using an initial guess of -50% (yes, *negative* 50%)
usually forces [X]IRR to give numeric results.

. . . Descartes Rule (...) states that for every change in sign, you have at
least one root. . . .

All polynomials of Nth order have N roots, some possibly complex, some possibly
nonunique. Descartes's "vrayes racines" translates as *true* roots, which must
be further translated into current terminology as positive real roots.

Here's something interesting. Multiply your cash values by -1 so that your
total is now positive 1,487,031.73. And use XIRR(values, dates, -10%).
Surprisingly you get -2.74% (no change!). If I change to XIRR (values,
dates, 5%), I get 2.98E-09 or very nearly 0%.

If you try to give undeserved meaning to the [X]IRR result, this may seem
interesting. Mathematically, it's rather obvious. As you state, the [X]IRR is
the solution of a polynomial, but it's a polynomial of the form

a[n] x^n + a[n-1] x^(n-1) + . . . + a[1] x + a[0] = 0

Negating both sides of the equation gives

-a[n] x^n - a[n-1] x^(n-1) - . . . - a[1] x - a[0] = 0

Obviously doing this would have no affect on the value of x.

I am not sure how you want to attempt to tackle your question. It is
conceivable that there is no correct IRR value because the true answer is an
imaginary answer and all the rest of the roots are simply bogus values. .

.. .

*NO*, *NO*, *NO*!!!

There is no [X]IRR if there are no sign changes. That there would be complex
roots for the polynomial is irrelevant - [X]IRR should be an interest rate, and
interest rates *MUST* be real valued.

If there are any sign changes, there are real roots. And if there are real
roots, all of them are *EQUALLY* valid (despite the fact that there are snake
oil salesmen disguised as economics PhDs who claim otherwise).

. . . If I had this problem (luckily I don't), I might be inclined to change
the 15-Oct-2001 to a negative value (say -100,000) and then add
(479,733.17+100,000) to the 15 May 2003 value. Although I am no longer
correct, I have eliminated some changes in sign (and thus some roots).

..

Gee, why not take this to it's logical extreme and dump all the values into just
two dates? That'd make the calculations elementary, and even eliminate the need
for the IRR function. Of course, the result would have lost even the tenuous
connection to anything meaningful that IRR possesses, but WTH.

--
Never attach files.
Snip unnecessary quoted text.
Never multipost (though crossposting is usually OK).
Don't change subject lines because it corrupts Google newsgroup archives.

 

[Kevin Stecyk]

Stephen,

Allow me to provide you with a simple example of why you sometimes need to
disregard the real roots of an equation.

Let's say you these values

-10
+150
-145
80
-250

For a total of negative 175 (= -175).

If you use IRR you will find that there are two answers: 50.12% and
1299.57%. Because the sum of the values is negative, we know that the IRR
must be less than 0.

So we could try plotting NPV using different discount rates. Remember that
when NPV=0 using a discount rate, that discount rate is a root or an IRR
value.

I generated 2000 NPV values ranging from NPV using a discount rate of -1000%
to +1000%. The ONLY two real roots I got were 50.12% and 1299.57%. Neither
is correct.

Now, is it ever correct to move cash flows around?

Depends if you want to be precisely wrong or approximately correct. If you
demand a 100% precision, go with either 50.12% or 1299.57%.

But if you can live with a small error in your final answer, it sometimes
makes sense to see if you can eliminate some of the changes in sign without
disturbing your cash flow stream too much. Obviously, your answer is not
precisely correct. But then again, neither is 50.12% or 1299.57% in this
example.

Hope that helps shed some light on this topic.

Regards,
Kevin

 

[Harlan Grove]

...
...

<<...>>

...

You've got to learn how to respond interspersed and, more importantly, how to
snip. Since you spent so much time copying my comments from my response, why
couldn't you have spent a few seconds more deleting my response rather than
having the quoted bits repeated twice?

<< If there are any sign changes, there are real roots. And if there are real
roots, all of them are *EQUALLY* valid (despite the fact that there are snake
oil salesmen disguised as economics PhDs who claim otherwise).>>

Mathematically valid yes, but still incorrect for our purposes.

You're wrong. Every real solution of the IRR polynomial does exactly what all
the others do - when used as the discount rate, it generates a zero NPV for the
cashflow under discussion.

I am too lazy to get my finance texts out to dig up an example. But
sometimes you simply must disregard a real root of the equation.

Most financial economics texts (certainly Brealey & Myers, 'Principles of
Corporate Finance') dismiss IRR as nearly useless. Those that don't but instead
discuss how to select roots are as important to the subject as biology texts
that discuss dinosaurs in the context of Creationism.

BTW, not interesting in debating this one. Believe what you will.

There's a difference between believing in mathematics on the one hand, and IRR
meaning anything and the existance of the tooth fairy on the other. One set of
beliefs may be of more use than the other in this newsgroup.

<<Gee, why not take this to it's logical extreme and dump all the values
into just two dates? That'd make the calculations elementary, and even
eliminate the need for the IRR function. Of course, the result would have
lost even the tenuous connection to anything meaningful that IRR possesses,
but WTH.>>

Because that would be just plain stupid.

Deliberately screwing up cashflows and expecting any useful to arise from the
result is no less stupid. You're not the first to suggest combining awkward,
opposite sign cashflows that are close in time in order to eliminate sign
changes. Some of these others take the additional step of discounting the
smaller magnitude cashflow by the presumed short-term market interest rate.
However, once this step is assumed to be valid, there's theoretical barrier to
doing this wholesale, combining the current market interest rate present values
of all negative cashflows to time 0 and the corresponding present values of all
positive cashflows to time 1. The result makes it easy to calculate an IRR, but
that IRR isn't applicable to the original cashflow (using it as a discount rate
doesn't give a zero NPV), only to the modified one.

 

[Kevin Stecyk]

Harlan,

I am going to ignore the diatribes, since that doesn't further the
discussion.

<<You're wrong. Every real solution of the IRR polynomial does exactly what
all
the others do - when used as the discount rate, it generates a zero NPV for
the
cashflow under discussion>>

As mentioned, not interested in debating with you. Believe what you will.


<<Most financial economics texts (certainly Brealey & Myers, 'Principles of
Corporate Finance') dismiss IRR as nearly useless.>>

Agree, but that wasn't the question asked.

The rest of your post is not worth responding to.

Regards,
Kevin

 

[Harlan Grove]

Allow me to provide you with a simple example of why you sometimes need to
disregard the real roots of an equation.

Let's say you these values

-10
+150
-145
80
-250

For a total of negative 175 (= -175).

If you use IRR you will find that there are two answers: 50.12% and
1299.57%. Because the sum of the values is negative, we know that the IRR
must be less than 0.

Wrong!

As you pointed out (but apparently didn't understand), changing the signs of all
cashflows leaves the IRR(s) unaffected. So, changing all the signs in the
cashflow above makes the sum positive, thus eviscerating your argument.

You really don't understand IRR. It's no more (and no less) than the discount
rates for which the net present value of the particular cashflow is zero. Since
you have a zero on one side of the equation, the opposite cashflow has exactly
the same IRRs.

Attempting to use other measures of the cashflow (such as the sign of the
terminal cumulative cashflow) to select or interpret the IRR is theoretically
invalid.

So we could try plotting NPV using different discount rates. Remember that
when NPV=0 using a discount rate, that discount rate is a root or an IRR
value.

I generated 2000 NPV values ranging from NPV using a discount rate of -1000%
to +1000%. The ONLY two real roots I got were 50.12% and 1299.57%. Neither
is correct.

Wrong again.

*BOTH* are correct. You're utterly failing to comprehend that IRRs can be
(usually are) correct *AND* utterly meaningless at the same time.

You seem to be trying to equate IRRs to risk-adjusted discount rates. Can't be
done. But by all means keep trying to do so. After all, you should follow your
beliefs even if they lead you off theoretical cliffs. Wouldn't want you to learn
anything that might shake your beliefs, would we?

Now, is it ever correct to move cash flows around?

Depends if you want to be precisely wrong or approximately correct. If you
demand a 100% precision, go with either 50.12% or 1299.57%.

Make that theoretically correct but meaningless (IRR based on actual cashflow)
or completely irrelevant but comforting and giving the spurious appearance of
reasonableness (manipulating cashflows). Most of humanity tends to prefer the
latter to the former, so go for it!

. . . Obviously, your answer is not
precisely correct. But then again, neither is 50.12% or 1299.57% in this
example.

Dead wrong.

Both 50.12% or 1299.57% are correct, but they're meaningless. For that matter,
it may be practically impossible to derive a proper risk-adjusted discount rate
for the cashflow above precisely because of the interspersed sign changes. The
correct economic interpretation of this cashflow is that it's so eratic that no
one in their right mind would want to undertake the project. To the extent that
unusual appearing IRRs tend to flag such projects, they do serve a purpose.

 

[Harlan Grove]

You don't understand that IRRs are meaningless. So by all means spend lots of
time with them!

 

[Kevin Stecyk]

Harlan,

<<Wrong!

As you pointed out (but apparently didn't understand), changing the signs of
all
cashflows leaves the IRR(s) unaffected. So, changing all the signs in the
cashflow above makes the sum positive, thus eviscerating your argument.>>



Subordinate: Boss, that project doesn't make any economic sense. The cash
flows when you add them up are less than 0. In other words, we will suffer
an economic loss.

Boss: Oh, don't worry about that. Just make multiply the profits by -1 to
turn them into losses. And multiply the losses by -1 to turn them into
profits. And then just work your way backwards so that the revenues are
expenses, and expenses are revenue and so on.

Subordinate: But can we do that? I mean, can we just willy nilly change
sign of expenses and revenues, profits and losses?

Boss: Of course, because if you check the IRR, the IRR is the same in both
cases. And not only is the IRR the same, it is whopping huge positive value
of about 50%. Well, actually, there is another root too at 1200%+. But
heck, with a 50% plus return on a project that loses money, how can we go
wrong? We should do as many of these projects as possible!

Subordinate: Oh, okay, now I understand. Profits and losses can be readily
interchanged willy nilly, just so long as the IRR is the same. Can we do
this in our financial statements too?

~~~~~~~~~~~~~~~~~

Harlan, while this has been fun. I am calling it quits here. You can post
and have the last word.

Regards,
Kevin

 

[Harlan Grove]

sure - here it is - it comes to a -2.74 but when I change
the cash inflow dates (make them later) i would expect the
irr to decrease and it is increasing..

...

The result is correct insofar as IRR is ever correct.

Your cashflows start off negative, have only one sign change (at the 19,393,391
inflow on 05/15/2003), and absolute value of the sum of the negatives (the
earlier set of cashflows) exceeds the sum of the positives. You're losing money,
so the negative IRR is correct.

If by 'it is increasing' you mean the IRR is heading toward zero as you delay
the inflows, you have to reverse your intuition when interest rates are
negative. When interest rates are negative, $1 a year in the future is worth
more than $1 today, and $1 two years in the future is worth even more. Indeed,
the further into the future, the greater the present value of a nominal $1 at
that time. For your cashflow, delaying the inflows increases them in present
value. In order for the NPV on the entire cashflow to remain zero, the interest
rate would need to move closer to zero.

 

[Harlan Grove]

...
...

Harlan, while this has been fun. I am calling it quits here. You can post
and have the last word.

Done.

 

[Harlan Grove]

Your cashflows start off negative, have only one sign change . . .

Nope, it has two, but that doesn't alter the fact that present values get bigger
the longer you wait when interest rates are negative.