NPER is also known as the number of payment periods for a loan taken, it is a financial term and in excel we have an inbuilt financial function to calculate NPER value for any loan, this formula takes rate, payment made, present value and future value as input from a user, this formula can be accessed from the formula tab or we can type =NPER().
Syntax:= NPER(rate,pmt,pv,[fv],[type])
The NPER function syntax has the following arguments:
- Rate Required. The interest rate per period.
- Pmt Required. The payment made each period; it cannot change over the life of the annuity. Typically, pmt contains principal and interest but no other fees or taxes.
- Pv Required. The present value, or the lump-sum amount that a series of future payments is worth right now.
- Fv Optional. The future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (the future value of a loan, for example, is 0).
- Type Optional. The number 0 or 1 and indicates when payments are due.
| Set type equal to | If payments are due |
|---|---|
| 0 or omitted | At the end of the period |
| 1 |
At the beginning of the period |
Example: Let’s look at some Excel NPER function examples and explore how to use the NPER function as a worksheet function in Microsoft Excel:
Syntax: =NPER(B1/12,B2,B3,B4,1)
Result:
Based on the Excel spreadsheet above, the following NPER examples would return:
Syntax: =NPER(B1/12,B2,B3,B4,1)
Result: 61.29281735
Syntax: =NPER(B1/12,B2,B3,B4)
Result: 61.68632051
Syntax: =NPER(B1/12,B2,B3)
Result: -9.611667598
Note:
- NPER Function in excel can be applied to find the number of months to clear the loan.
- NPER Function in excel assumes standard interest rate, PMT.
- All the outgoing payments should be supplied as negative numbers.
- All the arguments should be numerical values. If any non-numerical value is found it will return the result as #VALUE!.
- [fv], [type] is not a mandatory argument. If omitted it will treat as zero by default.
