NPER Excel Formula

Author Zaheer    Category Formulas     Tags ,

Microsoft excel is the spreadsheet software commercialized and developed by Microsoft. It is part of Office Suite and available for both Windows and Macintosh system. Excel is originally designed for Macintosh system and had been ported with MS/DOS last 1987 because of the slow porting of LOTUS123 to Microsoft OS and since then, Excel improved its features and dominates the spreadsheet market commercially.

Excel can be set up both stand alone applications or as part of the Office Suite. It offers the latter full integration with PowerPoint, Word and Publisher. The application offer fundamental functions for data manipulation such as formulas and arithmetic operations, along with statistical functions, histograms, graphs and charts. The macro functions in Excel are designed to perform more complex data processing, use Visual Basics for Application, and subset of famous scripting language made by Microsoft.

There are lots of viable alternative to learn the Microsoft Excel functions, just remember that the moving platform is an investment of time and money. One of the Excel functions that are commonly use is the NPER Function.


Excel NPER calculates the number of period needed to pay off the loan, for specified constant periodic payments and constant interest rates.


= NPER (rate, pmt, PV, [fv], type)

Where, the argument is as follow:

  • Rate – interest for each period
  • PMT  – amount paid each period
  • PV     – present value of loan
  • FV     – optional argument that tells the future value of loans, after final  payment
  • When deleted, [fv] take in the default value of zero
  • [Type] – optional argument which defines whether payment is made at the beginning or end of the period

The kind of argument could have value zero or one, meaning:

0 – payment is made at the end of the period

1 – payment is completed at the start of the period

When [type] argument is deleted, it takes a default value of zero

NPER Function Error

If you acquire error in the NPER function, it is possibly be the following:

#NUM Errors – it happens when the specific future value would not meet for specified periodic interest rates and payments. You have to increase payments or reduce the interests’ rate to avail result.

#VALUE! – it happens if the supplied arguments aren’t recognized as numeric value.

Excel NPER function offers negative result, when the positive one is being expected. This trouble normally happens if the present value and specified periodic payments both have similar arithmetic sign. If the loan be paid off, the present value must be negative and payments must be positive.

With the above description of NPER function you now learn and understand its definition and usage. Most Microsoft training materials for functions are demonstrated on their site. It let the user learn the functions and follow the procedures are being carried out. Without training materials, it would have been so hard to use and learn the new functions to optimize work flow procedure. Everyone can learn comfortably the Excel NPER functions as well as its other functions.

Post comment