IRR Negative while NPV is Positive

Aamir Jamshed wrote:

I am calculating IRR in MS excel 2010 (with IRR function; Guess 0.1) for incremetal cashflows of a project and i get negative IRR (-4%) for 17 years cashflows. However, uptill 11 years cashflows, IRR remains around 163.5% which seems correct. But for all 17 years CF IRR becomes -ve.
[....]

Please guide why IRR is negative while NPV is positve.

The IRR should be a rate that causes the NPV to be zero.  But the algorithm is not precise; and which side of zero you end up on is not predictable.

When I use IRR(A1:A17,10%) in XL2003, I get a #NUM error.  Often, that indicates that the "guess" is wrong.  (The default "guess" of 10% might have worked for you because the XL2010 IRR algorithm has been "improved".)

When I use IRR(A1:A17,-1%), I get about -3.9834%, probably about the same as your result. 

With that IRR as the discount rate, NPV returns about 3.20E-09, very nearly zero.  All seems fine.

By the way, the correct way to use NPV is =A1+NPV(rate,A2:A17), not NPV(rate,A1:A17).

I think the question you might have intended to ask is:  why is the IRR about 163.5284% for the first 11 cashflows, but -3.9834% for all 17 cashflows, since the influence of the last 6 cashflows should be diminishing?

[EDIT] Or so we would expect if IRR were positive.

And indeed, there is an IRR at about 163.5269% that causes the NPV of all 17 cashflows to approach zero.  You can find that result using IRR(A1:A17,163%).

The issue is:  sometimes there are multiple IRRs because the NPV curve crosses zero in multiple places as the discount rate varies.  This is especially true when the signs of the cashflows change more than once, as is the case with your example.

In the case where there are multiple IRRs, the IRR algorithm will tend to find the one closest to the "guess".  But sometimes, even that does not work.

BTW, for the first 11 cashflows, there is also an IRR around -13.5669%.  Try IRR(A1:A11,-13%).

Aamir Jamshed wrote:

Many thanks joeu. thats really helpful.

I'm glad to hear that.  But I forgot to mention that perhaps the MIRR would be useful to you [1].

The MIRR is the rate that causes the (negative) NPV of the outflows (presumed to be negative) to equal the NFV of the inflows (presumed to be positive) discounted to the present value.  Put another way:  the MIRR is the rate that causes the sum of the NPV of the outflows and the PV of the NFV of the inflows to be zero.

The MIRR is computable as long as there is at least one outflow.  However, it requires that you know some "investment rate" for inflows and some "finance rate" for outflows.

Usually there is some accounting basis for your choice (e.g. cost of capital).  And you know those rates, use them.

Alternatively, simplistically, you can think of the two rates as being the interest rate you might earn if the money were left in a risk-free account, for example a savings account.  In that case, the two rates can be the same.

Thus, for your example, MIRR(A1:A17,0.5%,0.5%) returns about 2.1378%.