Forum Discussion

minimalist's avatar
minimalist
Icon for Helper II rankHelper II
7 months ago
Solved

XIRR returning different (incorrect) values in Excel and Power BI

I'm trying to work out the IRR of a series of cashflows for multiple companies in the same dataset. All the cashflow commence with a negative. I tried the logic out in Excel and Power BI. The results were very different and not confidence inspiring.  

 

The XIRR measure I used is:

XIRR_TEST = XIRR( DATA_XIRR, DATA_XIRR[AMOUNT], DATA_XIRR[DATE] )

 

I didn't use the guess parameter as I have hundreds of companies in the original dataset.

 

The results match for Company 1 but are different for the other three. You'll notice that Excel is calculating the same incorrect XIRR for three of the companies. The intial output for these was 2.98-09 and I converted it to a percentage.

 

I tried to attach files but it seem to not be supported.  I'd appreciate if someone could advise what is going on and ideally show me a way to generate the correct IRR for these cashflows. If there is a way to share the files, please advise and I will upload them.

 

 Excel XIRRDAX XIRR
Company 11.0059%1.0059%
Company 20.0000002980%Error
Company 30.0000002980%Error
Company 40.0000002980%-80.4852%

 

 

As always, I'm grateful for any assistance. ðŸ˜Š

Need Help
  • v-prasare's avatar
    v-prasare
    7 months ago

    Hi minimalist,

    This behavior is expected and is due to a known difference between Excel XIRR and DAX XIRR implementations.

    Excel XIRR and DAX XIRR do not share the same convergence or retry logic.
    DAX XIRR relies on an iterative numerical method that is highly sensitive to the initial guess value and the cash‑flow profile. As a result, DAX XIRR may: 

    1. Return an error when it cannot find a valid solution

    2. Converge to a different mathematical root than Excel 

    3. Produce results that differ from Excel for the same cash‑flow series

     

    Excel XIRR applies additional internal retry and fallback logic, which allows it to successfully converge in scenarios where DAX XIRR may fail or return a different result.

    This limitation and expected behavior are documented by Microsoft and confirmed by community discussions.

    References:

     

    Recommended best practices:

    Provide an explicit Guess parameter when using XIRR in DAX.

    Use the AlternateResult parameter to handle non‑converging scenarios gracefully.

    Validate results against Excel when working with complex or non‑standard cash‑flow patterns.

     

     

     

    Thanks,

    Prashanth

7 Replies

    • minimalist's avatar
      minimalist
      Icon for Helper II rankHelper II
      7 months ago

      I tried to share but I don't seem able to. 

       

      The original problem is in my client's environment so reasonably enough, I can't share from there. I built this sanitised extract on my own PC using my Fabric/Power BI account and it seems that I am not allowed to share outside my organisation (of one person). 

       

      I spent the day examining other similar issues and it seems that XIRR is quite fragile and having a suitable guess is important. Thats is unfortunate as the investments I want to analyse have very different performances profiles.

       

      I can suppress the errors using the [AlternateResult] parameter but that seems like giving up. I might build another measure to dynamically calculate approximate guesses to see if that helps.

      • v-prasare's avatar
        v-prasare
        Icon for Community Support rankCommunity Support
        7 months ago
        Have you identified any alternative approach to handle this scenario? If yes, could you please share it here so that other community members facing similar issues can benefit as well?
  • v-prasare's avatar
    v-prasare
    Icon for Community Support rankCommunity Support
    7 months ago

    Hi minimalist,

    We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.

     

     

     

    Thank you for your patience and look forward to hearing from you.
    Best Regards,
    Prashanth Are
    MS Fabric community support

  • v-prasare's avatar
    v-prasare
    Icon for Community Support rankCommunity Support
    7 months ago

    Hi @minimalist,

    We would like to confirm whether our response has resolved your query. If you need any further assistance or have additional questions, please feel free to let us know. We’ll be happy to help.

     

     

    Thank you for your patience and look forward to hearing from you.
    Best Regards,
    Prashanth Are
    MS Fabric community support