Re: IRR
- From: Dana DeLouis <delouis@xxxxxxxxxxxxx>
- Date: Sat, 24 Jan 2009 10:25:05 -0500
> (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
<gotahavit@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
I have a question regarding IRR.Why do you think there is a requirement for Excel's IRR function that it must
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.
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.
- Prev by Date: Re: Extracting h:mm:ss from text string
- Next by Date: Re: Making code less column-specific
- Previous by thread: Re: IRR
- Next by thread: Re: IRR
- Index(es):
Relevant Pages
|
