Solved

# IRR function - project with all negative periods

Posted on 2010-01-01
1,124 Views
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

LVL 92

Accepted Solution

>>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 92

Expert Comment

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

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 92

Assisted Solution

>>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

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

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

## Write Comment

Please enter a first name

Please enter a last name

We will never share this with anyone.

## Featured Post

### Suggested Solutions

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

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

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

#### Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!