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)
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
Arguments
| Argument | Required? | What it does |
|---|---|---|
| rate | Yes | The interest rate per period (annual rate ÷ 12 for monthly payments). |
| nper | Yes | The total number of payment periods. |
| pv | Yes | The present value — the loan amount, or what a series of payments is worth today. |
| fv | No | The balance you want left after the last payment. Omitted = 0 (a loan paid off in full). |
| type | No | When 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%.
| A | B | |
|---|---|---|
| 1 | Annual rate | 6.5% |
| 2 | Years | 5 |
| 3 | Loan amount | $45,000 |
| 4 | ||
| 5 | Monthly payment | -$880.48 |
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?
| A | B | |
|---|---|---|
| 1 | Rate | Annual payment |
| 2 | 5% | -$32,376.14 |
| 3 | 6% | -$33,966.99 |
| 4 | 7% | -$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.
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.

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.