Months to pay off a loan with NPER
PremiumIn B5, work out how many monthly payments of 500 it takes to pay off the loan, with NPER. Use the monthly rate and give the payment and the loan opposite signs: NPER(B3/12,-B4,B2).
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Loan | Value | ||||
| 2 | Amount borrowed | 12000 | ||||
| 3 | Interest rate per year | 0.05 | ||||
| 4 | Monthly payment | 500 | ||||
| 5 | Months to pay off | |||||
| 6 | ||||||
| 7 | ||||||
| 8 |
Premium lesson
Unlock Loans: PMT and NPER and every other lesson
This is a premium lesson. Premium lets you solve it and every other lesson, take the timed mock tests, and earn certificates you can share on LinkedIn.
Why this works
NPER answers "how many payments?" from the rate per period, the payment and the amount. At 5% a year, 0.42% a month, payments of 500 clear 12,000 in about 25.3 months. In practice that's 26 payments, the last one smaller. Without the interest it would be exactly 24.
Common mistake: Giving the payment and the loan the same sign. NPER then returns #NUM!, because a loan that grows with every payment is never paid off.
The answer
- B5
=NPER(1B3/12,2-B4,3B2)
In plain words: How many 2monthly payments of the amount in B4 it takes to pay off 3the amount borrowed in B2 at 1the annual rate in B3, split into months.