XL: IRR Returns #DIV/0! Instead of #NUM! If No IRR Exists (30567)


The information in this article applies to:

  • Microsoft Excel 2000
  • Microsoft Excel 2002
  • Microsoft Excel 97 for Windows
This article was previously published under Q30567

SYMPTOMS

If you use the IRR function on a set of data that does not have an IRR (internal rate of return), the function sometimes returns a #DIV/0 error when it should return a #NUM! error.

NOTE: IRR is calculated by setting the NPV formula equal to 0 (zero) and solving for the interest rate.

CAUSE

In this formula, the future cash flows are divided by a discount factor. If the discount factor becomes too small, Excel may consider it a 0 (zero). If Excel considers this number a zero, it produces a #DIV/0 error.
Modification Type:MajorLast Reviewed:5/13/2003
Keywords:kbprb KB30567
©2002 Microsoft Corporation. All rights reserved.