How to use NPER Function in Excel

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:

NPER Function - How to use NPER Function in Excel

Syntax:  =NPER(B1/12,B2,B3,B4,1)

Result:

NPER Function in Excel - How to use NPER Function in Excel

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.
READ:  How to use SLN Function in Excel

Add a Comment

Your email address will not be published. Required fields are marked *