PMT calculates the regular payment needed to pay off a loan. The formula every finance professional uses for mortgages, car loans and business lending.
PMT calculates the fixed regular payment required to pay off a loan in full over a set number of periods, at a fixed interest rate. Give it the interest rate, the number of payments, and the loan amount — it returns the payment per period.
PMT is the core formula for mortgage calculations, car loan payments, business loan repayments, and any scenario where you need to know "how much will I pay each month?"
=PMT(rate, nper, pv, [fv], [type])| Argument | Description |
|---|---|
| rate required | Interest rate per period. For a 6% annual rate with monthly payments, use 6%/12 or 0.06/12. |
| nper required | Total number of payment periods. A 5-year monthly loan = 5*12 = 60. |
| pv required | Present value — the total loan amount today. |
| fv optional | Future value — balance remaining after all payments. Default is 0 (loan fully repaid). |
| type optional | 0 = payment at end of period (default). 1 = payment at start of period. |
PMT returns a negative value because it represents money leaving your account. Use -PMT or ABS(PMT) to display it as a positive payment amount.
Loan: £200,000. Annual rate: 4.5%. Term: 25 years. What is the monthly payment?
=-PMT(4.5%/12, 25*12, 200000)
← result: £1,111.85 per month
Divide the annual rate by 12 for monthly rate. Multiply years by 12 for number of monthly payments. Use -PMT to get a positive number.
=-PMT(B1/12, B2*12, B3)
← B1 = annual rate, B2 = years, B3 = loan amount
Reference cells makes it a live calculator — change any input and the monthly payment updates instantly. This is how mortgage calculators are built.
=-PMT(6%/52, 2*52, 10000)
← weekly payment on a 2-year £10k loan at 6%
Divide the annual rate by 52 for weekly rate. Multiply years by 52 for total weekly payments. The same logic applies to any payment frequency.
You want to retain £5,000 value after 3 years (balloon payment at end).
=-PMT(5%/12, 3*12, 20000, 5000)
The fv argument sets how much remains after all payments — useful for personal contract purchase (PCP) car finance.
Total paid = -PMT(rate/12, years*12, loan) * years*12
Total interest = Total paid - loan
Multiply the monthly payment by the number of payments to find total repaid. Subtract the original loan to find total interest paid — often a shocking figure.
Use PV (the reverse of PMT) to find the loan amount you can afford given a monthly budget.
=PV(4.5%/12, 25*12, -1200)
← max loan if you can afford £1,200/month
PMT tells you the total payment. IPMT and PPMT split it into interest and principal components for any given period.
Interest portion of payment 12:
=IPMT(4.5%/12, 12, 25*12, -200000)
Principal portion of payment 12:
=PPMT(4.5%/12, 12, 25*12, -200000)
Use these to build a full loan amortisation schedule — a table showing how each payment splits between interest and principal repayment over the life of the loan.
| Function | Answers |
|---|---|
| PMT | What is the payment per period? |
| PV | What loan can I afford given a payment? |
| RATE | What is the interest rate given payment and term? |
| NPER | How many payments to pay off a loan? |
| FV | What is the future value of an investment? |
| IPMT | How much interest is in a specific payment? |
| PPMT | How much principal is in a specific payment? |
PMT's rate argument must match the payment period. For monthly payments, divide the annual rate by 12. For quarterly payments, divide by 4. Using the annual rate directly gives a completely wrong answer.
nper must be the total number of payments, not years. For a 25-year monthly loan, nper = 25*12 = 300. Using 25 instead gives the payment for a 25-month loan — dramatically different.
PMT returns a negative number (outflow). Use -PMT or ABS(PMT) for a positive display. If you use the raw result in a further calculation, keep it negative — it represents money going out.
ExcelPro has PMT, PV, RATE and NPV exercises in the Accounting and Small Business tracks. Real loan scenarios, instant feedback, free to start.
Try financial exercises →