The Formula Gym
XP 0
Streak 0

The PMT function in Excel

PMT works out the regular payment on a loan from the interest rate, the number of payments and the amount borrowed.

What PMT does

The rate and the number of payments must use the same period. For monthly payments on a loan with an annual rate, divide the rate by 12 and multiply the years by 12. Enter the loan as a negative number if you want the payment to come out positive. An optional fourth argument holds the value left at the end, which is useful for leases.

Syntax

=PMT(rate, nper, pv, [fv], [type])
rate
The interest rate for each payment period, such as the annual rate divided by 12.
nper
The total number of payments.
pv
The amount borrowed today. Use a minus sign to get a positive payment.
fv
Optional. The value still left at the end, such as a lease's final value.
type
Optional. 0 for payments at the end of each period, 1 for the start.

Worked example

AB
1Loan amount12000
2Annual rate0.06
3Years3
4Monthly payment365.06
B4 =ROUND(PMT(B2/12,B3*12,-B1),2)

The 6% annual rate becomes 0.5% a month and three years become 36 payments. ROUND tidies the answer to pence.

Now you try

A fresh exercise every time. Press Another exercise as often as you like.

Where you will use it

Finance

Work out loan, mortgage or lease payments before you commit.

Sales & Ops

Show customers what a finance deal costs each month.

Common mistakes

  • Using the annual rate with monthly payments. Divide the rate by 12 so it matches the number of payments.
  • Forgetting the minus sign on the loan, which makes the payment negative.
  • Mixing years and months. If you divide the rate by 12, nper must be in months.

The PMT course

  1. 1
    Monthly loan paymentFree practice below
    Practise
  2. 2
    Weekly or quarterly paymentsMembers lesson
  3. 3
    Total interest paidMembers lesson
  4. 4
    Lease with a final valueMembers lesson
  5. 5
    Payment for every loanMembers lesson

Common questions

How do I work out the total interest?

Multiply the payment by the number of payments and subtract the amount borrowed.

Does PMT include fees?

No. It only covers the interest rate and the amounts you give it.

Related functions

Unlock every lesson

Every function has a free lesson. Members get all the lessons, fresh exercises every time and the weekly leaderboard. £7 a month or £49 a year, and you can cancel at any time.

See membership