XIRR Issue for the Power BI

[mrkeylam]

Dear Support Team

Hope all is well. This is Key Lam from Leading Edge Group. We are providing a POC for an Investment company by giving financial insights through Power BI. One of their KPI includes the calculation of the internal rate of return (XIRR), with which we are encountering the problem as below:

1. There are 40 projects needed to calculate the XIRR by Power Bi DAX
1. we found that 38 projects' results are correct
2. but 2 of them showed errors / different results with excel.

1. We try to put the same data in Excel and recalculate it by the Excel XIRR equation. It can show the 40 projects result as normal.

After the testing, we are not sure if it is a bug in Power Bi or if we missed something in the Power Bi. Since the POC timeline is urgent now, so we would like to escalate this question to Power Bi expect and hope they can help on this issue. Please kindly find the below data and related information for reference.

Case 1:

The cash flow of the first project with error as the XIRR and the result in Power Bi (we set “-“ if error)

   

The XIRR formula used in Power BI:

The same cashflow but different results in Excel:

Case 2:

The cash flow of the first project with error as the XIRR and the result in Power Bi:

  

The same cashflow but different results in Excel:

Regards

Key Lam

[v-xiaotang · Accepted solution]

Hi @mrkeylam 

After my testing, I found that the problem was that the last date was a net zero transactions.  xirr will work fine when the last date has a non-zero transaction.

You can refer to the solution in a same problem,

Solved: XIRR Calculation Error - Microsoft Power BI Community

Best Regards,

Community Support Team _Tang

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