XIRR issue

[kennek]

Why is XIRR 0% for years 2022-2025,but in 2026 its negative? Using a guess does not change the answer.

Dates	12/1/2018	11/1/2019	4/1/2020	3/1/2021	7/1/2022	7/1/2023	7/1/2024	7/1/2025	7/1/2026	7/1/2027	7/1/2028	7/1/2029
Values	-$24,612	-$62,579	-$63,417	-$5,259	$21,485	$21,575	$22,074	$22,185	$20,638	$19,601	$19,755	$20,044
XIRR	#N/A	#NUM!	#NUM!	#NUM!	0.0%	0.0%	0.0%	0.0%	-7.6%	-3.9%	-1.0%	1.2%

<colgroup><col width="64" style="width: 48pt;"> <col width="76" style="width: 57pt; mso-width-source: userset; mso-width-alt: 2779;" span="3"> <col width="69" style="width: 52pt; mso-width-source: userset; mso-width-alt: 2523;"> <col width="71" style="width: 53pt; mso-width-source: userset; mso-width-alt: 2596;" span="8"> <tbody> </tbody>

formula -> =xirr($C$2:C2,$C$1:C1)

 

[joeu2004]

I'm not convinced that this is a valid cash flow model, in the first place. Be that as it may....

The #N/A error arises because XIRR requires at least 2 data points for its calculation. That is, a pair of values and a pair of corresponding dates.

The #NUM errors arise because XIRR requires at least 2 values with opposite signs. That is, at least one negative and one positive value.

If you reformat the cells that display 0%, you will see that their actual value is 2.98E-09. In my experience, +/-2.98E-09 should be treated the same as #NUM and #DIV/0, to wit: usually, they indicate that XIRR requires a "guess".

When I enter a "guess" of -0.5 (-50%), XIRR is able to return valid IRRs.

Frankly, -0.5 was a quick-and-dirty "guess". Just got lucky with it.

 

[kennek]

Thanks! Entering a negative guess allows all the negative IRR's to solve, but makes the positive IRR's error out. Entering a positive guess allows the positive IRR to solve, but creates the original issue. I think i will solve this with the and iferror function that gives it a negative and positive iferror.

I'm not convinced that this is a valid cash flow model, in the first place. Be that as it may....

The #N/A error arises because XIRR requires at least 2 data points for its calculation. That is, a pair of values and a pair of corresponding dates.

The #NUM errors arise because XIRR requires at least 2 values with opposite signs. That is, at least one negative and one positive value.

If you reformat the cells that display 0%, you will see that their actual value is 2.98E-09. In my experience, +/-2.98E-09 should be treated the same as #NUM and #DIV/0, to wit: usually, they indicate that XIRR requires a "guess".

When I enter a "guess" of -0.5 (-50%), XIRR is able to return valid IRRs.

Frankly, -0.5 was a quick-and-dirty "guess". Just got lucky with it.

 

[joeu2004]

Entering a negative guess allows all the negative IRR's to solve, but makes the positive IRR's error out. Entering a positive guess allows the positive IRR to solve, but creates the original issue.

I never said, not intended to say, that you should use a "guess" anywhere else, or that the same "guess" can work for all formulas.

That's the unfortunate thing about Excel's XIRR implementation: there is no good way to determine a "guess"; and there is no "one size fits all" guess.

My own XIRR implementation rarely requires a "guess". But it does require a "guess" for the years 2022, 2023 and 2024. I suspect that has to do with the contrived nature of the cash flows (my guess).

If your IFERROR approach works for all of your data, it is just luck.