RATE & NPER practice exercises
3 graded problems with realistic data, all medium.
Two of the pieces PMT normally takes as known inputs, solved for instead: the interest rate itself, or how many payments it takes to pay something off. The 3 exercises below come from LearnExcel's practice library. Each one gives you a realistic table, a cell to fill, and a hint if you get stuck. Read them here, then solve them in the interactive grid, where your formula is checked the moment you press Enter.
New to the function? Read the RATE & NPER guide first: syntax, a worked example and the mistakes people make.
In this set
- Find the interest rate behind a known payment
- Use RATE to compare two loan offers
- Find how long it takes to pay off a balance
The exercises
A loan of $20,000 is being paid off with $386.66 monthly payments over 60 months. What monthly interest rate does that imply?
| A | B | |
|---|---|---|
| 1 | Loan amount | 20000 |
| 2 | Monthly payment | 386.66 |
| 3 | Months | 60 |
| 4 | Monthly rate | ? |
Enter a formula in B4.
Show a hint
RATE(nper, pmt, pv) solves backward for the rate. The payment leaves your account, so it's negative: =RATE(B3,-B2,B1).
RATE is the one you reach for when a lender quotes you a payment and a term but not a rate, which happens more often than you'd expect.
Two lenders both offer a $15,000 loan over 48 months. Lender A's payment is $355.78/month. Find the monthly rate Lender A is actually charging.
| A | B | |
|---|---|---|
| 1 | Lender | Lender A |
| 2 | Loan amount | 15000 |
| 3 | Monthly payment | 355.78 |
| 4 | Months | 48 |
| 5 | Monthly rate | ? |
Enter a formula in B5.
Show a hint
=RATE(B4,-B3,B2), same shape, once you have the rate for each lender, they're directly comparable.
A "low monthly payment" can hide a high rate if the term is stretched out; backing out the real rate with RATE is how you catch that.
A $10,000 credit card balance charges 1% interest per month. If you pay $200 every month, how many months will it take to pay it off?
| A | B | |
|---|---|---|
| 1 | Balance | 10000 |
| 2 | Monthly rate | 0.01 |
| 3 | Monthly payment | 200 |
| 4 | Months to pay off | ? |
Enter a formula in B4.
Show a hint
NPER(rate, pmt, pv) solves for how many periods it takes. The payment is an outflow, so it's negative: =NPER(B2,-B3,B1).
This is the sobering real-world use of NPER: a fixed minimum payment against a balance that's still charging interest takes a lot longer to clear than the payment amount alone suggests.
Solve these in a real grid
Type your formula, get graded instantly. Your first 10 exercises are free, no signup.
Start the RATE & NPER exercises →