Too negative XIRR?

[enneirda]

I have two groups of cashflows, that I detail below. The XIRR is going to be negative, as when the cash flows are viewed in their appropriate groups the XIRRs are -3% and -6%. When I try to stream all cash flows for an XIRR, I get 0%, but I want the EXACT NEGATIVE RETURN. It's like Excel can't calculate a greater negative return.
I've had this problem several times in the past, but had a work around methodology consisting of some long manual calculation (I've since lost that spreadsheet).
Any suggestions?
thank you!

CASH FLOWS:
V1 02/21/2006 (100,000.00) Investment
05/23/2006 (265,000.00) Investment
08/30/2006 (250,000.00) Investment
12/11/2006 (65,000.00) Investment
03/20/2007 (93,000.00) Investment
07/11/2007 20,140.00 Investment
11/05/2007 (20,140.00) Investment
11/05/2007 (45,860.00) Investment
02/06/2008 (100,000.00) Investment
07/29/2008 (50,000.00) Investment
10/17/2008 (31,140.00) Investment
11/21/2006 5,100.00 Distribution
03/27/2007 5,500.00 Distribution
07/11/2007 26,500.00 Distribution
10/01/2007 33,800.00 Distribution
11/05/2007 (20,140.00) Distribution
05/14/2008 29,100.00 Distribution
05/31/2012 770,000.00 Distribution XIRR -3.18%

VII 09/27/2007 (150,000.00) Investment
02/22/2008 (180,000.00) Investment
08/07/2008 (155,000.00) Investment
10/21/2008 (128,000.00) Investment
04/03/2009 (40,000.00) Investment
09/18/2009 (155,000.00) Investment
10/14/2011 (35,400.00) Investment
12/04/2008 10,300.00 Distribution
10/14/2011 131,600.00 Distribution
05/31/2012 470,000.00 Distribution XIRR -8.90%

XIRR for VI & VII 0% (NOOOO!)

 

[joeu2004]

I have two groups of cashflows, that I detail below.
The XIRR is going to be negative, as when the cash flows
are viewed in their appropriate groups the XIRRs are -3%
and -6%. When I try to stream all cash flows for an XIRR,
I get 0%

First, I confirm your observations in general. But XIRR returns
about -8.90% (-9%) for the second set, not -6%. A typo?

It might also be worth noting that using the formulas and methods described
below, we get similar results. That somewhat confirms the XIRR results for
the separate sets of cash flows.

(Note that XNPV does not seem to work. IIRC, I discovered that XNPV does
not like negative IRRs, the first parameter.)

Second, some general observations:

1. Generally, the IRR algorithm (referring to the generic term "IRR", not
any Excel function) is unstable when the signs of the sequence of cash flows
change more than once. This is a mathematical issue with the common
iterative methods, not with Excel per se. See
http://en.wikipedia.org/wiki/Internal_rate_of_return.

In your example, the signs change many times when the cash flows are sorted
by date.

2. You have some anomalies in the data. When I eliminate them, they do not
change the results significantly. So they are not significant.
Nonetheless, it would be prudent to correct them. They are:

a. An "investment" of +20,140 on 7/11/2007. Most investments are entered as
negative numbers, as they should be. My correction (assumption): change
to -20,140.

b. A "distribution" of -20,140 on 11/5/2007. Most distributions are entered
as positive numbers, as they should be. Also note that there is an
investment of -20,140 on 11/5/2007; presumably a reinvestment of the
11/5/2007 distribution. My correction (assumption): change the
"distribution" to +20,140.

