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.
|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.
|#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.|