Erick:
Since the solution to an IRR problem is basically a root finding problem
it is useful to remember Descartes' rules of signs. This rule says that if
the sequence has just one sign change there will be a real positive root.
If there are multiple sign changes there may be as many roots as there are
sign changes or less. So in your case there are two sign changes
therefore there may be 2 1 or 0 roots. In your case there are no roots.
You can see this by graphing the function. It appears, within the numbers
I used that it reaches a minimum at 56% (value is 10,821) after that the
function begins to increase and at any positive large rate it appear to
reach a value of 19,400+/- for all discount rates above about 500%.
Finally note that the NPV function in Excel (and in all other spreadsheets
I have seen) is inconsistent with the IRR function and does not in fact
compute the NPV the way you think. It should more correctly be labeled the
PV function. So you should compute the NPV by using the formula as shown
below or another equivalent form. Notice that the NPV function should not
include the zero date flow. You will understate the true NPV.
Example: (I assume following usually convention that first flow
was the zero period flow.)
Row/Col D E F G H
16 .1
17 Period CashFlow Rate NPV
18 0 19,415 0.00% 145,191 <=NPV(F18,$E$19:$E$33)+$E$18
19 1 1,821 10.00% 58,968
20 2 -18,856 20.00% 28,441
21 3 -18,856 30.00% 16,786
22 4 -18,856 40.00% 12,383
23 5 6,688 50.00% 10,984
24 6 32,500 60.00% 10,868
25 7 32,500 70.00% 11,291
26 8 32,500 80.00% 11,918
27 9 32,500 90.00% 12,594
28 10 13,085 100.00% 13,251
29 11 0 110.00% 13,859
30 12 0 120.00% 14,409
31 13 0 130.00% 14,900
32 14 0 140.00% 15,337
33 15 30,750 150.00% 15,723
Rate NPV
56.1593627% 10,821
3608650707931670.00% 19,415
Pieter Vandenberg
: Dear Norman:
: Thank you for your interest in my problem.
: Here are the cash flows starting with the earliest one
: first:
: 19,415
: 1,821
: (18,856)
: (18,856)
: (18,856)
: 6,688
: 32,500
: 32,500
: 32,500
: 32,500
: 13,085
: 0
: 0
: 0
: 0
: 30,750
: Thanks again,
: Erik Schneider
:>-----Original Message-----
:>Hi Erik!
:>
:>We need a bit of data on the cash flows that are
: producing the error.
:>
:>The most common cause of #DIV/0! is the wrong signage of
: the cash
:>flows. But you can get it with very large differences
: between the
:>(absolute) sums of negatives and positives.
:>
:>However, try it with a guess rate of -0.9
:>
:>--
:>Regards
:>Norman Harker MVP (Excel)
:>Sydney, Australia
:>
[email protected]
:>:>> Does anyone have any experience with Excel returning the
:>> DIV/0! error when using the IRR function? I also think
:>> that it seems to return implausible IRRs when the data
:>> gets closer and closer to creating a DIV/0! error. My
:>> data clearly has an IRR that Excel should be able to
:>> calculate!
:>
:>
:>.
:>
