PMT Function

Calculates the regular payment for a loan or savings plan.

Category

Financial

Available in

All versions, Microsoft 365

Returns

A payment amount (usually negative)

Taught hands-on in Financial Modeling · Financial Functions Toolkit module

Overview

PMT works out the fixed payment needed to pay off a loan — or reach a savings target — at a constant interest rate. Each payment covers that period's interest plus some of the balance.

Excel uses cash-flow signs: money you pay out is negative, money you receive is positive. Borrowing $45,000 (a positive amount coming in) gives a negative payment (money going out).

Syntax

=PMT(rate, nper, pv, [fv], [type])

Arguments

ArgumentRequired?What it does
rateYesThe interest rate per period (annual rate ÷ 12 for monthly payments).
nperYesThe total number of payment periods.
pvYesThe present value — the loan amount, or what a series of payments is worth today.
fvNoThe balance you want left after the last payment. Omitted = 0 (a loan paid off in full).
typeNoWhen payments fall: 0 or omitted = end of each period, 1 = start of each period.

Examples

Example 1 — Monthly payment on a van loan

Northstar is financing a $45,000 delivery van over five years at 6.5%.

B5fx=PMT(B1/12,B2*12,B3)
AB
1Annual rate6.5%
2Years5
3Loan amount$45,000
4
5Monthly payment-$880.48
=PMT(B1/12,B2*12,B3) Explain

The rate is divided by 12 and the years multiplied by 12 because payments are monthly. Wrap it in a minus sign — =-PMT(B1/12,B2*12,B3) — to show $880.48 as a positive number.

Example 2 — Comparing interest rates

How much does each extra point of interest add to the annual payment on a $250,000, 10-year loan?

B2fx=PMT(A2,10,250000)
AB
1RateAnnual payment
25%-$32,376.14
36%-$33,966.99
47%-$35,594.38

Example 3 — Saving towards a target

To have $100,000 for new equipment in three years, earning 4% a year, how much must Northstar set aside each month? The target goes in fv and pv is 0.

=PMT(4%/12,36,0,100000) Explain

Result: -$2,619.07 a month.

Common mistakes

  • Rate and periods in different units. Keep them matched: for monthly payments divide the annual rate by 12 and multiply the years by 12. Mixing an annual rate with monthly periods gives wildly wrong answers.
  • The payment is negative. That's correct — it's money going out. Put a minus in front of PMT or make pv negative if you'd rather see a positive amount.

Notes

  • Payment at the start of each month (type 1) is slightly lower: =PMT(6.5%/12,60,45000,0,1) returns -$875.73.
  • Related functions: IPMT, PPMT, CUMIPMT, CUMPRINC, NPER, RATE, PV.
Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.