Excel: IRR and MIRR Return #NUM Error

ID: Q23601

The information in this article applies to:

SUMMARY

The IRR and MIRR functions must be supplied with an array of values and an estimated value from which the result can be calculated within 20 iterations; otherwise, the function returns the #NUM error (if an estimate is omitted, Microsoft Excel uses 10 percent as a starting point).

Two reasons the function may not converge to a result are:

  1. The estimate is not accurate enough for the function to converge within the required 20 iterations.


  2. The array contains values that cannot converge to a correct value.


For the functions to operate properly, the first value in the array must be a negative number. This first value is the initial investment. If this number is positive, the internal rate of return will be infinite.

If the estimated value is not accurate enough, you must guess higher or lower values until the function can converge.

Additional query words: 1.0 1.00 1.03 1.04 1.06 1.5 1.50 2.2 2.20 3.0 3.00 4.0 4.00 


Keywords          : 
Version           : 
Platform          : 
Issue type        :

Last Reviewed: March 16, 1999