Why does XIRR result in #NUM! when I slightly change one of the cash flow entries?

Question

I am trying to back into a 9% IRR and can do so using XNPV, but would like to do so using XIRR.  Please see attached, when I approach 11 and 10% I start to get the #NUM! error.  I do not get this error in Google sheets.

Answer 1

Dana wrote:

If we look at the sum of your investments, you lost close to $1 Million dollars. [....] I would say I lost everything... nearly a 100% loss.

No.  A negative final balance means that we must add to the investment(!) at the end, if we interpret negative amounts as inflows.  (I made the same mistake and used the word "lost".  I corrected that now.)

The fact is:  (one interpretation of) the sum of the cash flows might suggest exactly the opposite.  We invested or began with 13,866,949 in 2021, and by 2030, we had withdrawn a total of 28,917,667 -- $15M more than our initial investment.

What is "lost" in the end is any additional gain on what must have remained after withdrawing nearly $29M over time.

We cannot know how much that might be without knowing the IRR. Or really:  the rate of return in the last year.  Remember:  the IRR is just an __average__ compounded rate of return; the actual rates of retun can vary greater each year.

But all of that is "fake news" -- wild speculation.  The fact is:  each cash flow is a __net__ cash flow.  For all we know, we contributed additional amounts each year.  The positive net cash flows tells only that we withdrew more than any additional contribution.

And more to the point:  the entire "story" is based on very questionable amounts -- and no context.

---

Dana wrote:

But, that is only my opinion

I won't respond to any more of your "opinions" on the subject.  My responses have been intended for the benefit of hankw06, so that he can put your "opinions" into context.

(I want to wax philosophically about parallels to US politics in the past several years. But I'll bite my tongue -- or fingers.)

Answer 2

Dana wrote:

Oh I see.  You're saying that we think his last payment is not really a cash flow.  He's just looking for a last value to bring xirr to return 9%.

"We" are not thinking anything.  The last amount (year 2031) is indeed a "cash flow" in IRR terminology.  The last amount is a net cash flow that combines any last contribution and withdrawal with the remaining balance, as if the remaining balance were a withdrawn.

And yes, hankw06 is trying to determine the last __net_cash_flow__ that makes the IRR 9%.  At least, that is my understanding.

(But that was never why I objected to your baseless conclusion that 3% was more "real".)

---

Dana wrote in a deleted response (that hankw06 might have seen):

Here's how my version of xirr converges at higher precision using vba on your model #1.

[....]

*: 0.0375180400312507895962668014

There is nothing wrong with an algorithm that finds the 3%+ IRR by default.  But it also proves nothing.

As I noted, two other (professional) algorithms find the 9%+ IRR by default.  That also proves nothing.

(The latter two algorithms can also find the 3%+ IRR, given the proper "encouragement".)

My only point was, and it is worth repeating:  I see nothing to suggest that one solution is more "real" or "better" than the other.

And that is just __my__ opinion.

Answer 3

Wow, the responses already have been great.  I agree there are 2 answers which is why excel returns the error, but doesn't explain why Google sheets does not.  I believe -$16M +/- is the correct number to achieve a 9% IRR as mentioned above, but don't think there is a way to make excel realize that even when entering 9% as a guess and using goal seek to set the xnpv of all cash flows to 0 by changing the final value.

For clarification, yes the initial investment is $13.87M and I know the cash flows each year based on projected income available to pay.  I then use these projections to show how much should go to the investor until they achieve a 9% IRR.  I realize they achieve this much sooner than year 10, but want the model to be accurate for any scenario.

"Back into" - I meant simply find the number that results in a 9% XIRR assuming I know all prior amounts and dates.

Thanks again for all the feedback.