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

ERRATA.... I deleted my response from last night.  It was substantially correct, and it might have been helpful.  But it contained one insignificant formula that might be incorrect.  I need to find the time to review it before reposting.

In the meantime, it might greatly simplify things if you heeded my request and uploaded an example Excel file (redacted)

that demonstrates the problem to a file-sharing website, and post the download URL in a response here.

I like box.net/files; others like dropbox.com. You might prefer onedrive.live.com because it uses the same login as

answers.microsoft.com.

In any case, test the download URL first, being careful to log out of all websites that share the same login,

in order to ensure that anyone else can access the file anonymously.

Ideally, the example Excel file should go beyond the data that you posted originally, showing annual balances before any withdrawals, rates of returns, and how the "cash flows" and remaining balances after any withdrawals are calculated.

Answer 2

(Ignore the previous submission of this posting.  I pressed Submit prematurely!)

hankw06 wrote:

I believe -$16M +/- is the correct number to achieve a 9% IRR

That was never in dispute.

However, the IRR is just a number that satisfies a polynomial equation, namely the NPV formula.

So given any discount rate that we choose for the IRR, we can always calculate __a__ value for the last term of the polynomial algebraically, using any of the formulas that I presented in previous responses.

That does not make the calcuated last term (e.g. -$16M) "correct" in practical terms.  See Examples 3, 4 and 5 below.

---

hankw06 wrote:

the initial investment is $13.87M and I know the cash flows each year based on projected income available to pay

What do you mean by "available" to pay?  Is the "projected income" (returns) actually withdrawn from the account?  Or are the returns left or otherwise reinvested in the account ("unrealized gains")?

The answer is critical for choosing the correct cash flow model.

Consider a simple investment of $1,000,000 that returns 9% per period (year).

Aside.... In the following examples, rounded calculated values are shown, but the exact results are used for any dependent calculations. Also, I use Excel IRR and NPV for simplicity, since your cash flows occur regularly ("yearly").  Excel IRR is more reliable.  And Excel XIRR provides more dubious "date precision" than you should not require, IMHO.

---

Example 1: The returns are left in the account; they are not withdrawn.  The following is the correct cash flow model.

Formulas:

C4: =B6*C3

C6: =B6+C4-C5

B7: =-B6

C7: =C5

L7: =L5+L6

B8: =IRR(B7:L7)

L8: =-NPV(9%,B7:K7) * (1+9%)^COLUMNS(B7:L7)

Note that the calculated last cash flow (L8) is the same as the actual last cash flow (L7).

Also note that the intermediate cash flows are zero because the withdrawals are zero.

Finally, note that the last cash flow is the final balance, signed as a withdrawal (plus, in your case).

---

Example 2: The returns are withdrawn (distributed; not reinvested).  The following is the correct cash flow model.

Additional Formula:

C14: =MAX(0,C13)

The use of MAX in the calculation of the withdrawals (row 14) ensures that we do not distribute a loss (negative returns), as a practical matter.

(The reference to C13 assumes that we withdraw the entire amount of any positive gain.

More generally, the formula in C14 should be =MAX(0, MIN(C13, desiredWithdrawal)).)

Again, note that the calculated last cash flow (L17) is the same as the actual last cash flow (L16).

And note that the last cash flow is the sum of the final balance (L15) and the last withdrawal (L14).  That is, it is the net cash flow.

It might be worth noting, again, that the final balance contribution to the last cash flow is signed as a withdrawal (plus).

---

The following applies those examples to your second model, which I suspect has the intended initial cash flow ($13,866,950).  Presumably, the last cash flow (-$15,000,000) was a guess.  It is the value to be calculated.

(Sorry:  The cell references below are for a different worksheet.  So there is some "overlap" with references above.  Mea culpa!)

Example 3: The returns are left in the account; they are not withdrawn (like Example 1). The following is a valid cash flow model.

Formulas:

C3: =C4/B6

L4: =L8-K6

C6: =B6+C4-C5

B7: =-B6

C7: =C5

B8: =IRR(B7:L7)

L8: =-NPV(9%,B7:K7) * (1+9%)^COLUMNS(B7:L7)

Note that the final balance (L6) and actual last cash flow (L7) are consistent with the calculated last flow (L8) that is required for an IRR of 9% (B8).

You might notice that the last return (L3 and L4) is negative; a loss.  Nevertheless, it is feasible.

Alternatively....

---

Example 4: The returns are withdrawn (like Example 2). The following is not a valid cash flow model.

Additional Formula:

C14: =MAX(0,C13)

Note that the last withdrawal (L14) is zero because the last return (L12 and L13) is negative; a loss.

However, in this case, the necessary last rate of return (L12) is -215.74%.  As a practical matter, that is not feasible.  We cannot lose more than 100% of the value.

In fact, the last rate of return will always be less than -100% (i.e. a larger loss) whenever the required last cash flow (L17) is negative.

The contradiction tells us that the cash flow model and/or the data is incorrect, even though the calculated IRR is 9% due to the algebraic relationships.

My guess:  The return of $16,838,498 (E13) is a typo.

If we assume that the correct return is $1,683,849 (drop the last digit), the following would be a valid cash flow model.

Example 5: Like Example 4, with a "corrected" return in 2024.  The following is a valid cash flow model.

Of course, that is wild speculation.  Only you can provide the changes that are necessary to correct the cash flow model.

If you need further assistance, please heed my request for you to upload an example Excel file that shows how all of these values (%return, return, withdrawal, balance and cash flows) are determined.