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.
Hi, can someone assist me with this error in Power Pivot. The XIRR is not giving me errors in Power Pivot but returns this error when the data is loaded to a pivot in Excel. I used these DAX formulas
IRRX:=VAR Summary =
SUMMARIZE ( 'NPV Fact', 'NPV Fact'[MonthDate], "@Amt", SUM ( 'NPV Fact'[Net Cash Flow (Query)] ) )
VAR FilterZeros =
FILTER ( Summary, [@Amt] <> 0 )
RETURN
XIRR ( FilterZeros, [@Amt], 'NPV Fact'[MonthDate] )
-----------------------------------------------------------------------
IRR:=IF(
ISBLANK(CALCULATE(SUM('NPV Fact'[Net Cash Flow (Query)]))) ||
ISBLANK(CALCULATE(MAX('NPV Fact'[MonthDate]))) ||
ISBLANK(CALCULATE(MAX('NPV Fact'[TENANT]))) ,
BLANK() ,
XIRR(
'NPV Fact', 'NPV Fact'[Net Cash Flow (Query)] , 'NPV Fact'[MonthDate] , 0,05))