Forum Discussion
XIRR Calculation Error
The problem is with that first date being a net-zero transaction. Try filtering out such days.
IRR = VAR Summary = SUMMARIZE ( Table, Table[Transaction Date], "@Amt", SUM ( Table[Amount] ) ) VAR FilterZeros = FILTER ( Summary, [@Amt] <> 0 ) RETURN XIRR ( FilterZeros, [@Amt], [Transaction Date] )The first transaction is the culprit again. Having a small value like that as the first transaction is destabilizing and the XIRR function is not very robust. I don't know of a good general solution to fix this deficiency but I have applied some tricks in my reports that sometimes work.
For example, if you modify the data slightly to invest an extra $100 for one day so that instead of -1 on 9/1/2009 you have -101 on 9/1/2009 and 100 on 9/2/2009, then XIRR does converge. This modification does not materially change the result but it improves the stability of XIRR convergence.
Thanks Alexis,
Similar to the one above, should the first items always be a negative integer, Below I have a list of another transactions, with the first 3 rows as positive data.
When I Include all rows the XIRR formula throws an error,
When I exclude the first 3 rows I get a value of 63.7% which is the accurate value.
XIRR Documentation does not mention that
Yep. XIRR is finicky, not robust, and poorly documented. I've written much more robust IRR functions in SQL but DAX is not well-suited for iterative, goal-seek type of calculations.
One tip that you may find useful is that you can improve its robustness by providing a decent guess to start from. As a back-of-the-napkin estimate, you can annualize the Total Value to Paid In (TVPI) a.k.a. Multiple of Investment Cost (MOIC) as a return to use for the XIRR guess.
E.g. If your multiple is 1.33x over three years, then the initial guess to seed the XIRR calculation would be about 10% since (1+0.1)^3 = 1.331.