(IRR()... I get an error message (#NUM!).

5000
-2400
3034
222
-5743
9000

Hi. Just some thoughts.
Without doing any math, we see that you have a lot of cash flows in the
same direction as your first payment. This indicates a large negative
return. Large negative returns cause problems math-wise for reasons
below. I would make sure my data was correct. (ie is it -9000?)

First...Excel's IRR has some "known" issues. It is not really that
good. It has been brought up before where it fails, when it really
shouldn't.

For the sake of a demo, let's change the 9000 to 2500.
If we use IRR(data, -.41) we get -47%
If we use IRR(data, -.40) we get #num!
If we use IRR(data, -.39) we get -26%

Well, Excel really shouldn't return an error. All it had to do was
decide on either number. Again, not a great algorithm by Excel.

If we solve the basic equation, there are 3 real solutions, and 2 complex:

-1.9410621,
-0.47409967,
-0.25722072,
-0.92380875 - 1.1637655 I,
-0.92380875 + 1.1637655 I

Excel couldn't handle the switch, and returned an error.
Let's look at the basic underlying equation:

5000 - 2400/(r + 1) + 3034/(r + 1)^2 + 222/(r + 1)^3 - 5743/(r + 1)^4 +
2500/(r + 1)^5

When we use Excel, or perhaps a better method like the Newton method to
arrive at a solution that set the above to zero, what happens when we
have negative values?
As 'r approaches -1, the values trend toward infinity. (error for Excel)
The slope is very high, and can be an issue in a custom function if not
careful.
Just on the other side of -1 the slope is approaching infinity, but in
the opposite direction.
Excel has other issues, and this is another one it can not handle very well.
This is why a solution to IRR in this case is not really meaningful.
If we put back your 9000, and solve the equation, I show only 1 real
solution using machine precision.
However, I don't think a rate of -2.0996 is meaningful.

-2.0996540900510077,
-1.0761593698610863 - 1.2447059494677744*I,
-1.0761593698610863 + 1.2447059494677744*I,
-0.1340135851134098 - 0.5501448705825116*I,
-0.1340135851134098 + 0.5501448705825116*I


= = =
HTH :)
Dana DeLouis


gotahavit wrote:

"Ron Rosenfeld" wrote:

On Thu, 22 Jan 2009 14:21:01 -0800, gotahavit
wrote:

I have a question regarding IRR.
I am trying to figure out the IRR for an investment (actually, this has come
up a number of times in the past). Unfortunately, it is not one of those
simple cases where the initial investment is negative and all subsequent cash
flows are positive. There are a couple of times when the cash flow signs
switch. I understand the multiple IRR issue, but it still doesn't help that I
cannot get a return number from Excel.
I have to imagine that these cases, where the negatives and positives switch
more than once, is common. It is difficult to just say "well, there is no
IRR". I still need to know what the rate of return would be. What can I do?
Please help.

Thanks.
Why do you think there is a requirement for Excel's IRR function that it must
be the case that "the initial investment is negative and all subsequent cash
flows are positive." ??

What does "cannot get a return number from Excel" mean? Do you get a blank? Do
you get an Error message? If so, what is the message or error value?

HELP states that there must be at least one positive and one negative cash
flow; but I see no requirement there such as you write. And I've analyzed
numerous cash flows where the "signs switch".

Either you are using an inappropriate "guess", or there is something peculiar
about the values you are using. But without more specific information, it
would be difficult to advise you further.
--ron

I already replied to one of the other people who replied to my question, but
when I use the following CF stream, I get an error message (#NUM!).
5000
-2400
3034
222
-5743
9000

I have had the same problem in the past with different CF numbers, so when I
tried to look into it, I thought it was because of the multiple IRR issue
that arises from switching signs.
Can you get IRR to work using these numbers?

Thanks.