IRR Function Error Messages

[Guest]

I am receiving several #DIV/0 and #NUM! error messages when trying to use the
IRR function. So far I have conducted basic troubleshooting, such as
rechecking the formula, ensuring there are no #DIV/0 or #NUM! errors in the
build-up of numbers, but nothing seems to work. Does anyone know other ways
of error-checking the IRR formula? Any other common issues that might cause
this problem?

 

[Ron Rosenfeld]

I am receiving several #DIV/0 and #NUM! error messages when trying to use the
IRR function. So far I have conducted basic troubleshooting, such as
rechecking the formula, ensuring there are no #DIV/0 or #NUM! errors in the
build-up of numbers, but nothing seems to work. Does anyone know other ways
of error-checking the IRR formula? Any other common issues that might cause
this problem?

NUM probably means that your "guess" is way off (or the functions assumed guess
of 0.1 is way off.

With regard to the DIV/0, have you tried Tools/Formula Auditing/Trace Error to
see where it comes from?

--ron

 

[joeu2004]

I am receiving several #DIV/0 and #NUM! error messages
when trying to use the IRR function.

Assuming no usage errors on your part (including propagating errors in
referenced cells), both errors indicate that you need a "guess"
argument -- or a better "guess" argument.

The Help page mentions only #NUM in this regard. But you can get #DIV/
0 if IRR() encounters an error internally before the number of
iterations is exhausted. For example, consider the cash flow sequence
of -1e6 followed by 1 nine times. First, try IRR() without any
"guess" argument; then, use a "guess" of -78%.

 

[Guest]

Thanks, Ron. Changing the guess helped for most of them, including some of
the DIV/0s. Unfortunately the auditing tool (trace error) hasn't helped as
there doesn't appear to be anything in the preceding formulas to generate a
problem.

Appreciate the tip.

CC

 

[Ron Rosenfeld]

Thanks, Ron. Changing the guess helped for most of them, including some of
the DIV/0s. Unfortunately the auditing tool (trace error) hasn't helped as
there doesn't appear to be anything in the preceding formulas to generate a
problem.

Appreciate the tip.

CC

Glad it helped. If you aren't getting any help from trace with the DIV/0
errors, then those, too, are probably being caused by an inappropriate guess.
--ron