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