Order of IRR payments

[rwunderlich]

I'm struggling to get an IRR() calc to work correctly. I have a payment row for each month, and the data table is sorted by month. I'm doing something like:

IRR(Debit + Credit + Value)

I get an incorrect result because it appears the IRR is not processing the payments in the month order, but rather in load order of the values. I can confirm that by generating the list of payments  using concat() as input to a RangeIRR function. That gives the correct result if I sort the list by month.

Anyone encounter this and have a workaround?

Thanks,

Rob

[swuehl]

Not sure if I can follow, Sunny.

I was talking about load order of Payment values doesn't seem to have an Impact, not about the order of records. This for sure should have an impact (and it does).

Regards,

Stefan

[swuehl]

I have observed something else:

Adding a line to my above sample with an already existing Payment value

Payment
-1
3
1
2
1

and IRR(Payment) returns NULL.

Removing the code for the random load order, the function returns 2.4267, but the RangeIRR() function and excel return 2.4769.

[sunny_talwar]

I am not sure what the exact issue is, but I think what Rob is saying is that the payments need to be this

Date, Payments

01/01/2014, -10

01/01/2015, 5

01/01/2016, 4

01/01.2017, 6

But if they are loaded like this:

Date, Payments

01/01/2014, -10

01/01/2016, 4

01/01/2015, 5

01/01.2017, 6

for some reason, the IRR isn't consistent. I am not sure how to add the Rand table in this case for testing. But can you test what IRR do you get in this case?

[swuehl]

I believe IRR isn't looking at the dates, it's assuming equi-distant time of cash flow .

The sequence of Payments is different in your example, so IRR will differ: 0.2257 vs. 0.2165

[sunny_talwar]

Right, and I believe what Rob is struggling with is the fact that although IRR is not dependent on the sequence of payments, but in reality they do follow a schedule. 01/01/2015 payment needs come before 01/01/2016 payment when the IRR calculation is done.

[sunny_talwar]

May be I am completely wrong, but I think based on the sample I provided, Rob is looking to get 0.2257 regardless of how the dates (and payments are sorted) and closest I was able to get was using this

=XIRR(TOTAL Payment, Aggr(YearStart(Min(TOTAL Date), RowNo()), (Date, (NUMERIC))))

The above expression gives 0.2255 regardless of how the data is sorted

[swuehl]

Sunny,

as far as I understood, Rob has created the data table sorted chronological, so we would expect it to work with IRR(). It seems it doesn't work.

My question from prev post was if there is some more evidence that the load order plays a role.

I currently believe that (solely the) load order doesn't play a role, but if the data table Show duplicate cash flow values.

For example:

LOAD Payment, recno() as ID INLINE [
Payment
-10
4
5
6
5
];

will produce different results using IRR() in QlikView compared to RangeIRR() / Excel  (0.3229 vs 0.3290)

If you add a very small number to each Payment to make them distinct, the results are getting consistent again:

LOAD Payment+1E-14*recno() as Payment2, Payment, recno() as ID INLINE [
Payment
-10
4
5
6
5
];

[swuehl]

Sunny T <span class="icon-status-icon icon-mvp" title="Mvp"></span> wrote:

Rob the closest I was able to get was this:

=XIRR(Payments, Aggr(YearStart(Min(TOTAL Date), RowNo()), (Date, (NUMERIC))))

This gives 16.33% whereas help says that it should be 16.34% (IRR - chart function ‒ QlikView)

I changed the order of the payments from the sample in help

Cashflow:

LOAD 2013 as Year,

  *

Inline [

Date|Discount|Payments

2013-01-01|0.1|-10000

2013-10-30|0.1|4200

2013-03-01|0.1|3000

2014-02-01|0.2|6800

] (delimiter is '|');

Just using the straight IRR gives 17.15%

Sunny,

I believe Qlik / MS Excel uses a 365 day year. So if you want to mimic IRR with XIRR, I think you would need to use:

=XIRR(Payments, aggr( Makedate(2000)+rowno()*365, (Date, (NUMERIC))))

The start year is pretty arbitrary. Worked for me on QV11.20 without the aggr() StructuredParameter (so I used correct table record sorting).

[sunny_talwar]

Uses 365 days for IRR you mean?

[sunny_talwar]

Yup, you are right, that seems to do it

I had a little different expression, but the idea is the same:

=XIRR(TOTAL Payment, Aggr(YearStart(Min(TOTAL Date))+ (RowNo()*365), (Date, (NUMERIC))))