PMT practice exercises
4 graded problems with realistic data, from easy to medium.
Calculates the fixed periodic payment on a loan (or the fixed periodic contribution to reach a savings goal) at a constant interest rate. The 4 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 PMT guide first: syntax, a worked example and the mistakes people make.
In this set
- Find a car loan's monthly payment
- Find a 30-year mortgage payment
- Find the deposit needed to hit a savings goal
- Find a lease payment with a balloon payment at the end
The exercises
You're financing an $18,000 car at 0.4% interest per month, to be paid off over 48 months. Find the monthly payment.
| A | B | |
|---|---|---|
| 1 | Loan amount | 18000 |
| 2 | Monthly rate | 0.004 |
| 3 | Months | 48 |
| 4 | Monthly payment | ? |
Enter a formula in B4.
Show a hint
PMT(rate, nper, pv) solves for the payment. Enter the loan amount as pv: =PMT(B2,B3,B1).
PMT is what turns a rate/term/principal into the one number everyone actually wants to know: what shows up on the bill every month. Excel returns it as a negative number because it's money leaving your account; that's not a mistake, it's the sign convention every finance function in Excel shares.
A $240,000 mortgage at 0.3% monthly interest (about 3.6% APR), paid off over 360 months (30 years). Find the monthly payment.
| A | B | |
|---|---|---|
| 1 | Lender | Pacific Home Loans |
| 2 | Loan amount | 240000 |
| 3 | Monthly rate | 0.003 |
| 4 | Months | 360 |
| 5 | Monthly payment | ? |
Enter a formula in B5.
Show a hint
Same shape as any PMT problem: =PMT(B3,B4,B2). A 30-year term is just a bigger nper.
A mortgage is the PMT example almost everyone eventually needs for real: the only things that change from a car loan are the size of the numbers.
You want to have $10,000 saved in 24 months. Your account pays 0.1% interest per month and you're starting from $0. How much do you need to deposit each month?
| A | B | |
|---|---|---|
| 1 | Savings goal | 10000 |
| 2 | Monthly rate | 0.001 |
| 3 | Months | 24 |
| 4 | Monthly deposit | ? |
Enter a formula in B4.
Show a hint
PMT's 3rd argument (pv) is what you start with: here that's 0. The goal goes in the 4th argument (fv) instead: =PMT(B2,B3,0,B1).
PMT isn't only for loans: flip which argument holds the number (pv for a balance you're paying off, fv for a target you're building toward) and it answers "how much do I save?" just as well as "how much do I owe?"
Leasing equipment priced at $15,000, with a $4,000 balloon payment still due when the lease ends in 36 months, at 0.25% interest per month. Find the monthly lease payment.
| A | B | |
|---|---|---|
| 1 | Equipment price | 15000 |
| 2 | Balloon due at end | 4000 |
| 3 | Monthly rate | 0.0025 |
| 4 | Months | 36 |
| 5 | Monthly payment | ? |
Enter a formula in B5.
Show a hint
The balloon is a future value still owed, so it goes in PMT's 4th argument; and like every pv/fv pair in these finance functions, the two point in opposite directions, so it needs the opposite sign from pv: =PMT(B3,B4,B1,-B2). Leaving part of the debt for the balloon is exactly why the monthly payment comes out lower than paying off the full $15,000.
Real leases, and loans with a payoff balance at the end, almost always use this 4-argument shape: the balloon lets the regular payment stay smaller.
Solve these in a real grid
Type your formula, get graded instantly. Your first 10 exercises are free, no signup.
Start the PMT exercises →