topic Re: Order of IRR payments in QlikView

Order of IRR payments

rwunderlich — Tue, 15 Mar 2016 21:42:39 GMT

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

Re: Order of IRR payments

sunny_talwar — Tue, 15 Mar 2016 22:05:06 GMT

Rob, I have never used IRR in QV before and I don't know if I will be able to offer any help or not, but if you have a small sample to look at, I can def. spend some time looking at it

Re: Order of IRR payments

rubenmarin — Tue, 15 Mar 2016 22:16:06 GMT

Hi, I didn't have used that function either and I don't have a QV12 here to test but.. can the new sort option for aggr give a help?

Something like: IRR(Aggr(Sum(Value), (DateField(Numeric, ascending))))

The sortable Aggr function is finally here!

Re: Order of IRR payments

sunny_talwar — Tue, 15 Mar 2016 22:42:21 GMT

Just tried with Aggr() function, it isn't working for some reason

Re: Order of IRR payments

sunny_talwar — Tue, 15 Mar 2016 23:29:31 GMT

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%

Re: Order of IRR payments

rwunderlich — Wed, 16 Mar 2016 03:58:36 GMT

It seems like the sortable aggr() would be the trick, but it's not returning the right results. It looks like the sort only works within the aggr, and that the aggr does not actually return sorted results to the outside aggregation?

For example,

concat(aggr(sum(x),(y(ASCENDING))))

does not return a concatenation sorted by y,

-Rob

Re: Order of IRR payments

rwunderlich — Wed, 16 Mar 2016 04:00:15 GMT

Thanks for the sample Sunny. I can't use XIRR because it uses a slightly different algorithm than IRR, and my results must exactly match an IRR calculation. It's a good sample to work with though.

-Rob

Re: Order of IRR payments

sunny_talwar — Wed, 16 Mar 2016 10:21:54 GMT

I agree its not perfect, but I thought to suggest it as a work-around. Because from my reading of the two, it seems the difference between the two options is that IRR assumes that payments are periodic where as XIRR can handle non-periodic payments. What I tried to do was making the non-periodic dates and create a periodic dates for each payment.

Let us know if you find a another work around, or a good solution here.

Thanks,

Sunny

Re: Order of IRR payments

swuehl — Wed, 16 Mar 2016 10:55:05 GMT

It seems like the sortable aggr() would be the trick, but it's not returning the right results. It looks like the sort only works within the aggr, and that the aggr does not actually return sorted results to the outside aggregation?

For example,

concat(aggr(sum(x),(y(ASCENDING))))

does not return a concatenation sorted by y,

-Rob

Hi Rob,

won't the Concat() function re-sort the values anyway depending on sort weight or text order?

Concerning your original issue, could you detail on your assumption that load order of values comes into play?

I tried to reproduce (on QV11.20 SR10 X64) using

RAND:
LOAD Ceil(RAND()*21)-11 as Payment
AutoGenerate 100;

NoConcatenate
LOAD * INLINE [
Payment
-1
3
1
2
];

DROP TABLE RAND;

This does change load order of Payment values on each reload. But Irr(Payment) in a text box keeps constant (2.4567, in consistency with Excel).

Regards,

Stefan

Re: Order of IRR payments

sunny_talwar — Wed, 16 Mar 2016 11:23:42 GMT

Stefan -

I am not sure how it's working in QV11.20 + I have not tested the same in QV12. But if we think about it outside of logic, don't you think that the order of payments should impact IRR. A bigger payment received earlier would have a higher NPV compared to a smaller payment. On the same logic I would assume that a bigger payment received earlier should also have a lower IRR and vice versa.

I am going to give your script a shot in QV12 and see what I get and respond back to all

Re: Order of IRR payments

swuehl — Wed, 16 Mar 2016 12:16:32 GMT

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

Re: Order of IRR payments

swuehl — Wed, 16 Mar 2016 12:37:16 GMT

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.

Re: Order of IRR payments

sunny_talwar — Wed, 16 Mar 2016 12:50:00 GMT

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?

Re: Order of IRR payments

swuehl — Wed, 16 Mar 2016 13:05:49 GMT

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

Re: Order of IRR payments

sunny_talwar — Wed, 16 Mar 2016 13:09:54 GMT

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.

Re: Order of IRR payments

sunny_talwar — Wed, 16 Mar 2016 13:25:34 GMT

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

Re: Order of IRR payments

swuehl — Wed, 16 Mar 2016 14:34:28 GMT

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
];

Re: Order of IRR payments

swuehl — Wed, 16 Mar 2016 15:22:38 GMT

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).

Re: Order of IRR payments

sunny_talwar — Wed, 16 Mar 2016 15:24:42 GMT

Uses 365 days for IRR you mean?

Re: Order of IRR payments

sunny_talwar — Wed, 16 Mar 2016 15:27:36 GMT

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))))