XIRR error for huge cash flows streams

[Nagesh20]

Hi All,

My cashflow data is more than 12,000 rows for different funds which inlcude both positive and negative values. In order to calculate pooled IRR, applying this simple formula "Return = XIRR(Test1,Test1[Cash Flow],Test1[Date])" and getting belw error.

[Ashish_Mathur]

Hi,

What result do you get when you calculate the XIRR in MS Excel?

[Nagesh20]

Hi @Ashish_Mathur , I get the XIRR of 0.0000298% in Excel. 

[Ashish_Mathur]

Try to give a guess rate in the XIRR measure.

[Nagesh20]

Tried the below formula and getting the same error

XIRR = XIRR(Test1,Test1[Cash Flow],Test1[Date],0.1)

XIRR = XIRR(Test1,CALCULATE(SUM(Test1[Cash Flow])),Test1[Date],0.1)

Identified one more issue with the data in Table View (in Desktop) -  even though table contains large positive cashflows but i can see only negative numbers through filter option. This issue is not there in power query. I do not know whether this would impact on the calculation.

[Ashish_Mathur]

Hi,

You should be able to see the positive numbers by applying a Number filter on that column.  Share the download link of the PBI file.

[Anonymous]

Hi @Nagesh20 ,

XIRR Function (DAX) states:

Return Value

Internal rate of return for the given inputs. If the calculation fails to return a valid result, an error is returned.

Remarks

The value is calculated as the rate that satisfies the following function:

The cash flow value series must contain at least one positive and one negative number.

Does your data have to contain at least one positive and one negative number?

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.