#NUM errors when calculating IRR

[sinclairtribe]

I am trying to calculate a few IRRs that are returning #NUM. I can't
figure out why. I've been trying different guesses and I've been
tweaking the number of iterations and max change in Tools | Options |
Calculation, and yet I still get #NUM. Can anyone take a look and
figure out what I'm doing wrong?

Here are the three cash flows for which I can't get an IRR:

Cash Flow 1:
$ (14,038,400) $ 9,419,009 $ (172,248) $ 6,172,725
$ (269,064) $ 2,955,516 $ (269,064) $ (5,730,809)
$ (79,376)


Cash Flow 2:
$ (9,826,880) $ 6,500,691 $ (120,573) $
4,433,082 $ (188,344) $ 2,391,228 $ (188,344) $
(4,011,566) $ (55,563)


Cash Flow 3:
$ (4,211,520) $ 2,918,318 $ (51,674) $
1,739,643 $ (80,719) $ 564,288 $ (80,719) $
(1,719,243) $ (23,813)


I would REALLY appreciate any help anyone could provide!

Thanks in advance,
Adam Sinclair

 

[davesexcel]

Here's a post I just found, similar to your question

http://tinyurl.com/j6zs3

 

[vandenberg p]

Hello:

With the numbers you gave for the first one trying all rates from
-90% up to 2060% there is no answer. That's what the #num is
telling you. Why? For the same reason you will not
be able to find the value of X in the following:

6X^2 - 10X + 5 = 0

Look up Descartes rule of signs. There are 6 sign changes.
Therefore the solution may have as many as 6 answers or as few as zero.

Pieter Vandenberg

(e-mail address removed) wrote:
: I am trying to calculate a few IRRs that are returning #NUM. I can't
: figure out why. I've been trying different guesses and I've been
: tweaking the number of iterations and max change in Tools | Options |
: Calculation, and yet I still get #NUM. Can anyone take a look and
: figure out what I'm doing wrong?

: Here are the three cash flows for which I can't get an IRR:

: Cash Flow 1:
: $ (14,038,400) $ 9,419,009 $ (172,248) $ 6,172,725
: $ (269,064) $ 2,955,516 $ (269,064) $ (5,730,809)
: $ (79,376)


: Cash Flow 2:
: $ (9,826,880) $ 6,500,691 $ (120,573) $
: 4,433,082 $ (188,344) $ 2,391,228 $ (188,344) $
: (4,011,566) $ (55,563)


: Cash Flow 3:
: $ (4,211,520) $ 2,918,318 $ (51,674) $
: 1,739,643 $ (80,719) $ 564,288 $ (80,719) $
: (1,719,243) $ (23,813)


: I would REALLY appreciate any help anyone could provide!
:
: Thanks in advance,
: Adam Sinclair

 

[joeu2004]

Why? For the same reason you will not be able to find the
value of X in the following: 6X^2 - 10X + 5 = 0[.]
Look up Descartes rule of signs. There are 6 sign changes.
Therefore the solution may have as many as 6 answers or
as few as zero.

I do not believe that necessarily explains the #NUM errors.
IRR has no trouble computing the rate (2%) of the following
cash flow, despite 8 sign changes:

-100000
{10000,-1000} eight times
53435

 

[joeu2004]

I am trying to calculate a few IRRs that are returning #NUM.

IRR (Office Excel 2003) had no trouble computing the rate
(20.13% per period) for the second cash flow. I assume
the cash flows read left-to-right, top-to-bottom.

As for the first and third cash flows, IRR computes the rate
at which the NPV is zero. But in those cases, the NPV is
never zero for any rate. Ergo, the IRR cannot be computed.

If these are real-life cash flows, I wonder if the periods are
spaced unevenly. If so, you need to insert zero for the periods
when there are no cash flows, or use XIRR and actual dates
of each non-zero cash flow.

If this is a class exercise, it would be prudent to graph the
NPV for ranges of rates. For the first and third cash flows,
consider starting at -12% incrementing by 1% for 100 points.
You might notice something interesting for large positive
rates. But if you graph exceedingly (absurdly) large rates,
you will see that even then, NPV never quite reaches zero.
(Close, but no cigar.)


-----

 

[sinclairtribe]

Thanks, all! This is for a school exercise... the periods are evenly
spaced but the project throws off some irregular cash flows... I had
planned on showing NPVs for a couple of discount rates, but graphing
100 rates sounds like a great idea (if I can find the time).

 

