Solved: XIRR function cannot find solution

ApurvaKhatri · ‎05-11-2018

Hi,

I am trying to use XIRR function but I keep getting the error.

Number	Cost	Date	Quarter
1004-007	-51449.7	11/13/2021	2014Q2
1004-007	-51449.7	11/20/2021	2014Q2
1004-007	-51449.7	11/27/2021	2014Q2
1004-007	-51449.7	12/4/2021	2014Q2
1004-007	-51449.7	12/11/2021	2014Q2
1004-007	-51449.7	12/18/2021	2014Q2
1004-007	-51449.7	12/25/2021	2014Q2
1004-007	-51449.7	1/1/2022	2014Q2
1004-007	-51449.7	1/8/2022	2014Q2
1004-007	-51449.7	1/15/2022	2014Q2
1004-007	-51449.7	1/22/2022	2014Q2
1004-007	-51449.7	1/29/2022	2014Q2
1004-007	-51449.7	2/5/2022	2014Q2
1004-007	-51449.7	2/12/2022	2014Q2
1004-008	-51592.3	6/9/2018	2014Q2
1004-008	-51592.3	6/16/2018	2014Q2
1004-008	-51592.3	6/23/2018	2014Q2
1004-008	-51592.3	6/30/2018	2014Q2
1004-008	-51592.3	7/7/2018	2014Q2
1004-008	-51592.3	7/14/2018	2014Q2
1004-008	-51592.3	7/21/2018	2014Q2
1004-008	-51592.3	7/28/2018	2014Q2
1004-008	-51592.3	8/4/2018	2014Q2
1004-008	-51592.3	8/11/2018	2014Q2
1004-008	-51592.3	8/18/2018	2014Q2
1004-008	-51592.3	8/25/2018	2014Q2
1004-008	-51592.3	9/1/2018	2014Q2
1004-008	-51592.3	9/8/2018	2014Q2
1004-008	-51592.3	9/15/2018	2014Q2
1004-008	-51592.3	9/22/2018	2014Q2
1004-008	-51592.3	9/29/2018	2014Q2
1004-008	-51592.3	10/6/2018	2014Q2
1004-008	-51592.3	10/13/2018	2014Q2
1004-008	-51592.3	10/20/2018	2014Q2
1004-008	-51592.3	10/27/2018	2014Q2
1004-008	-51592.3	11/3/2018	2014Q2
1004-008	-51592.3	11/10/2018	2014Q2
1004-008	-51592.3	11/17/2018	2014Q2
1004-008	-51592.3	11/24/2018	2014Q2
1004-008	-51592.3	12/1/2018	2014Q2
1004-008	-51592.3	12/8/2018	2014Q2
1004-008	-51592.3	12/15/2018	2014Q2
1004-008	-51592.3	12/22/2018	2014Q2
1004-008	-51592.3	12/29/2018	2014Q2
1004-008	-51592.3	1/5/2019	2014Q2
1004-008	-51592.3	1/12/2019	2014Q2
1004-008	-51592.3	1/19/2019	2014Q2
1004-008	-51592.3	1/26/2019	2014Q2
1004-008	-51592.3	2/2/2019	2014Q2
1004-008	-51592.3	2/9/2019	2014Q2
1004-008	-51592.3	2/16/2019	2014Q2
1004-008	-51592.3	2/23/2019	2014Q2
1004-008	-51592.3	3/2/2019	2014Q2
1004-008	-51592.3	3/9/2019	2014Q2
1004-008	-51592.3	3/16/2019	2014Q2
1004-008	-51592.3	3/23/2019	2014Q2
1004-008	-51592.3	3/30/2019	2014Q2
1004-008	-51592.3	4/6/2019	2014Q2
1004-008	-51592.3	4/13/2019	2014Q2
1004-008	-51592.3	4/20/2019	2014Q2
1004-008	-51592.3	4/27/2019	2014Q2
1004-008	-51592.3	5/4/2019	2014Q2
1004-008	-51592.3	5/11/2019	2014Q2
1004-008	-51592.3	5/18/2019	2014Q2
1004-008	-51592.3	5/25/2019	2014Q2
1004-008	-51592.3	6/1/2019	2014Q2
1004-008	-51592.3	6/8/2019	2014Q2
1004-008	-51592.3	6/15/2019	2014Q2
1004-008	-51592.3	6/22/2019	2014Q2
1004-008	-51592.3	6/29/2019	2014Q2
1004-008	-51592.3	7/6/2019	2014Q2
1004-008	-51592.3	7/13/2019	2014Q2
1004-008	-51592.3	7/20/2019	2014Q2
1004-008	-51592.3	7/27/2019	2014Q2
1004-008	-51592.3	8/3/2019	2014Q2
1004-008	-51592.3	8/10/2019	2014Q2
1004-008	-51592.3	8/17/2019	2014Q2
1004-008	-51592.3	8/24/2019	2014Q2
1004-008	-51592.3	8/31/2019	2014Q2
1004-008	-51592.3	9/7/2019	2014Q2
1004-008	-51592.3	9/14/2019	2014Q2
1004-008	-51592.3	9/21/2019	2014Q2
1004-008	-51592.3	9/28/2019	2014Q2
1004-008	-51592.3	10/5/2019	2014Q2
1004-008	-51592.3	10/12/2019	2014Q2
1004-008	-51592.3	10/19/2019	2014Q2
1004-008	-51592.3	10/26/2019	2014Q2
1004-008	-51592.3	11/2/2019	2014Q2
1004-008	-51592.3	11/9/2019	2014Q2
1004-008	-51592.3	11/16/2019	2014Q2
1004-008	-51592.3	11/23/2019	2014Q2
1004-008	-51592.3	11/30/2019	2014Q2
1004-008	-51592.3	12/7/2019	2014Q2
1004-008	-51592.3	12/14/2019	2014Q2
1004-008	-51592.3	12/21/2019	2014Q2
1004-008	-51592.3	12/28/2019	2014Q2
1004-008	-51592.3	1/4/2020	2014Q2
1004-008	-51592.3	1/11/2020	2014Q2
1004-008	-51592.3	1/18/2020	2014Q2
1004-008	-51592.3	1/25/2020	2014Q2
1004-008	-51592.3	2/1/2020	2014Q2
1004-008	-51592.3	2/8/2020	2014Q2
1004-008	-51592.3	2/15/2020	2014Q2
1004-008	-51592.3	2/22/2020	2014Q2
1004-008	-51592.3	2/29/2020	2014Q2
1004-008	-51592.3	3/7/2020	2014Q2
1004-008	-51592.3	3/14/2020	2014Q2
1004-008	-51592.3	3/21/2020	2014Q2
1004-008	-51592.3	3/28/2020	2014Q2
1004-008	-51592.3	4/4/2020	2014Q2
1004-008	-51592.3	4/11/2020	2014Q2
1004-008	-51592.3	4/18/2020	2014Q2
1004-008	-51592.3	4/25/2020	2014Q2
1004-008	-51592.3	5/2/2020	2014Q2
1004-008	-51592.3	5/9/2020	2014Q2
1004-008	-51592.3	5/16/2020	2014Q2
1004-008	-51592.3	5/23/2020	2014Q2
1004-008	-51592.3	5/30/2020	2014Q2
1004-008	-51592.3	6/6/2020	2014Q2
1004-008	-51592.3	6/13/2020	2014Q2
1004-008	-51592.3	6/20/2020	2014Q2
1004-008	-51592.3	6/27/2020	2014Q2
1004-008	-51592.3	7/4/2020	2014Q2
1004-008	-51592.3	7/11/2020	2014Q2
1004-008	-51592.3	7/18/2020	2014Q2
1004-008	-51592.3	7/25/2020	2014Q2
1004-008	-51592.3	8/1/2020	2014Q2
1004-008	-51592.3	8/8/2020	2014Q2
1004-008	-51592.3	8/15/2020	2014Q2
1004-008	-51592.3	8/22/2020	2014Q2
1004-008	-51592.3	8/29/2020	2014Q2
1004-008	-51592.3	9/5/2020	2014Q2
1004-008	-51592.3	9/12/2020	2014Q2
1004-008	-51592.3	9/19/2020	2014Q2
1004-008	-51592.3	9/26/2020	2014Q2
1004-008	-51592.3	10/3/2020	2014Q2
1004-008	-51592.3	10/10/2020	2014Q2
1004-008	-51592.3	10/17/2020	2014Q2
1004-008	-51592.3	10/24/2020	2014Q2
1004-008	-51592.3	10/31/2020	2014Q2
1004-008	-51592.3	11/7/2020	2014Q2
1004-008	-51592.3	11/14/2020	2014Q2
1004-008	-51592.3	11/21/2020	2014Q2
1004-008	-51592.3	11/28/2020	2014Q2
1004-008	-51592.3	12/5/2020	2014Q2
1004-008	-51592.3	12/12/2020	2014Q2
1004-008	-51592.3	12/19/2020	2014Q2
1004-008	-51592.3	12/26/2020	2014Q2
1004-008	-51592.3	1/2/2021	2014Q2
1004-008	-51592.3	1/9/2021	2014Q2
1004-008	-51592.3	1/16/2021	2014Q2
1004-008	-51592.3	1/23/2021	2014Q2
1004-008	-51592.3	1/30/2021	2014Q2
1004-008	-51592.3	2/6/2021	2014Q2
1004-008	-51592.3	2/13/2021	2014Q2
1004-008	-51592.3	2/20/2021	2014Q2
1004-008	-51592.3	2/27/2021	2014Q2
1004-008	-51592.3	3/6/2021	2014Q2
1004-008	-51592.3	3/13/2021	2014Q2
1004-008	-51592.3	3/20/2021	2014Q2
1004-008	-51592.3	3/27/2021	2014Q2
1004-008	-51592.3	4/3/2021	2014Q2
1004-008	-51592.3	4/10/2021	2014Q2
1004-008	-51592.3	4/17/2021	2014Q2
1004-008	-51592.3	4/24/2021	2014Q2
1004-008	-51592.3	5/1/2021	2014Q2
1004-008	-51592.3	5/8/2021	2014Q2
1004-008	-51592.3	5/15/2021	2014Q2
1004-008	-51592.3	5/22/2021	2014Q2
1004-008	-51592.3	5/29/2021	2014Q2
1004-008	-51592.3	6/5/2021	2014Q2
1004-008	-51592.3	6/12/2021	2014Q2
1004-008	-51592.3	6/19/2021	2014Q2
1004-008	-51592.3	6/26/2021	2014Q2
1004-008	-51592.3	7/3/2021	2014Q2
1004-008	-51592.3	7/10/2021	2014Q2
1004-008	-51592.3	7/17/2021	2014Q2
1004-008	-51592.3	7/24/2021	2014Q2
1004-008	-51592.3	7/31/2021	2014Q2
1004-008	-51592.3	8/7/2021	2014Q2
1004-008	-51592.3	8/14/2021	2014Q2
1004-008	-51592.3	8/21/2021	2014Q2
1004-008	-51592.3	8/28/2021	2014Q2
1004-008	-51592.3	9/4/2021	2014Q2
1004-008	-51592.3	9/11/2021	2014Q2
1004-008	-51592.3	9/18/2021	2014Q2
1004-008	-51592.3	9/25/2021	2014Q2
1004-008	-51592.3	10/2/2021	2014Q2
1004-008	-51592.3	10/9/2021	2014Q2
1004-008	-51592.3	10/16/2021	2014Q2
1004-008	-51592.3	10/23/2021	2014Q2
1004-008	-51592.3	10/30/2021	2014Q2
1004-008	-51592.3	11/6/2021	2014Q2
1004-008	-51592.3	11/13/2021	2014Q2
1004-008	-51592.3	11/20/2021	2014Q2
1004-008	-51592.3	11/27/2021	2014Q2
1004-008	-51592.3	12/4/2021	2014Q2
1004-008	-51592.3	12/11/2021	2014Q2
1004-008	-51592.3	12/18/2021	2014Q2
1004-008	-51592.3	12/25/2021	2014Q2

