XIRR Calculation Error

[Anonymous]

Hi,

I am trying to calculate XIRR for a given set of cashflows, which comply with all rules of XIRR function, but I am still getting an error.

Please see my data below, Can anyone please help me with this.

When loaded into excel the XIRR formula the calue returned is 13.1%,

but powerbi throws an error, Calculation error: cannot find a solution;  with formula

IRR = XIRR(Table,Table[Amount],Table[Transaction Date]), even when guess value is added

Transaction Date	Amount
11/05/2018	-$  4,811,400
11/05/2018	$  4,811,400
30/09/2018	-$       33,600
1/10/2018	-$ 18,400,500
1/10/2018	-$ 17,448,750
11/12/2018	-$  1,089,214
31/12/2018	-$     282,112
31/12/2018	-$     115,680
31/12/2018	$       42,266
31/03/2019	-$     232,941
31/03/2019	-$     147,441
31/03/2019	$       96,201
31/03/2019	$     438,675
10/05/2019	$     412,695
28/05/2019	-$  6,043,410
28/05/2019	-$         4,608
30/06/2019	-$     208,622
30/06/2019	-$       96,290
30/06/2019	-$         7,828
30/06/2019	$     878,247
16/08/2019	-$  5,981,850
16/08/2019	-$       69,390
24/09/2019	$  3,547,828
30/09/2019	-$     328,670
30/09/2019	-$     203,422
30/09/2019	-$       18,664
30/09/2019	$     979,257
29/11/2019	-$  1,784,835
29/11/2019	-$           405
20/12/2019	$  1,495,058
31/12/2019	-$     222,257
31/12/2019	-$           222
31/12/2019	$     309,150
31/12/2019	$     919,578
24/03/2020	-$     579,447
24/03/2020	-$       21,897
31/03/2020	-$     158,850
31/03/2020	-$       20,028
31/03/2020	$           892
31/03/2020	$     742,211
12/06/2020	-$  1,215,810
12/06/2020	-$         4,806
17/06/2020	$     498,669
30/06/2020	-$         5,929
30/06/2020	$       21,136
30/06/2020	$     760,991
30/09/2020	-$       35,691
30/09/2020	-$           716
30/09/2020	$     852,155
30/09/2020	$  2,073,625
30/09/2020	$ 17,405,685
30/09/2020	$ 34,811,370

[AlexisOlson · Accepted solution]

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] )

[Anonymous]

I appreciate your prompt response on this issue, well modifying cash flows themselves would be challenge but I can probably modify them on a powerquery level.

Thanks again,

Brian

[AlexisOlson]

Yes, I did the modification in Power Query as well (before eventually pushing that logic upstream to a SQL view).

[Anonymous]

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 

[AlexisOlson]

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.

[AlexisOlson · Accepted solution]

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] )

[Anonymous]

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

[AlexisOlson · Accepted solution]

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.