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.

REFERENCES

"Function Reference," version 4.0, page 291 "Function Reference," version 3.0, pages 161-162

KBCategory: kbusage
KBSubcategory:

Additional reference words: 5.003.00 4.00 M_eXceL 4.00a



THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: September 12, 1996
©1997 Microsoft Corporation. All rights reserved. Legal Notices.