The formula is : XIRR(Tablename, cost, Date). I keep getting the error.

ChrisMendoza · ‎05-11-2018

I realize this is a sample however, XIRR Function (DAX) states:

Return Value

Internal rate of return for the given inputs. If the calculation fails to return a valid result, an error is returned.

as well as the following:

Remarks

The value is calculated as the rate that satisfies the following function:

The series of cash flow values must contain at least one positive number and one negative number.

Does your data meet must contain at least one positive number and one negative number?

I added another row with 100,000 in your sample set and it produced a value in the measure.

Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!

View solution in original post

ChrisMendoza · ‎05-11-2018

I realize this is a sample however, XIRR Function (DAX) states:

Return Value

Internal rate of return for the given inputs. If the calculation fails to return a valid result, an error is returned.

as well as the following:

Remarks

The value is calculated as the rate that satisfies the following function:

The series of cash flow values must contain at least one positive number and one negative number.

Does your data meet must contain at least one positive number and one negative number?

I added another row with 100,000 in your sample set and it produced a value in the measure.

Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!

Anonymous · ‎06-08-2020

I keep getting the same error for my data, could you please help me get the XIRR value? Here is my sample data. I do not understand why the XIRR function is not working on this data set. Also, just want to know if XIRR function is optimized for direct query? According to the optimized dax functions list for direct query, XIRR is not listed in there. Can you please confirm this?

