Excel: IRR Function Returns Finite Result for Infinite Rate

If a series of values that should result in an infinite rate of return is passed to the IRR function, Microsoft Excel returns a very large value, rather than an error indicating that the result was incalculable.

When calculating the internal rate of return for a series of periodic cash flows, the values used may result in an infinite rate of return. For instance, if the initial investment is $0 and the first income is $1, the rate of return compared to the initial investment would be infinitely large. Under this circumstance, an error would be expected, but instead the IRR function returns a nonerror value.

Microsoft has confirmed this to be a problem in Excel for the Macintosh versions 2.2 and 3.0, Excel for Windows versions 2.2 and 3.0, and Excel for OS/2 versions 2.2, 2.21, and 3.0. We are researching this problem and will post new information here as it becomes available.

Reference(s):

"Microsoft Excel Function Reference," for the Macintosh, version 3.0, pages 134-5