Solved

# IRR function - project with all negative periods

Posted on 2010-01-01
Medium Priority
1,182 Views
Last Modified: 2012-05-08
Is there a trick when calculating the IRR when all the periods are negative.  I understand that there must be both positive and negative numbers in the periods in order for it to work, but then how does one decide between two "bad" projects? - Using the IRR function of course.

Thanks,
JP
0
Question by:easycapital
• 3
• 2
5 Comments

LVL 93

Accepted Solution

Patrick Matthews earned 2000 total points
ID: 26158511
>>Is there a trick when calculating the IRR when all the periods are negative.

No.  If all of the cash flows are negative, then there is no solution.

Remember what IRR is: the discount rate such that, when it is applied to all of the cash flows, produces an
NPV of zero.  If all of the flows are negative, then there is no such discount rate.

>>I understand that there must be both positive and negative numbers in the periods in order for it to work,
>>but then how does one decide between two "bad" projects? - Using the IRR function of course

The right answer is, no matter what sign any of the cash flows has, to use NPV instead of IRR.  I will repeat
here my standard screed against IRR:

I urge you to be very careful in using internal rates of return.  IRR--the finance construct itself, and not just the
Excel functions IRR(), XIRR(), and MIRR()--is, simply put, not reliable, and prioritizing investment decisions
on an IRR basis can lead to bad decision-making.  You should use net present value (NPV) instead.

1) Depending on the nature of the cash flows, there can actually be more than one IRR. (The number of IRR
results will match the number of times the stream of cash flows changes signs.)

2) Some scenarios can have very high IRR but actually have negative NPV using any rational discount rate.

Illustrating both...  Consider the following cash flows:

Today, -1,600
1 year from now, 10,000
2 yrs from now, -10,000

This stream actually has two IRRs: 25%, and 400%, both of which sure look attractive.  Yet, any reasonable
discount yields a negative NPV.

Bottom line: trust NPV, and eschew IRR.

Regards,

Patrick
0

LVL 93

Expert Comment

ID: 26158537
JP,

Even if you lay aside for the moment the objection that IRR is simply inferior to NPV, you still have a serious
problem with the Excel IRR functions: Excel will return a single IRR, while the number of actual solutions
can be greater than 1.

Thus, even if you accept IRR as a valid criterion, depending on your actual situation Excel may not be the
best tool to even calculate IRR.

Patrick
0

Author Comment

ID: 26158554
Patrick,

Thanks for the detailed answer.  I agree with you, it is just that in some instances it is important to see the % in the return.

I agree with you in that the IRR is looked down in many cases.

How do you suggest the IRR should be handled or analyzed or presented, In the case where there is more than one IRR?  So, if there is a new IRR everytime the sign changes then when do we do with all the resulting IRR's?

Thanks,
JP
0

LVL 93

Assisted Solution

Patrick Matthews earned 2000 total points
ID: 26158672
>>How do you suggest the IRR should be handled or analyzed or presented, In the case where there is
>>more than one IRR?  So, if there is a new IRR everytime the sign changes then when do we do with
>>all the resulting IRR's?

I'm afraid there is no truly satisfying answer.  Per the help documentation, Excel uses an iterative process
to find the IRR; if after 20 iterations the function is not closely converging on an answer, it returns an error.

IRR (as well as XIRR) has an optional Guess argument that provides a seed value for the algorithm; if
the guess is omitted, then Excel uses 0.1.

In my example above, assume the following values are in A1:A3...

-1600
10000
-10000

A formula of =IRR(A1:A3) will return 0.25.

To get the formula to return the other possible value, 4, you need to provide a good value for the Guess.  That,
however, is a bit tricky.  For example, adding a Guess value of 2 through 8 will get you the 4 solution, but other
values will not.

In the above, you are solving the equation for n, where n is really 1 / (1 + IRR):

-1600 + 10000n - 10000n^2 = 0; or to go into the standard order for quadratic equations:
-10000n^2 + 10000n - 1600 = 0

You can use the quadratic formula (http://en.wikipedia.org/wiki/Quadratic_formula#Quadratic_formula) to find
the two solutions.  (In this case, the a, b, anc c values are -10000, 10000, and -1600, respectively.)

When you apply the quadratic formula, you end up with these expressions to find the IRRs:

=1/((-A\$2+SQRT(A\$2^2-4*A\$3*A\$1))/(2*A\$3))-1            --->      4
=1/((-A\$2-SQRT(A\$2^2-4*A\$3*A\$1))/(2*A\$3))-1            --->      0.25

Problem is, once the underlying formula is no longer quadratic in form, there is no formulaic way to determine
the answer, and you are left with relying on some iterative process.
0

Author Closing Comment

ID: 31671795
Thanks Patrick, always looking forward to hearing your thoughts.  I guess I will "have" to make the project profitable one month :).

Thanks for all the good info.
Juan Peralta
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
###### Suggested Courses
Course of the Month12 days, 21 hours left to enroll

#### 578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.