Help: XIRR & XNPV - Results vary too much if calculating annual vs monthly cash flows.

The XIRR formulas in C10 and C23 are:
C10: =XIRR(B5:I5,B4:I4)
C23: =XIRR(B18:I18,B17:I17)

You can confirm that these are valid IRRs (but perhaps not the only ones) by verifying that the following formulas return nearly zero:
For C10: =XNPV(C10,B5:I5,B4:I4)
For C23: =XNPV(C23,B18:I18,B17:I17)

Indeed, both return values between -3E-9 and -5E-9, which are very small and very close to zero.

Excel XIRR always returns an annual(ized) IRR. So in the case of C23, the monthly IRR is approximately =(1+C23)^(1/12)-1. That returns about 17.21%.

Note that that is close to 17.03%, which is returned in C10 for the same cash flow amounts that occur annually. I think that is what you expected.

Your NPV calculation using the WACC looks correct. But note that it has nothing to do with the IRR calculation. The IRR is a discount rate that causes the NPV to be zero (or relatively nearly so).

Thank you, your response was very helpful and answered my questions.

You're welcome. And I want to compliment your initial posting. The question was phrased very concisely, and the example Excel file was very clear. A rarity in these forums, especially for a first time poster.

But I overlooked part of your original question....

Originally Posted by sbrownusc

Also, please confirm I can always use XIRR or XNPV (instead of IRR and NPV) to build a template, in case there are any variations/inconsistencies in timelines.

I'm sorry to say: no, they are not always computable.

First, mathematically, an IRR cannot always be calculated for every cash flow series. Also, again mathematically, there can be multiple IRRs for the same cash flow series; which one is "best" is a judgment call. The reasons are complicated to explain.

Second, the Excel IRR and XIRR implementations are even less reliable.

In part, they are limited by internal iteration and tolerance constraints. But also, I believe their internal algorithm is flawed: I suspect they use an estimated derivative instead of the exact derivative, which is computable. Consequently, sometimes they fail unnecessarily, even when a good "guess" is provided. But even more peculiarly, sometimes they return bogus numeric values instead of an Excel error (#NUM or #DIV/0).

In my experience, Excel XIRR is less reliable than Excel IRR. But of course, the latter cannot always be used as a substitute.

Therefore, it might be prudent to test any value returned by Excel IRR and XIRR by using Excel NPV and XNPV to be sure the result is relatively nearly zero. (Of course, Excel IRR and XIRR should have done that already. Klunk!)

(But another caveat: Excel XNPV does not accept negative IRRs. There is no good reason for that limitation; in fact, Excel IRR does. The workaround is to use SUMPRODUCT instead of XNPV.)

And third, there is the issue of providing a "guess". I don't know of any reliable way to calculate a "guess" automatically, other than by implementing an iterative algorithm in a VBA function. And again, sometimes the Excel functions fail even when we provide a good "guess", notably the correct IRR that I have calculated another way. (Klunk!)

Despite all those caveats, in my experience, Excel IRR and XIRR generally work well for real-world cash flows, if the cash flow model is set up correctly.

Thanks, this makes a lot of sense. Would you mind if I ask another clarification on XNPV calcs? From what I'm reading, there are two ways (both seem to be accepted) to do the calculation in excel. One way, takes all cash outflows and inflows together into the XNPV formula (this is also how my company does it). However, I think it may be more accurate to do the XNPV calc on cash inflows only, then add back the non discounted initial cash outflow. Please see below, my formulas are in cells D10, and D11.

I imagine there will be a lot of caveats to this discussion, but these caveats are probably very helpful so I can apply them to each situation.

Thanks.

Screen Shot 2017-07-08 at 11.29.51 PM.png

Originally Posted by sbrownusc

From what I'm reading, there are two ways (both seem to be accepted) to do the calculation in excel. One way, takes all cash outflows and inflows together into the XNPV formula (this is also how my company does it). However, I think it may be more accurate to do the XNPV calc on cash inflows only, then add back the non discounted initial cash outflow. Please see below, my formulas are in cells D10, and D11.

Generally, it is better to attach actual Excel files, not images. "If a picture is worth a thousand words, an Excel file is worth a million". :-)

But in this case, I am able to see enough in the image, thanks again to your uncommon thoroughness.

The formula in C10, shown in D10, would be correct (usually) if you were using Excel NPV. The conventional definition of NPV discounts all cash flows to "present time", which is usually the time of the first cash flow. In other words, conventional NPV(C8,B5:M5) = B5 + C5/(1+R1) + D5/(1+R1)^2 +.... But Excel NPV discounts the first cash flow, as well. In other words, Excel NPV(C8,B5:M5) = B5/(1+R1) + C5/(1+R1)^2 +.... Effectively, "present time" is the period before the first cash flow. So, to calculate a conventional NPV, you are correct: we should calculate NPV(C8,C5:M5)+B5.

I believe Excel NPV(C8,B5:M5) would be correct for cash flows "in advance" (TYPE=1), such as leases.

(On the other hand, when C8 is the IRR and NPV is expected to be zero, we can show that the two uses of Excel NPV are mathematically equivalent. In other words, NPV(C8,B5:M5) and NPV(C8,C5:M5)+B5 should return about the same result when C8 is the IRR. The only difference should be due to computer arithmetic anomalies.)

But you are using Excel XNPV. If you look carefully at the mathematical description of the XNPV calculation in the XNPV help page (click here) [1], you will see that Excel XNPV discounts to the time of the first cash flow. In other words, it follows the conventional definition. So for Excel XNPV, no adjustment is necessary. The formula in C11, shown in D11, works just fine, to wit: XNPV(C8,B5:M5).

[1]https://support.office.com/en-us/article/XNPV-function-1b42bbf6-370f-4532-a0eb-d67c16b664b7?ui=en-US&rs=en-US&ad=US&fromAR=1