topic Simple XIRR Formula returning null value in App Development

Simple XIRR Formula returning null value

mjperreault — Sat, 16 Nov 2024 18:34:12 GMT

Hi All,

I am experiencing a strange behavior with the XIRR formula. I have a very simple date set

Load * inline [
Date,Amount,ID
2/3/2020,-57463519.21,ID
3/6/2020,-330276.80,ID
4/2/2020,-91822.87,ID
4/30/2020,12352400.00,ID
];

And on the sheet in a KPI I have the formula XIRR(Amount,Date) which is returning null. Consequently if I use the same data set in Excel. I get -.9985 when doing an XIRR formula.

Was wondering if anyone had any ideas?

Thanks,
Mark

Re: Simple XIRR Formula returning null value

sunny_talwar — Fri, 29 May 2020 17:54:55 GMT

It seems like Qlik can only calculate a positive XIRR. If you change your Amount for 4/30/2020 from 12352400 to 62352400... you would see a XIRR of 0.37 show up.

Re: Simple XIRR Formula returning null value

mjperreault — Fri, 29 May 2020 18:06:51 GMT

Hi @sunny_talwar ,

Thanks for the response, it's an interesting idea but it doesn't seem to hold true if I introduce another ID of mine.

Dataset is now

Load * inline [
Date,Amount,ID
2/3/2020,-57463519.21,A
3/6/2020,-330276.80,A
4/2/2020,-91822.87,A
4/30/2020,12352400.00,A
12/31/2019,-11458584.76,B
2/14/2020,-1277.37,B
3/31/2020,10201279.31,B
];

ID B has a negative IRR

Re: Simple XIRR Formula returning null value

sunny_talwar — Fri, 29 May 2020 18:27:43 GMT

Thanks for correcting me and sorry for misleading. It seems that the XIRR is not able to go below a certain point. I used this

Load * inline [
Date,Amount,ID
2/3/2020,-57463519.21,ID
3/6/2020,-330276.80,ID
4/2/2020,-91822.87,ID
4/30/2020,13176269.00,ID
];

and I get this

Any number below.. it won't work. Not sure as to why.