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.
Hello Alexis,
I tried using your formula above and worked like a charm, but it doesnt work for another fund I am trying to calculate. Could you please help me throw some light on this one.
Transaction Date Amount
9/1/2009 -1
9/23/2009 -96987.5
10/7/2009 -106860674.3
10/9/2009 -74964867.59
10/15/2009 -16924977.06
10/16/2009 -3770502.678
10/19/2009 -82438531.51
10/21/2009 -37109881.69
10/30/2009 -8664165.28
11/5/2009 -4271473.502
11/8/2009 -4296460.461
11/10/2009 -20361351.93
11/18/2009 -24625360.42
11/19/2009 -30582.63
12/7/2009 -23885552.27
12/31/2009 -25118.5
1/13/2010 -26550
2/17/2010 -102625
2/25/2010 -176850
3/16/2010 -20315.51
3/23/2010 -23404.5
5/6/2010 10202480.69
5/7/2010 -2963.63
7/7/2010 -43325.43
7/10/2010 -42658.78
7/20/2010 -14857.45
9/24/2010 -96987.5
10/27/2010 -86771.63
10/29/2010 -42506.04
11/2/2010 45413.82
11/5/2010 -2424000000
11/8/2010 -12500000
11/9/2010 539398612.8
11/18/2010 -23508.48
12/8/2010 -11900000
12/15/2010 -11100000
1/27/2011 1623339.691
4/29/2011 2176198.1
7/29/2011 6008117.7
10/31/2011 6030485.21
1/13/2012 6056051.99
3/13/2012 -38483010
4/30/2012 24168605
7/30/2012 24979547
10/29/2012 27396408.57
11/9/2012 13048000
11/30/2012 11783474.66
12/3/2012 876314.3231
12/4/2012 1818556.73
12/5/2012 807511.3968
12/6/2012 324488.3162
12/7/2012 532867.44
12/11/2012 1943493.548
12/12/2012 2563192.03
12/13/2012 1528912.854
12/14/2012 732183.55
12/17/2012 2058550.56
12/18/2012 1838991.26
12/19/2012 2833561.341
12/20/2012 3750221.93
12/21/2012 548385.996
12/24/2012 530594.4
12/31/2012 1361033.669
1/2/2013 1257049.905
1/3/2013 967845.8237
1/4/2013 27534382.83
1/7/2013 1872588.09
1/8/2013 1534601.118
1/9/2013 1311506.674
1/10/2013 1712662.109
1/11/2013 97338.3586
1/14/2013 1327735.712
1/15/2013 5196238.58
1/16/2013 2188493.701
1/17/2013 3595664.39
1/18/2013 1795744.73
1/23/2013 494193.4646
1/24/2013 191230.62
1/25/2013 1095558.001
1/28/2013 7298293.225
1/29/2013 1332780.581
1/30/2013 1054751.246
2/1/2013 3494861.37
2/4/2013 3668036.66
2/5/2013 2616699.749
2/6/2013 3139008.23
2/8/2013 1331450.442
2/11/2013 5450516.72
2/12/2013 7398108.4
2/14/2013 7854891.18
2/15/2013 1957658.61
2/19/2013 2536601.389
2/20/2013 645374.984
2/22/2013 9693995.456
2/25/2013 1927946.77
2/26/2013 4456544.073
2/27/2013 3078530.96
2/28/2013 18843539.62
3/1/2013 6958880.943
3/4/2013 13479962.34
3/5/2013 13029385.63
3/6/2013 116182428.5
4/29/2013 1506037
4/30/2013 29002326
7/30/2013 29002326
7/31/2013 1506037
10/29/2013 31419186.5
11/1/2013 6041507261
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.