question on XIRR (excel)

Question

Let's say we have an initial investment of -10 on 1/1/2000, and from 1/1/2001 to 1/1/2018 (with annual payments on Jan-1 of each year for 18 years) we get a CF of +2 each year with a final payment of 10 on 1/1/2019.

Thus:
1/1/2000: -10
1/1/2001: +2
...
1/1/2018: +2
1/1/2019: +10

The XIRR of above would be 19.85%, close to IRR of 20%.

Is it possible to hit an XIRR of 10% by tinkering with the final payment on 1/1/2019? It looks like we can only negate by the total positive CF received, which is +26. Otherwise, (and please correct me if I'm wrong) if we go any lower we can't solve for 0 and we'll get a #NUM error.

So if we did -26 on 1/1/2019, we'll get an XIRR of 16.24%. If we use -27 or any lower, we'll get an #NUM error. Is there another method or shortcut I can use to get down to 10%? Or is hitting 10% IRR at this point no longer possible due to the hefty CF received?

Answer 1

Found the answer and leaving an answer here in case anyone runs into the same question:

Again, the problem was I couldn't get XIRR+goal seek to help me solve for the last value on 1/1/2019 needed to bring my overall XIRR down to 10%. If we use instead XNPV+goal seek, it gets us our correct last value. For example, if we leave 0 as the last value on 1/1/2020, we get a xnpv of 6.72. We need to solve for this 6.72 to 0, so we do a goal seek on it by changing the last value on 1/1/2019. This will get us a value of -45.27 and a xnpv of 0, effectively getting us an overall XIRR of 10%.

If we test it using XIRR on the new values you'll see it gives us a #NUM, I believe it's due to limitations on the number of iterations that XIRR uses. However, we can still check using the IRR formula, which gives us an overall return of 10%.

Answer 2

@lostinOracle.... Sorry for the late response. -45.27 is not the correct answer; nor is any approximation between -45.265 and -45.275, recognizing that -45.27 is undoubtedly rounded.

And it is not necessary to use Goal Seek or Solver. AlexC is correct in approach. With dates in A1:A20 and known cash flows (-10,2,...2) in B1:B19, the last cash flow in B20 is

=-XNPV(10%,B1:B19,A1:A19)*(1+10%)^((A20-A1)/365)

which is -39.1531365995098, or

=-NPV(10%,B1:B19)*(1+10%)^ROWS(A1:A20)

which is -39.1590904484145.

The difference arises because XIRR and XNPV use the exact difference in days, which is not always 365 days per year. We can confirm those CFs by using XNPV or NPV respectively; don't use NPV with the XNPV-based FV formula.

AlexC is correct that XIRR returning #NUM is due to design flaws, IMHO. My own Newton-Raphson implementation of XIRR returns 9.99999999999991% in just 3 iterations when we use -39.1531365995098.

Of course Excel XIRR supports multiple negative cash flows. For example, when A1:A5 is 10000,2000,-3000,-5000,-7000 and B1:B5 are annual dates starting with 1/1/2000, XIRR(A1:A5,B1:B5) returns 0.074998751282692 in C1, and XNPV(C1,A1:A5,B1:B5) returns about 2.59E-05, which is relatively close to zero. Excel XIRR is most reliable when the first 1,...,m cash flows are one sign, and last cash flows m+1,..,n are the opposite sign. But Excel XIRR can work with multiple changes in sign. For example, when A1:A5 is 10000,-3000,-5000,2000,-7000, XIRR(A1:A5,B1:B5) returns 0.0701347976922989 in C1, and XNPV(C1,A1:A5,B1:B5) returns about -5.58E-05, which again is relatively close to zero.