Using the Excel NPER Function


The Excel NPER function is a financial function that returns the number of periods for an investment based on periodic, constant payments and a constant interest rate.


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

Syntax Breakdown

Required. Interest rate per period.

Required. Payment that is made each period. The payment cannot change over the life of the loan or investment. The payment figure normally contains interest and principal, but no other taxes or fees that may be associated with a loan.

Required. Present value that a series of future payments is worth today. PV should be entered as a negative number.

The example below shows what happens if PV is not entered as a negative value. As you see, this produces a very different result.

Excel NPER PV Example

Optional. The future value desired after the last payment is made. If omitted, FV is assumed to be zero (0).

Optional. Argument indicating when payments are due. Use 1 for beginning of the period and zero (0) for end of the period.

Usage Notes

NPER can be used to get the number of payments for a loan, given the amount, interest rate, and the periodic and constant payment amount.

Rates and Periods
Take care when working with rates and periods that they are consistent. For example, if you supply rate as an annual rate, such as 10%, be sure to take 10%/12 to find the monthly rate so that NPER is returned in months.