Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
mjperreault
Creator
Creator

Simple XIRR Formula returning null value

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
];

mjperreault_0-1590768761958.png

 

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.

mjperreault_1-1590768821388.png

 

Was wondering if anyone had any ideas?

 

Thanks,
Mark

Labels (5)
1 Solution

Accepted Solutions
sunny_talwar

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

image.png

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

View solution in original post

3 Replies
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
Creator
Creator
Author

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

mjperreault_0-1590775604503.png

 

sunny_talwar

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

image.png

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

View solution in original post