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

[sunny_talwar]

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

[rubenmarin]

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!

[sunny_talwar]

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

[sunny_talwar]

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%

[rwunderlich]

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

[rwunderlich]

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

[sunny_talwar]

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

[swuehl]

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

[sunny_talwar]

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