PMT function
PMT calculates the fixed payment for a loan or a savings plan, based on a constant interest rate and number of payments. Use it for mortgage, car loan and lease payments. The two things to get right are units (monthly rate with monthly payments) and signs (money you receive is positive, money you pay is negative).
- Available in
- Every current version
- Category
- Financial functions
- Returns
- The payment per period. Negative if pv is positive, because the payment is money going out.
- Google Sheets
- Works the same in Google Sheets.
Syntax
=PMT(rate, nper, pv, [fv], [type])
| Argument | Required? | What it is |
|---|---|---|
rate | Required | The interest rate per period. For monthly payments on an annual rate, divide by 12. |
nper | Required | The total number of payments. For a 5-year loan paid monthly, 60. |
pv | Required | The amount borrowed today. Enter it as negative to get a positive payment. |
fv | Optional | The balance you want left after the last payment. Usually 0, the default. |
type | Optional | 0 or omitted if payments are due at the end of each period, 1 if at the beginning. |
Examples
Each result below is calculated from the sample sheet by our formula engine, the same one that grades the practice.
| Cell | Formula | Result | What it shows |
|---|---|---|---|
| D2 | =PMT(B3/12,B4*12,-B2) | $489.15 | Monthly payment: monthly rate and 60 monthly periods |
| D3 | =PMT(B3/12,B4*12,B2) | -$489.15 | Same payment as a negative: money going out |
| D4 | =PMT(B3,B4,-B2) | $6,015.86 | Paying once a year instead: annual rate, 5 periods |
Try it live
This is the sample sheet. Change any number or text, or edit a formula, and the results update.
Show the sample data as a table
| Input | Value | Result | |
| Loan amount | $25,000 | $489.15 | |
| Annual rate | 6.5% | -$489.15 | |
| Years | 5 | $6,015.86 |
Practice PMT
From lesson 25, Loan payments with PMT. Graded instantly, then re-checked on changed data.
Monthly payment
B6, calculate the monthly payment for the loan in B2:B4.Common mistakes
| What you see | Why, and how to fix it |
|---|---|
| A payment that is far too high | The annual rate was used with monthly periods. Divide the rate by 12 and multiply the years by 12. |
| A negative payment | That is the sign convention: a positive loan amount produces a negative (outgoing) payment. Enter the loan as -B2, or wrap PMT in ABS. |
| Rate typed as 6 instead of 6% | PMT treats 6 as 600% per period. Enter 6%, or 0.06. |
Tips
- Total interest is the payment times the number of payments, minus the loan: =PMT(...)*n - loan.
- Put the rate, term and amount in their own cells so you can change them and see the payment update.
Questions
How do I calculate a monthly loan payment in Excel?
Use =PMT(annual_rate/12, years*12, -loan_amount). For $25,000 at 6.5% over 5 years, that is about $489.15 a month.
Why is my PMT result negative?
Excel treats money you pay out as negative. Enter the loan amount as a negative number to get a positive payment.
Does PMT include taxes and insurance?
No. PMT covers principal and interest only. Add escrow items like property tax separately.
Lessons that use PMT
Last reviewed October 2026. Every example result on this page is calculated live by the Spreadsheet Practice formula engine.