cancel
Showing results for
Did you mean:
Helper I

## XIRR Calculation Error

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.

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
2 ACCEPTED SOLUTIONS
Community Champion

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] )``````
Community Champion

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.

7 REPLIES 7
Helper I

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

Community Champion

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

Helper I

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

Community Champion

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.

Community Champion

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] )``````
Helper I

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

Community Champion

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.

Announcements

#### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

#### Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors
Top Kudoed Authors