"enneirda" wrote:
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 a
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.
"enneirda" wrote:
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.