The NPER function
Returns how many payment periods it takes to pay off a loan, or to reach a savings goal, with fixed payments and a fixed interest rate.
Syntax
NPER(rate, pmt, pv, [fv], [type])- rate
- The interest rate per period. For monthly payments on an annual rate, divide it by 12.
- pmt
- The payment each period, negative for money paid out.
- pv
- The amount today, such as the loan amount.
- fv (optional)
- The amount to reach at the end. Left out, 0, a loan paid off in full.
- type (optional)
- 0 or left out if payments are made at the end of each period, 1 if at the start.
Examples
D1
=NPER(1B2/12,2-B3,3B1)| A | B | C | D | |
|---|---|---|---|---|
| 1 | Loan amount | 15000 | 56.18429076 | |
| 2 | Annual interest rate | 0.05 | ||
| 3 | Monthly payment | 300 |
In plain words: How many 2monthly payments of the amount in B3 pay off 3the loan in B1 at 1the annual rate in B2, split into months.
B1=ROUNDUP(NPER(0.05/12,-300,15000),0)
| A | B | |
|---|---|---|
| 1 | 57 |
B1=NPER(0.04,0,-10000,20000)
| A | B | |
|---|---|---|
| 1 | 17.67298769 |
Practice NPER
Questions
Why does NPER return a decimal?
The last payment is smaller than the others. Round up with ROUNDUP to get the number of payments you actually make.
Why does NPER return
The payment is too small to ever pay off the loan, because it does not cover the interest, or the signs of the payment and the loan amount are the same. The payment should be negative and the loan positive.