Can XIRR Work on Dates That Move Backwards in Time?

[W]

I have used XIRR in the past with a row of dates that move from earliest to
oldest date as you move right to left in the spreadsheet. The
corresponding data values are directly below the dates.

Today I worked with a spreadsheet where the dates move opposite: Earliest
date is on the far right, and more recent dates move toward the left. Is
there any way to get XIRR to work with such an arrangement of dates? I
only get errors.

 

[joeu2004]

Today I worked with a spreadsheet where the dates move opposite:
Earliest date is on the far right, and more recent dates move
toward the left. Is there any way to get XIRR to work with such
an arrangement of dates? I only get errors.

It would be nice if XIRR sorted all of the dates and corresponding values.

(I created a user-defined function to do just that.)

But it does not.

The first date/value in the range must be the earliest date. All others can
be in any order.

Suppose your dates are newest-to-oldest in B1:Z1, with corresponding values
in B2:Z2.

Create the following array-entered formulas (press ctrl+shift+Enter instead
of just Enter):
A1: =Z1
A2: =Z2

Then instead of XIRR(B1:Z1,B2:Z2), use XIRR(A1:Y1,A2:Y2).

If you do not want hard-code the right-most cell references, there are ways
to determine the right-most value. For example,
INDEX(B1:Z1,1,MATCH(2,1/$B$1:$Z$1)). That must be in an array-entered
formula (press ctrl+shift+Enter instead of just Enter). Change Z1 to the
right-most cell that you expect to use.

 

[Michael Marshall]

I have used XIRR in the past with a row of dates that move from earliest to

oldest date as you move right to left in the spreadsheet. The

corresponding data values are directly below the dates.



Today I worked with a spreadsheet where the dates move opposite: Earliest

date is on the far right, and more recent dates move toward the left. Is

there any way to get XIRR to work with such an arrangement of dates? I

only get errors.

If each of the cash flows and their corresponding dates are contained in adjacent cells then order of the cash flows and dates do not matter when calculating internal rate of return for irregular cash flows using Excel XIRR function or TADXL tadXIRR function

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

A7:E7
4000 1000 -10000 5000 3000
A8:E8
12/31/2014 12/31/2016 12/31/2012 12/31/2013 12/31/2015

But since the date for each of the cash flows is in the cell right below itthus it does not matter at all whether the cash flows are in any given order

So the following three calls to XIRR or tadXIRR would display the same IRR of 14.49%

=XIRR(A1:E1, A2:E2)
=tadXIRR(A1:E1, A2:E2)

=XIRR(A4:E4, A5:E5)
=tadXIRR(A4:E4, A5:E5)

=XIRR(A7:E7, A8:E8)
=tadXIRR(A7:E7, A8:E9)

Excel IRR reports an internal rate of return when interest is compounded periodically whereas tadXIRR function found at http://tadxl.com can display IRR for both periodic and continuous compounding of interest.

 

[joeu2004]

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.