[vandenberg p]

Read my answer carefully: It says that there can be as many
answer as sign changes or "less." As long as there is an answer
and the guess rate (either the implicit one or the supplied one) is
in the right area an answer is provided. But if the guess rate is
in the wrong area (in that case usually the root finder bumps the time and/or
try limit) or the cash flows have the wrong sequence or scale no answer will be
found and #num is returned. So whether you get an answer does depend
upon the actual sequence of numbers their size and the guess rate.
Your particular choice of numbers allowed the IRR to find a rate and report
it.

Since the Excel IRR (as all are) is a numerical root finding technique,
it produces only one answer, if it can find it, that is closest (in an
algorithm sense, since it does depend on the slope in addition to
actual numerical closeness) to the guess rate it uses. I don't know exactly
which algorithm it uses, but it is probably some version of Newton-Rhapson,
which is generally very fast and efficient.
Google "Newton-Rhapson" I got about 800 hits.

The following sequence of cash flows without a guess rate will
produce an answer of 25%.

Cash Flow-504.00 2862.00 -6070.00 5700.00 -2000.00

But with different guess rates produce a whole slew of different answers.

Guess IRR
00.00% 25.00%
10.00% 25.00%
20.00% 25.00%
30.00% 33.33%
40.00% 42.86%
50.00% 42.86%
60.00% 66.67%
70.00% 66.67%
80.00% 66.67%
90.00% 66.67%

It is easiest to demonstrate use a simpler equation that we can solve with the Quadratic Formula.

Take the following cash flows: -28.00 53.00 -8.00

Two sign changes, therefore there can be 0, 1 or 2 roots

Find the IRR using Quadratic Formula
-28+53/(1+r)^1-8/(1+r)^2=0
Multiply through by (1+r)^2
-28*(1+r)^2 + 53*(1+r)^1 -8 =0
Let X = (1+r)
-28*X^2+53*X-8=0

The quadratic formula:
1.727 =(-53-((53^2)-(4*(-28)*(-8)))^0.5)/(2*-28)
0.165 =(-53+((53^2)-(4*(-28)*(-8)))^0.5)/(2*-28)
0.727 =1.727-1
-0.835 =.165-1

It is easy in this form to see what the problem is. If you
change the the 53 to 29, IRR will return a #num. Why? Because
there is no solution. Descartes' rule tells that the is possible,
yet if we change 29 to 30 we get one answer (-50%).

Finally here is Descartes' Rule of Signs

"We can determine also the number of true and false roots
that any equation can have, as follows: An equation can have as
many true roots as it contains changes of sign, from + to - or
from - to +; and as many false roots as the number of times two + signs or two -
signs are found in succession."

Source: http://www.cut-the-knot.org/fta/ROS2.shtml
Also try: http://www.purplemath.com/modules/drofsign.htm

(e-mail address removed)
<[email protected]>
wrote: : "vandenberg p" wrote: :> Why? For the same reason you will not be able to find the
:> value of X in the following: 6X^2 - 10X + 5 = 0[.]
:> Look up Descartes rule of signs. There are 6 sign changes.
:> Therefore the solution may have as many as 6 answers or
:> as few as zero.

: I do not believe that necessarily explains the #NUM errors.
: IRR has no trouble computing the rate (2%) of the following
: cash flow, despite 8 sign changes:

: -100000
: {10000,-1000} eight times
: 53435

 

[joeu2004]

Read my answer carefully: It says that there can be as many
answer as sign changes or "less." As long as there is an answer
and the guess rate (either the implicit one or the supplied one)
is in the right area an answer is provided.

And read my answers carefully -- both of them.

First, your original response did not allude to the fact that
proper choice of "guess" might find a solution. I did not
want other readers to be left with the (wrong) impression
that simply because there are a lot of cash flow sign changes,
there would be no solution or it would be difficult for IRR
to find a solution. As you point out in your second response,
it all depends on how close the (default) "guess" is to any
one of the solutions.

Second, and more significantly, I said that the number of sign
changes was not "necessarily" the answer -- as in "necessary
and sufficient" -- as you seemed to imply. In fact, as I indicated
in my other response, the real problem is that there is __no__
solution, at least to 2 of the 3 examples. It would not matter
how close a "guess" you make. It has little to do with the
number of sign changes (albeit we might need more than 2 or
3 for the situation to arise).