XIRR issue

[kennek]

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

[TABLE="width: 695"]
<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>[TR]
[TD="width: 64, bgcolor: transparent"]Dates
[/TD]
[TD="width: 76, bgcolor: transparent, align: right"]12/1/2018[/TD]
[TD="width: 76, bgcolor: transparent, align: right"]11/1/2019[/TD]
[TD="width: 76, bgcolor: transparent, align: right"]4/1/2020[/TD]
[TD="width: 69, bgcolor: transparent, align: right"]3/1/2021[/TD]
[TD="width: 71, bgcolor: transparent, align: right"]7/1/2022[/TD]
[TD="width: 71, bgcolor: transparent, align: right"]7/1/2023[/TD]
[TD="width: 71, bgcolor: transparent, align: right"]7/1/2024[/TD]
[TD="width: 71, bgcolor: transparent, align: right"]7/1/2025[/TD]
[TD="width: 71, bgcolor: transparent, align: right"]7/1/2026[/TD]
[TD="width: 71, bgcolor: transparent, align: right"]7/1/2027[/TD]
[TD="width: 71, bgcolor: transparent, align: right"]7/1/2028[/TD]
[TD="width: 71, bgcolor: transparent, align: right"]7/1/2029
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Values
[/TD]
[TD="bgcolor: transparent, align: right"]-$24,612[/TD]
[TD="bgcolor: transparent, align: right"]-$62,579[/TD]
[TD="bgcolor: transparent, align: right"]-$63,417[/TD]
[TD="bgcolor: transparent, align: right"]-$5,259[/TD]
[TD="bgcolor: transparent, align: right"]$21,485[/TD]
[TD="bgcolor: transparent, align: right"]$21,575[/TD]
[TD="bgcolor: transparent, align: right"]$22,074[/TD]
[TD="bgcolor: transparent, align: right"]$22,185[/TD]
[TD="bgcolor: transparent, align: right"]$20,638[/TD]
[TD="bgcolor: transparent, align: right"]$19,601[/TD]
[TD="bgcolor: transparent, align: right"]$19,755[/TD]
[TD="bgcolor: transparent, align: right"]$20,044[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]XIRR[/TD]
[TD="bgcolor: transparent, align: center"]#N/A[/TD]
[TD="bgcolor: transparent, align: center"]#NUM![/TD]
[TD="bgcolor: transparent, align: center"]#NUM![/TD]
[TD="bgcolor: transparent, align: center"]#NUM![/TD]
[TD="bgcolor: transparent, align: right"]0.0%[/TD]
[TD="bgcolor: transparent, align: right"]0.0%[/TD]
[TD="bgcolor: transparent, align: right"]0.0%[/TD]
[TD="bgcolor: transparent, align: right"]0.0%[/TD]
[TD="bgcolor: transparent, align: right"]-7.6%[/TD]
[TD="bgcolor: transparent, align: right"]-3.9%[/TD]
[TD="bgcolor: transparent, align: right"]-1.0%[/TD]
[TD="bgcolor: transparent, align: right"]1.2%
[/TD]
[/TR]
</tbody>[/TABLE]
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.