XIRR Value Sorting Issue

[mjperreault]

Hi All,

I am experiencing some weird behavior with the XIRR function and it seems to be related to the input ordering that the function is applying.  I have the attached scheduled payments (also shown below).  If you compute an XIRR in excel based on the formatting shown below (Date/TransactionID in ascending order) the output will be 0.  

Date	Value	TransactionID
10/7/2019	(1,061,243.08)	1
10/8/2019	(2,647,337.28)	2
10/16/2019	(9,727,403.85)	3
10/21/2019	(864,505.72)	4
10/22/2019	(854,814.33)	5
10/24/2019	(1,101,071.58)	6
10/29/2019	(1,827,420.00)	7
11/12/2019	(1,831,805.94)	8
11/19/2019	4,932,006.33	9
12/2/2019	1,018,308.62	10
12/15/2019	559,407.50	11
12/31/2019	-	12
1/29/2020	1,315,706.26	13
1/30/2020	472,506.68	14
2/5/2020	764,298.32	15
3/31/2020	-	16
6/15/2020	(336,157.50)	17
6/15/2020	336,157.50	18
6/30/2020	1,821,655.20	19

However in Qlik if you load in the above dataset and create a KPI XIRR(Value,Date) the output is .86756 and I've discovered its because the XIRR function in Qlik is ordering the inputs by Value in ascending order instead of date.  If you change the sorting in the excel to largest to smalled on value then compute an XIRR in Excel you get the same value as in Qlik.

The problem is I cant seem to be able to specify in the Qlik function that i want to sort by Date, I've tried 

Xirr(Aggr(Min(Value),(Date,(NUMERIC,ASCENDING)),TransactionID),Aggr(Min(Date),(Date,(NUMERIC,ASCENDING)),TransactionID))

and this does not seem to work.

Was wondering if anyone has any ideas?! To me this feels like a bug with the Qlik function but maybe I am not doing something.

I've attached my QVF and Excel file if anyone wants to take a look!

@sunny_talwar  wondering if you maybe had any thoughts

Thanks!
Mark

[sunny_talwar]

I tried looking at this, and I think the issue is not the sorting related but it is the first amount related. In QlikView, it seems to believe that the biggest negative number is the beginning for XIRR. I am not sure why it is doing that. but look at this in Excel

Excel doesn't care of sorting, all it cares about is the first date. In Excel's case it picks the first day based on the order you provide it. In QlikView, it is picking it based on the largest negative number -9,727,403.85.

I tried using RangeXIRR to see if I can overcome this problem, but I was not able to. So, I don't really know if there is a solution for this problem or not.

May be @Kushal_Chawda  can look at this.

[mjperreault · Accepted solution]

Hi @sunny_talwar ,

Thanks for taking a look, yes I agree with your analysis, first value is what I had meant by being sorting issue.  The Qlik XIRR function seems to input the transactions in ascending order based on value instead of date which seems incorrect to me. 

@Kushal_Chawda  if you are able to take a look as well this would be great!

Thanks,
Mark

[Kushal_Chawda]

@mjperreault  @sunny_talwar  as you guys already did bit of analysis I am not so far as well with same inputs. But only way I can think of now is to create the custom XIRR function which is I am sure won't be easy but also not impossible.  I will try to create it but don't expect to be completed within day or two 😀

[sunny_talwar]

@Kushal_Chawda - The only problem with a custom XIRR or IRR is that it is iterative function which might go into the territory of impossible without using R or some outside tool.

[Torbjorn_Soderberg]

Hi,

I have been working on an update for Qlik Xirr which has now been released to QCS.

During this update I was also investigating the example that you posted here. I know this is an old thread but I want to clear some things up. First, it turns out that even though changing the order (so that the first date is different) will have an effect on XNPV function which is used internally by the XIRR function, the final result from XIRR should not be affected by the data order (as long as the calculation did not reach infinity or similar). In other words, sorting the value/date pairs differently should not change the result from XIRR. So if the result changes after resorting the data, then at least one of those results must be incorrect.

And in this case, -0.86756 is the correct answer for this data, and sorting should not matter. The fact that Excel sometimes returns zero or almost zero when it shouldn’t means that there is a bug in Excel.

Here is an example where someone reports a similar issue, where Excel Xirr also returns near zero:

https://social.technet.microsoft.com/Forums/lync/en-US/66785cd3-a13b-49e7-a08a-6f0706a03309/bug-in-e...

Now when it comes to doing a closer investigation of a XIRR calculation, I recommend to draw XNPV as a line graph with discount_rate on the x-axis and XNPV result on the y-axis. This is useful since XIRR is defined as the discount_rate for which XNPV=0. In other words, XIRR should return the value on the x-axis where the XNPV curve crosses the y=0 line.

I did this for your data and here is the result:

The red line is a reference line I created to further prove my point by using the Xirr function in the reference line expression (under add-ons, dimension reference line). As you can see in the screen shot, the red line indicates the value on the x-axis where the blue XNPV curve crosses the y=0 line.

Here is a screen shot of how to add that reference line to a line chart using the XIRR function:

In the next screen shot I am using the same data but with the rows sorted in reverse date order. This will make the date that is last chronologically become the first to be processed by XNPV (which makes that date into the “present day” date for the purpose of calculating “net present value”). I did not use any expression to sort the dates differently, instead I just manually entered them in the reverse order in the load script, here is the resulting XNPV graph:

I have the same y-scale set as before (-10,000,000 to 10,000,000), but now the XNPV curve appears to be less dramatic and have a more linear shape than before. Notice that sorting in reverse changes the value of XNPV in every location except on the place where it crosses the y=0 line, that still happens at x=-0.87. This is why XIRR does not change its value when the data is sorted differently (as long as the calculation succeeds).

I also want to point out that for XNPV, sort order only matters for the first date in the list. This is because the first date that is encountered is stored as a reference date and then all other payments are discounted to that date. The reference date becomes the “present day” in the “Net present value” calculation. The other payment-date pairs can be in any order since each payment will be discounted by its own date relative to the reference date, and in the end all discounted payments are just summed up.

So to summarize, -0.86756 is the correct value for your example regardless of sorting, and the Qlik XIRR result was correct all along. And with the latest update (see link below) our XIRR should be able to handle the other scenarios correctly too.

https://community.qlik.com/t5/Member-Articles/XIRR-function-Fix-and-Update/ta-p/2049021