I've had this problem several times in the past,
but had a work around methodology consisting of some
long manual calculation (I've since lost that spreadsheet).

Suppose the first set of data are entered with dates in B1:B18 and cash
flows in C1:C18 (with the corrections noted above). Then the NPV (generic
term; corresponds to the Excel XNPV function) can be computed with the
following formula (in F2):

=SUMPRODUCT(C1:C18/(1+F1)^((B1:B18-B1)/365))

F1 can contain the formula =XIRR(C1:C18,B1:B18). Or we can derive the
annual IRR (generic term) in F1 using Goal Seek, set up as follows:
Set cell: F2
To value: 0
By changing: F1

(Note: We could use Solver instead of Goal Seek. Sometimes, one works
better than the other.)

It might be interesting to compare the results from XIRR and Goal Seek by
formatting the SUMPRODUCT cells as Number with 18 decimal places and the IRR
cells as Percentage with 14 decimal places. You should see some very small
differences.

Likewise, if the second set of data are entered with dates in B19:B28 and
cash flows in C19:C28, the NPV can be computed as follows (in F20):

=SUMPRODUCT(C19:C28/(1+F19)^((B19:B28-B19)/365))

F19 can contain the formula =XIRR(C19:C28,B19:B28). Or we can derive the
annual IRR in F19 using Goal Seek.

Finally, we can compute the NPV of the combined two sets of data with the
following formula (in H2):

=SUMPRODUCT(C1:C28/(1+H1)^((B1:B28-B1)/365))

And with Goal Seek, we can derive the annual IRR in H1. As you might have
expected, the result is about -5.26%.

So you are correct: Excel XIRR has difficulty with the combined cash flow.
Obviously, it is certainly not as generic a problem as you describe ("It's
like Excel can't calculate a greater negative return") since Excel XIRR does
just fine computing the negative IRRs for the individual sets of cash flows.

I can only speculate what the problem is for Excel XIRR with the combined
cash flows.

But in any case, it is always prudent to test the result of Excel XIRR by
computing the NPV (generic term). Since Excel XNPV is unreliable for
negative IRRs, it would be prudent to use the SUMPRODUCT formula to compute
the NPV.

Nevertheless, when cash flows have multiple sign changes, even Goal Seek
(Solver) might have difficulty finding a reasonable result.

Moreover, it might be more be "realistic" to compute the MIRR. However,
note that the MIRR does not have the same mathematical interpretation that
IRR does. But then again, the mathematical interpretation of IRR is dubious
when there are multiple sign changes.

 

[joeu2004]

1. Generally, the IRR algorithm (referring to the generic term
"IRR", not any Excel function) is unstable when the signs of
the sequence of cash flows change more than once. This is a
mathematical issue with the common iterative methods, not with
Excel per se. See http://en.wikipedia.org/wiki/Internal_rate_of_return.

It is mathematical issue with the NPV curve as we change the IRR, not just
with the iterative methods for estimating IRR. The NPV curve becomes
sinusoidal instead of steadily increasing or decreasing as it approaches
zero, usually asymptotically.

The point I was trying to make is: the sinusoidal nature of the NPV curve
wreaks havoc on the common iterative methods because of the multiple changes
in the direction of the slope of the curve. That makes it difficult
(unreliable) to "home in" on "the" inflection point; and there are multiple
inflection points.

I was trying to spare you the mathematical mumbo-jumbo when I
over-simplified the description.

Moreover, it might be more be "realistic" to compute the MIRR.
However, note that the MIRR does not have the same mathematical
interpretation that IRR does. But then again, the mathematical
interpretation of IRR is dubious when there are multiple sign
changes.

I don't mean to oversell the MIRR; in fact, I don't like it. But I must
admit that it does have a certain intuitive appeal insofar as the faux "rate
of return" (not really!) is always computable. Perhaps my issue with the
MIRR is the (mis)use of "IRR" in its terminology. But I don't want to get
into a dogmatic argument about it.

 

[joeu2004]

1. Generally, the IRR algorithm (referring to the generic term "IRR", not
any Excel function) is unstable when the signs of the sequence of cash
flows change more than once. This is a mathematical issue with the common
iterative methods, not with Excel per se. See
http://en.wikipedia.org/wiki/Internal_rate_of_return. [....]
I can only speculate what the problem is for Excel XIRR with the combined
cash flows.

I cannot find any reason for Excel XIRR to "fail" (return an incorrect IRR)
for the combined cash flow.

First, my own implementation of XIRR using a Newton-Raphson algorithm finds
the correct IRR compared to the Goal Seek results derived in a previous
posting.

Second, the NPV curve looks reasonable in the range of IRR between -6% and
25%. (Note that Excel XIRR starts with a guess of 10%.) That is, there is
only one inflection point, and it is near the correct IRR of about -5.26%.

Be that as it may, I might also note that if we provide a "guess" (3rd
parameter) of -1%, Excel XIRR does indeed find about the correct IRR
compared to the Goal Seek result.

I usually do not mention, much less suggest, the use of the "guess"
parameter because we usually have no idea what it should be in the first
place. Moreover, specifying the "wrong" guess might lead to a very wrong
result with some dubious cash flows.