IRR & #DIV0!
David wrote:
I've got a series of numbers in a row from which I need to know the IRR.
Because the first number is positive the function is returning the #DIV0 error.
IRR() requires that __some__ cash flows be negative and positive, but
it does __not__ require that the first cash flow be negative. For
example, IRR({2000,-10000,4000,5000}) works just fine (in Office Excel
2003).
In my experience, the #DIV/0! error usually has the same meaning as the
#NUM! error, namely: the IRR() algorithm hit a divide-by-zero
condition before it (would have) exhausted the iteration limit of
20(!).
The remedy might be to pick a good "guess", the 2nd IRR() parameter.
However, that does not always work, either. For example,
IRR({-2000,10000,4000,5000}) produces 445.10%, but
IRR({10000,-2000,4000,5000},guess) refuses to provide a solution, even
when I provide it (-158.34%!) in the "guess" parameter.
(I determined the solution manually by using the sum of the PV's and
trial rates.)
|