IRR

[gotahavit]

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.

 

[ryguy7272]

I tried an example with CF switching from positive to negative and back and
forth; IRR and MIRR worked for me just fine. What results are you expecting?
Have you tried =XIRR().

HTH,
Ryan---

 

[Ron Rosenfeld]

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

 

[gotahavit]

I tried the following CFs:
5000
-2400
3034
222
-5743
9000

When I use IRR, I get #NUM!
Does it work for you?

 

[gotahavit]

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.

 

[Dana DeLouis]

when I use the following CF stream, I get an error message (#NUM!).

Hi. For me, I find it easier to switch the point of reference.
Change the sign of all your numbers so that the first payment is an
outflow (negative). Unless I am mistaken, you have a large negative return.
If you last payment is changed from -9000 to -2500, you will see a
negative return of about -47% (Both from Excel, and a custom function)
When you start decreasing your value to -9000, the return goes to a very
large negative number and causes an error.

= = =
HTH :>)
Dana DeLouis

 

[ryguy7272]

I tried a few combinations of things; large initial +CF and subsequent -CF,
then large initial -CF and subsequent +CF. As the negative IRR increased
more and more, I encountered errors. I'm with Dana on this! The result is
hugely negative. Bad investment. Excel is not telling you how bad it is,
but it's very bad and you should avoid it at all costs. I couldn't get
greater than -50% return. Anyway, why would you want to! -50% return;
anyone interested? DJIA vaporized? Economy is f****d! I think the thing
speaks for itself.

HTH,
Ryan---

 

[Ron Rosenfeld]

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?

I do not believe that you can.

Others may be able to explain this better, but I'll try.

IRR is the rate for which the NPV is zero. However, certain types of cash
flows have multiple instances where the NPV is zero (more likely with
"switching sign" data) and so IRR is not valid.

If you were to graph NPV vs discount rate for the above series of cash flows,
you would see that the graph crosses the 0 line at two points -- a highly
negative and a highly positive rate.

You could certainly analyze your investment by looking at total outflow vs
total inflow, but I don't think you can do a valid IRR with that data.
--ron

 

[Ron Rosenfeld]

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.

I found this interesting discussion regarding IRR's:

http://members.tripod.com/~Ray_Martin/DCF/nr7aa003.html
--ron

 

[Ron Rosenfeld]

As a matter of interest, what are the two values of discount rate which give
NPV = 0?

They are very large numbers -- approximately + or - 5368709120%

But I suspect it is the complex nature of the cash flows that make IRR
inappropriate.
--ron

 

[Dana DeLouis]

(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

 

[Dana DeLouis]

They are very large numbers -- approximately + or - 5368709120%

Hi. I may be wrong, but here goes.
Excel's IRR can not handle this, but if we tell it to start a hair to
the left of the discontinuity line, we can get the only real solution.
This is not the compete function I use, but should work.
Again, the solution is not very meaningful.

? MyIRR([A1:A6],-1.001)

-2.09963276604736

Function MyIRR(Rng, Optional Guess As Double)
'// = = = = = = = = = = = = = = = = =
'// Assumes a vertical array of data
'// By: Dana DeLouis
'// = = = = = = = = = = = = = = = = =

Dim v
Dim dr()
Dim R
Dim OldRte
Dim Ct As Long
Dim J As Long

With WorksheetFunction
'Make 1-Dimensional
v = .Transpose(Rng.Value)
For J = 1 To UBound(v)
v(J) = CSng(v(J))
Next J

'Make an exact derivative
ReDim dr(1 To UBound(v) - 1)
For J = 1 To UBound(dr)
dr(J) = -J * v(J + 1)
Next J

If IsMissing(Guess) Then
R = 0.1
Else
R = Guess
End If

Do
OldRte = R
R = R - .SeriesSum(1 + R, 0, -1, v) / _
.SeriesSum(1 + R, -2, -1, dr)
Ct = Ct + 1
Loop While OldRte <> R And Ct <= 40
End With

MyIRR = R
End Function

Again, Excel's IRR can not do this. :>~
= = =
HTH
Dana DeLouis

 

[Dana DeLouis]

OOps...I didn't see the program caution (other program). It is very
rare that we go 40 loops. It is usually 7-8.
I went back and changed it to 50.

Loop While OldRte <> R And Ct <= 50

The loop is there to prevent cycling between two ending solutions due to
precision.


Now with:
? MyIRR([A1:A6],-1.001)

-2.09965409005101

with checks with the only real solution:

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

Again, I told it to start a hair to the left of the discontinuity line.
= = =
Dana DeLouis

 

[joeu2004]

If you were to graph NPV vs discount rate for the
above series of cash flows, you would see that the
graph crosses the 0 line at two points -- a highly
negative and a highly positive rate.

How did you use a "highly negative" discount rate?

The terms of the NPV formula include the factor 1/(1+i)^n. If i =
-100%, the factor is incalculable. If i < -100%, we can calculate the
factor only for even-numbered cash flows.

 

[joeu2004]

Oops....

The terms of the NPV formula include the factor 1/(1+i)^n.
If i = -100%, the factor is incalculable.  If i < -100%,
we can calculate the factor only for even-numbered cash flows.

That's only half right (i = -100%). No problem with i < -100% as long
n = 0 and n >= 1.

(When I say "no problem", I mean the factor can be calculated. I'm
not sure it makes sense for the sign of the denominator to alternate.)

 

[Ron Rosenfeld]

How did you use a "highly negative" discount rate?

The terms of the NPV formula include the factor 1/(1+i)^n. If i =
-100%, the factor is incalculable. If i < -100%, we can calculate the
factor only for even-numbered cash flows.

I just inserted a negative number into the cell reference I used for the RATE
argument in the NPV function:

=NPV(cell_ref,$A$1:$A$6)
--ron

 

[Ron Rosenfeld]

How did you use a "highly negative" discount rate?

The terms of the NPV formula include the factor 1/(1+i)^n. If i =
-100%, the factor is incalculable. If i < -100%, we can calculate the
factor only for even-numbered cash flows.

I just put a negative value into the NPV formula. I believe others have posted
a better analysis of the problem than my feeble (and inaccurate) attempt.
--ron

 

[joeu2004]

Errata....

Oops....



That's only half right (i = -100%).  No problem with
i < -100% as long n = 0 and n >= 1.

I should have said __integral__ n >= 1. I don't know how we would
compute (1+i)^n without using log(1+i) for non-integral n.

I should probably note that my concerns about 0<n<1 and non-integral n
relate more to the computability of XIRR. Although the discussion
here is about IRR, my mind keeps segueing to XIRR.

 

[Ron Rosenfeld]

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.

I think I misunderstood your problem.

If your problem is to generate an annualized return figure for data which does
not have an IRR that Excel can compute, I suppose you'd have to generate some
kind of approximation.

For your series of cash flows, it appears as if you are "investing" a total of
$8143 and getting back a total of $17,256 (negative vs positive flows). So
your gross return is $9,113 over five time-periods.

It is interesting that if you ignore the first $5000 payment you receive, the
IRR on the remaining cash flow calculates to 44%

--ron