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.
what do you mean by "[@Amt]" in your example? I'm trying to recreate this since I am having the same issue but I don't know what this is referencing. Thank you
[@Amt] is the name of the column I defined as SUM ( Table[Amount] ) in the Summary variable.