Microsoft KB Archive/104626

= Microsoft Knowledge Base =

Excel: NPER Function Returns #NUM! Error Value
Last reviewed: September 12, 1996

Article ID: Q104626

The information in this article applies to:


 * Microsoft Excel for Windows, versions 3.0, 4.0, 4.0a, 5.0
 * Microsoft Excel for OS/2, version 3.0
 * Microsoft Excel for the Macintosh, version 3.0, 4.0, 5.0

SUMMARY
The NPER function in Microsoft Excel will return a #NUM! error value if the payment made each period is less than or equal to the interest charged during the first period.

MORE INFORMATION
The NPER function returns the number of periods for an investment based on periodic, constant payments and a constant interest rate.

Steps to Reproduce Behavior
On a new worksheet, enter the following values:

A1: 1000 A2: -20000 A3: 5.50% A4: =NPER(A3,A1,A2) where:


 * cell A1 contains the payment per period
 * cell A2 contains the present value of the debt
 * cell A3 contains the interest rate per period
 * cell A4 calculates the number of periods required to eliminate the debt

The NPER formula in cell A4 will return a #NUM! error value because the payment (1000) is less than the interest charged during the first period (-20000 * 5.5% = 1100). At this rate of payment, the debt will never be settled; instead, it will increase.

If you increase the payment in cell A1 to a value greater than 1100, the NPER formula will return a valid result.