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

[rubenmarin]

Hi, I'm kind of lost with this calculations and maybe I didn't understood something, but this option also returns 0.1634 in text box and in a table, no matter the sort of the table:

=RangeIRR($(=Concat(Payments, ',', Date)))

It's showed as an error in syntax checker (at least in QV 11.20) but it seems to work. I don't have more complex data to make some tests and, anyway...I wouldn't know how to check if the result is correct.

[swuehl]

Hi Ruben,

I believe that's exactely the way Rob has done his initial data validation, at least that's how I did it in above scenarios.

The error in the syntax checker is a known issue, QV just doesn't expand the dollar sign expression before doing the syntax check.

I am not sure if RangeIRR() or XIRR() with above mod are an option for Rob, let's see.

Personally, I think there should be now options at hand to workaround the issue with IRR (and yes, I believe this function is buggy).

I haven't used IRR much and I also think the Help text is more confusing than helping when talking about that the cash flow times "must occur at regular intervals, such as monthly or annually", when they in fact are regarded as annual events.

Well, you could calculate numbers for any scale, but you just can't compare these numbers to well known annual interest rates.

That's why I would rather use XIRR when coping with monthly cash flows, like I 've understood Rob is dealing with.

Regards,

Stefan

[rubenmarin]

Thanks for explanation, now I have a slightly better understanding of the problem:

- No matter if the times occur at regular intervals, IRR uses the values as they will periodic. (Tried setting the same date for all records and the result doesn't changes)

- Sorts the values based on load order

Maybe was designed thinking in a chronological load of data.. so it can be improved adding an optional parameter for sorting.

[rwunderlich]

Thanks to everyone for the suggestions. I have not resolved the issue, but all the suggestions were helpful . I unfortunately ran out of time at this engagement. I'll summarize what I've found and what I did.

1. Customer is currently doing an IRR calculation using java that matches what you might expect to find in excel. I'[m trying to reproduce these exact same values in QV. I have about 140K payment values that need to be aggregated in various slices. My payment dates are all month end dates, and I need to produce an annualized number.

2. Using RangeIRR works (produces the same answer). However, the input to RangeIRR must be generated using $(=concat(..) DSE so will not work when the chart has dimensions. Note that minus the dimension problem, in all cases it produces the correct result with the customers data.

3. IRR most of the time is widely off due the order of payments. As others have demonstrated here, it's difficult to get the numbers in order when there are duplicating payment values. Sortable aggr doesn't seem to help.

4. XIRR would work, except that XIRR seems to use the actual date, giving more weight to the 31 days of March than the 28 days of Feb, whereas IRR considers all periods to be equal (1/12 of the year in this case). I've tried adjusting the dates to represent equal periods, and this gives me the correct number in 80% of the cases. I have not figured out why it does not work for the other 20%. I'll update this thread when I have an answer.

-Rob

[rubenmarin]

Finally I understand the problem, the part with the chart with dimensions is what I was losing, when i tried the IRR() funtions all dimensions disappeared, so I didn't thought in a chart with dimensions.

From examples above, adding some rows to the data:

LOAD Date(Date) as Date, Discount, Payments

Inline [

Date|Discount|Payments

2013-01-01|0.1|-10000

2013-04-30|0.1|4200

2013-03-01|0.1|3000

2013-02-01|0.1|6800

2013-01-01|0.2|-10000

2013-02-01|0.2|6800

2013-03-01|0.2|3000

2013-04-30|0.2|4200

] (delimiter is '|');

And a table with Discount as dimension I understand your struggling... and I suposse there is no need to propose a solution sorting the load order.

I also looked for some Java funtion to reproduce IRR() in a chart formula, but what I found will take a time to understand what is doing, and if some the people in this thread didn't found it, I better not try.

So for now, the only options I have are:

- Wait to an upgrade for IRR() that allows a sort parameter

- Wait for an upgrade for Aggr() that returns sorted parameters to the external function

- ...More options where I can't help

I had been proud to have helped you.

[swuehl]

4. XIRR would work, except that XIRR seems to use the actual date, giving more weight to the 31 days of March than the 28 days of Feb, whereas IRR considers all periods to be equal (1/12 of the year in this case). I've tried adjusting the dates to represent equal periods, and this gives me the correct number in 80% of the cases. I have not figured out why it does not work for the other 20%. I'll update this thread when I have an answer.

Rob, I do get consistent results comparing IRR (in Excel) values with XIRR generated values (in Excel and QV) using the synthetic date dimension I discussed above. Have you also tried this?

If you have some sample lines of data, I can create a quick demo QVW if it's unclear what I was suggesting above.

Regards,

Stefan

[sunny_talwar]

I agree with Stefan, XIRR can made to behave like IRR by manipulating the date argument within XIRR, but exact output may depend on the kind of data that is there.

[swuehl]

Just to clarify, I believe (and also tested it) that the solution will also work in QV11, if the date dimension's load order is in chronologic order (I've noticed that the example above used QV12 syntax).

I also think that XIRR and IRR converge in their internal implementation if we hit the constant cash flow period value assumed in IRR. Then discounting cash flow should be the same in both functions.

I would assume the iterative solver to find the zero transitions in the net present value equation is the same in both functions, too. So I wouldn't be surprised if we find no difference between IRR and XIRR.

(But you never know, I was surprised that QV's IRR is working so badly, too)

[tsglenn22]

Hey Rob,

I realize this is about 6 years too late, but I've had success using a sorted aggr(). It looks something like this:

IRR(aggr(sum(PAYMENTS),(MONTHS,(NUMERIC,ASCENDING))))*12

Where months are integers from 0 to the term of the loan.