Excel practice · Finance Functions

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

  1. Find a car loan's monthly payment
  2. Find a 30-year mortgage payment
  3. Find the deposit needed to hit a savings goal
  4. Find a lease payment with a balloon payment at the end

The exercises

1. Find a car loan's monthly payment Easy

You're financing an $18,000 car at 0.4% interest per month, to be paid off over 48 months. Find the monthly payment.

AB
1Loan amount18000
2Monthly rate0.004
3Months48
4Monthly 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.

2. Find a 30-year mortgage payment Medium

A $240,000 mortgage at 0.3% monthly interest (about 3.6% APR), paid off over 360 months (30 years). Find the monthly payment.

AB
1LenderPacific Home Loans
2Loan amount240000
3Monthly rate0.003
4Months360
5Monthly 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.

3. Find the deposit needed to hit a savings goal Medium

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?

AB
1Savings goal10000
2Monthly rate0.001
3Months24
4Monthly 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?"

4. Find a lease payment with a balloon payment at the end Medium

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.

AB
1Equipment price15000
2Balloon due at end4000
3Monthly rate0.0025
4Months36
5Monthly 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 →

More finance functions practice

All Excel practice exercises · Excel test prep