The NPER function is a Financial formula that calculates and returns the number of repayment periods of a loan, based on constant payments and interest rate. In this guide, we’re going to show you how to use the Excel NPER function and also go over some tips and error handling methods.
Supported versions
- All Excel versions
Excel NPER Function Syntax
Arguments
| rate | The interest rate for the loan. |
| pmt | The constant payments for the loan. |
| pv | The present value, or the total value of all loan payments today. |
| [fv] | Optional. The future value, or a cash balance you want after the last payment is made. Default value is 0 (zero). |
| [type] |
Optional. When payments are due. 0 = end of period. (Default) 1 = beginning of period. |
Examples
Without future value and due date type
The NPER function has 3 required arguments: rate, pmt and pv. Using these arguments, you can calculate the number of payment periods for constant payments. The function assumes payments are paid at the end of each period, and the loan is to be paid in full.
For example, the following formula calculates the number of periods for a loan amount $100,000 with a 6% annual interest, which will be paid for the amount of $3,042.69 each period.
With future value and due date type
You can specify a target cash balance for after the last payment is made, and whether payments are to be made at the beginning of each period or the end.
The following formula is the extended version of the first example. It calculates same loan amount for the same period amount with the same interest. The difference is that there is $50,000 left after the last payment and payments are made at the beginning of each period.
