cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
brianalva
Helper I
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.

 

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

View solution in original post

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.

View solution in original post

7 REPLIES 7
brianalva
Helper I
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

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

brianalva
Helper I
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 

 

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

View solution in original post

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.

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

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

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

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

Top Solution Authors