Explainry Tutorials to Expert in IT

NPER function in Excel


If you took a loan with constant payments and a constant interest rate, the NPER function returns the number of periods required to pay off the loan. The function is one of the Financial function and was introduced in the Excel 2007 version.



The NPER function has the following arguments:

rate – Required. The rate of interest per period.

pmt – Required. The constant payments to be made during each period, usually, the argument contains principal and interest but no other fees or taxes.

pv – Required. The present value of the loan, a total value of all payment now.

[fv] – Optional. The future value of the loan after the last payment. If omitted, the default value of 0 is taken.

[type] – Optional – Either 1 or 0, indicates when the payment will due.

Type Timing
0 (zero) Payment at the end of the period.
1 Payment at the beginning of the period.


In the following scenario, the NPER function is used to calculate the number of payment periods required to pay back a loan with present value of $50,000, annual interest rate of 13% and yearly constant payments of $5,000.


Note that periodic payment for the loan is input as negative value because it is the outgoing payment.

Function Errors

Error Occurs
#NUM! If the specified future value will never be met or  the supplied interest rate or the constant payments are insufficient.
#VALUE! If either of the supplied argument is non-numeric.

Copyright © 2016 - 2020 Explainry.com | All Rights Reserved