I am calculating IRR in MS excel 2010 (with IRR function; Guess 0.1) for increme
ID: 3563246 • Letter: I
Question
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.
Incremental CF are as follows;
-2913,
+5588,
+4548,
+2414,
+534,
-126,
-799,
-1161,
-1204,
-295,
-925,
-603,
-625,
-642,
-662,
-129,
-282.
PLease note that NPV10 of these cashflows is positive, around 4837. Please guide why IRR is negative while NPV is positve.
Regards,
Explanation / Answer
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%).
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.