Forum Discussion

Pibass's avatar
Pibass
Frequent Visitor
2 years ago

Help with XIRR

I am using the XIRR function which seems to be working fine for all cases, but one. Shown here:

 

The cashflows work in Excel to calculate the XIRR to approximately 27%. 
Here is a link to the cashflows:
https://1drv.ms/u/s!Ao6v0drQftqRhaFhSoO5lNOZeigeeA?e=lghItN 

Here is the DAX for my measure:


Thanks in advance for any help!



 

Need Help

2 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    Lotsa caveats.

     

    • The series of cash flow values must contain at least one positive number and one negative number.

    • Avoid using ISERROR or IFERROR functions to capture an error returned by XIRR. If some inputs to the function may result in a no solution error, providing an alternateResult parameter is the most reliable and highest performing way to handle the error.

    • To learn more about using the alternateResult parameter, be to check out this video.

    • When the absolute value of initial payment is small, the calculation likely fails to return a valid result.

    • Avoid including 0 values in payments. They will not impact the final result, and using 0 as initial payment will fail XIRR() calculation always.

  • Pibass's avatar
    Pibass
    Frequent Visitor
    2 years ago

    Thanks for the tips. I initially avoided the use of IFERROR, but when I do that, the row was excluded completely from my results. I added it to see which rows were missing.