Thank you very much for your help,

Month	Cashflow
9/1/2015	0
10/1/2015	2530756
11/1/2015	2710211
12/1/2015	2979793
1/1/2016	2527118
2/1/2016	2796499
3/1/2016	2614789
4/1/2016	2777616
5/1/2016	2676319
6/1/2016	2994484
7/1/2016	2482082
8/1/2016	2703377
9/1/2016	2833793
10/1/2016	2518664
11/1/2016	2659052
12/1/2016	2690785
1/1/2017	2431973
2/1/2017	2646933
3/1/2017	2278861
4/1/2017	2323329
5/1/2017	2338517
6/1/2017	2537406
7/1/2017	2283102
8/1/2017	2317830
9/1/2017	2279586
10/1/2017	2393647
11/1/2017	2491507
12/1/2017	2041174
1/1/2018	2225666
2/1/2018	2377273
3/1/2018	1961357
4/1/2018	2095609
5/1/2018	2065100
6/1/2018	2144858
7/1/2018	2063577
8/1/2018	2233725
9/1/2018	2049687
10/1/2018	2021182
11/1/2018	2012237
12/1/2018	2134861
1/1/2019	2018427
2/1/2019	1997118
3/1/2019	1824474
4/1/2019	2026753
5/1/2019	2042240
6/1/2019	1779184
7/1/2019	2027999
8/1/2019	2030078
9/1/2019	1734192
10/1/2019	1871081
11/1/2019	2020876
12/1/2019	1821809
1/1/2020	2110641
2/1/2020	1582513
3/1/2020	1666309
4/1/2020	1961726
5/1/2020	1636368
6/1/2020	1607873
7/1/2020	1561124
8/1/2020	1512438
9/1/2020	1462936
10/1/2020	1419186
11/1/2020	1381266
12/1/2020	1375718
1/1/2021	1371869
2/1/2021	1368332
3/1/2021	1364890
4/1/2021	1361034
5/1/2021	1357726
6/1/2021	1354761
7/1/2021	1352805
8/1/2021	1351168
9/1/2021	1318704
10/1/2021	1317577
11/1/2021	1316801
12/1/2021	1263470
1/1/2022	1263078
2/1/2022	1262628
3/1/2022	1155687
4/1/2022	1154458
5/1/2022	1153272
6/1/2022	1005599
7/1/2022	1003438
8/1/2022	1001239
9/1/2022	802456.5
10/1/2022	799104.1
11/1/2022	795512.8
12/1/2022	434482.4
1/1/2023	425285.9
2/1/2023	415978.1
3/1/2023	155253
4/1/2023	143833.5
5/1/2023	132329.9
6/1/2023	-284073
7/1/2023	-297435
8/1/2023	-310741
9/1/2023	-444419
10/1/2023	-460857
11/1/2023	-477247
12/1/2023	-650332
1/1/2024	-1024136
2/1/2024	-1253589
3/1/2024	-941283
4/1/2024	-954679
5/1/2024	-968061
6/1/2024	-662988
7/1/2024	-675013
8/1/2024	-686819
9/1/2024	-348419
10/1/2024	-358869
11/1/2024	-369370
12/1/2024	-145120
1/1/2025	-154294
2/1/2025	-163296
3/1/2025	40036.85
4/1/2025	31953.25
5/1/2025	23256.5
6/1/2025	109458
7/1/2025	100499.8
8/1/2025	91586.33
9/1/2025	233011.1
10/1/2025	224952.6
11/1/2025	216932.7
12/1/2025	278887
1/1/2026	271376.7
2/1/2026	263904.4
3/1/2026	326778.9
4/1/2026	319776.2
5/1/2026	312808.2
6/1/2026	348197.5
7/1/2026	341560.2
8/1/2026	334955.9
9/1/2026	381111.7
10/1/2026	374866
11/1/2026	368651.8
12/1/2026	403539.5
1/1/2027	397643.9
2/1/2027	391775.4
3/1/2027	443003.7
4/1/2027	437506.6
5/1/2027	432036.3
6/1/2027	460488.1
7/1/2027	455279.1
8/1/2027	450097
9/1/2027	475544.6
10/1/2027	470597.5
11/1/2027	465674.2
12/1/2027	481523
1/1/2028	475077.7
2/1/2028	468669.5
3/1/2028	486045.6

ApurvaKhatri · ‎05-11-2018

Thanks alot @ChrisMendoza.!!!! I will try

XIRR function cannot find solution

Return Value

Remarks

Return Value

Remarks

Helpful resources

Power BI Dataviz World Championships

Power BI Monthly Update - December 2025

FabCon Atlanta 2026

How to Get Your Question Answered Quickly

FabCon is coming to Atlanta

XIRR function cannot find solution

Return Value

Remarks

Return Value

Remarks

Helpful resources

Power BI Dataviz World Championships

Power BI Monthly Update - December 2025

FabCon Atlanta 2026

How to Get Your Question Answered Quickly