Simple XIRR Formula returning null value

[mjperreault]

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

[sunny_talwar]

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.

[mjperreault]

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

[sunny_talwar · Accepted solution]

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.