"Michael Marshall" wrote:
Here is the actual cash flows that move from left to right
A1:E1
-10000 5000 4000 3000 1000
A2:E2
12/31/2012 12/31/2013 12/31/2014 12/31/2015 12/31/2016

Now the same cash flows move from right to left
A4:E4
1000 3000 4000 5000 -10000
A5:E5
12/31/2016 12/31/2015 12/31/2014 12/31/2013 12/31/2012

And now all the cash flows are mixed up
[....]
So the following three calls to XIRR [...] would display the
same IRR of 14.49%

Yes, in theory. But did you even bother to try them, especially the 2nd set
(reverse order)?

If it truly worked for you, what version of Excel are you using?

I do not have access to Excel 2013 or any Mac Excel.

But with Excel 2003, 2007 and 2010, XIRR for the 2nd data set returns about
2.980E-09, not about 14.49% that XIRR correctly returns for the 1st data
set.

At first, this did not surprise me given the formula for XIRR. As I noted,
the annualized period for each cash flow is (d[j]-d[1])/365. Note the
reference to d[1], the first date in the range.

However, when I use Goal Seek to derive the internal rate of return in G4
that results in zero for the formula
=SUMPRODUCT(A4:E4/(1+G4)^((A5:E5-A5)/365)), G4 is indeed about 14.49%.

This is due to the symmetry of PV and FV calculations. That is, the
SUMPRODUCT parameter becomes an array of appreciated values, not discounted
values, to wit:
=SUMPRODUCT({1000,3000,4000,5000,-10000}/(1+G4)^({0,-366,-731,-1096,-1461}/365)

(In the 3rd case of mixed order, we are discounting and appreciating to some
"central" point in time, namely the first date in the range.)

Nevertheless, if Goal Seek can derive the internal rate of return for that
summation, XIRR should be able to as well.

Moreover, my own Newton-Raphson implementation of XIRR does work with the
dates in reverse order, even when I do not sort the data first.

So the Excel errors in some cases and the bogus result in other cases seems
to be a defect in the implementation of XIRR.

I can imagine that XIRR is fixed in some later revision of Excel.