PMT Function in Excel: Calculate a Loan Payment
By ExcelReady TeamUpdated 6 min read
The short answer
PMT returns the regular payment for a loan with a fixed interest rate. Write =PMT(rate, nper, pv): the interest rate per period, the number of payments and the loan amount. For a 20,000 loan at 6% a year over 5 years, paid monthly, =PMT(6%/12,5*12,20000) returns -386.66. The minus sign means money you pay out.
Key takeaways
- The syntax is =PMT(rate, nper, pv, [fv], [type]); the last two are optional.
- Match the units: for monthly payments, divide the yearly rate by 12 and multiply the years by 12.
- PMT returns a negative number for a loan. Put a minus in front, =-PMT(...), to show it as positive.
- Total interest is the payment times the number of payments, minus the loan amount.
- Keep the inputs in cells and lock them with $ so you can test other rates and terms.
On this page+
- What does the PMT function do?
- How to calculate a monthly loan payment
- Why is the PMT result negative?
- How to round the payment and find total interest
- PMT examples: mortgage, yearly payments and savings
- How to compare loans with absolute references
- Related questions: how long, and how much can I borrow?
- Common PMT mistakes
- Practise it
The PMT function in Excel calculates the fixed payment on a loan: a car loan, a personal loan or a mortgage. You give it the interest rate, the number of payments and the amount borrowed, and it returns what you pay each period. The only tricky parts are matching monthly and yearly units and understanding the minus sign. This guide covers both, with a loan you can rebuild yourself.
What does the PMT function do?#
PMT returns the payment for a loan with a fixed rate and equal payments. Each payment covers that period's interest and pays back part of the loan, so the balance reaches zero on the last payment.
=PMT(rate, nper, pv, [fv], [type])| Argument | Meaning | Example |
|---|---|---|
| rate | Interest rate per period | 6%/12 for monthly |
| nper | Total number of payments | 5*12 = 60 |
| pv | Present value: the loan amount | 20000 |
| fv | Optional. Balance left at the end. Default 0 | 0 |
| type | Optional. 0 = pay at end of period (default), 1 = start | 0 |
PMT works in every version of Excel, on Windows, Mac and the web.
How to calculate a monthly loan payment#
Put the inputs in cells, then point PMT at them. Here is the setup:
| A | B | |
|---|---|---|
| 1 | Loan amount | 20,000 |
| 2 | Annual rate | 6% |
| 3 | Years | 5 |
| 5 | Monthly payment |
In B5, type:
=PMT(B2/12,B3*12,B1)The result is -386.66. B2/12 turns 6% a year into 0.5% a month. B3*12 turns 5 years into 60 monthly payments. B1 is the 20,000 you borrow.
The units must match. If you forget to divide the rate by 12, Excel thinks you pay 6% interest every month, and the payment jumps to about 1,237.
Why is the PMT result negative?#
PMT follows a cash-flow sign rule: money you receive is positive, money you pay out is negative. You receive the loan, so pv is positive and the payment is negative.
To show a positive payment, use either of these:
=-PMT(B2/12,B3*12,B1): a minus in front of the function.=PMT(B2/12,B3*12,-B1): a negative loan amount.
Both return 386.66. Pick one style and use it everywhere, so other people reading the sheet aren't confused.
How to round the payment and find total interest#
Wrap PMT in ROUND to get a payment in whole cents, the way a bank quotes it:
=ROUND(-PMT(B2/12,B3*12,B1),2)The ROUND function guide explains the second argument, and the rounding lesson lets you try ROUND, ROUNDUP and ROUNDDOWN on real numbers. The article on ROUND, ROUNDUP and ROUNDDOWN shows when each one is right.
Once you have the payment, total cost and interest are simple sums:
| Question | Formula | Result |
|---|---|---|
| Total paid over 5 years | =B5*B3*12 | 23,199.36 |
| Total interest | =B5*B3*12-B1 | 3,199.36 |
(These use the unrounded payment. With the rounded 386.66, the totals are 23,199.60 and 3,199.60.)
PMT examples: mortgage, yearly payments and savings#
The same function handles many money questions. Change the inputs, keep the shape.
| Scenario | Formula | Result |
|---|---|---|
| 250,000 mortgage, 4.5%, 30 years, monthly | =PMT(4.5%/12,30*12,250000) | -1,266.71 |
| 20,000 loan, 6%, 5 yearly payments | =PMT(6%,5,20000) | -4,747.93 |
| Save 10,000 in 3 years at 3%, monthly | =PMT(3%/12,36,0,10000) | -265.81 |
The savings example uses fv instead of pv. You start with nothing (pv = 0) and want 10,000 at the end (fv = 10000). The opposite question, "how much will my savings grow to?", is the job of FV, covered in the compound interest formula guide.
How to compare loans with absolute references#
Lock the loan amount with dollar signs, then fill one formula across a table of rates and terms. Say rates sit in A8:A10 and you want the payment for a 20,000 loan over 5 years next to each one:
=-PMT(A8/12,$B$3*12,$B$1)Fill it down from B8 to B10 (Windows: Ctrl+D; Mac: ⌘D). A8 changes to A9 and A10, but $B$3 and $B$1 stay put. Press F4 on Windows (⌘T on Mac) while the cursor is in a reference to cycle through the $ options. The lesson on relative vs absolute references practises exactly this.
Related questions: how long, and how much can I borrow?#
PMT has sister functions that solve for a different unknown, using the same arguments and the same sign rule.
| Question | Formula | Result |
|---|---|---|
| How many months to repay 20,000 at 6% with 500 a month? | =NPER(6%/12,-500,20000) | about 44.7 |
| How much can I borrow at 6% over 5 years if I can pay 400 a month? | =PV(6%/12,60,-400) | about 20,690 |
| What rate gives a 386.66 payment on 20,000 over 60 months? | =RATE(60,-386.66,20000)*12 | about 6% |
NPER answers "how long", PV answers "how much" and RATE answers "at what interest rate". In each case, the payment is negative because it's money you pay out. Knowing all four makes it easy to check one answer with another: plug the PMT result into RATE and you should get the original rate back.
Common PMT mistakes#
Check these when the payment looks wrong:
- Yearly rate with monthly payments. Always divide the annual rate by 12 for monthly payments.
- Years instead of payments in nper. Five years of monthly payments is 60, not 5.
- Rate typed as 6 instead of 6%. PMT then uses 600% interest. Type the % sign or use 0.06.
- Expecting a positive number. The minus sign is normal; flip it with
=-PMT(...). - Rate of zero. PMT still works and simply divides the loan by the number of payments.
To see how each payment splits into interest and principal, build a loan amortization schedule with IPMT and PPMT, the sister functions of PMT. PMT also comes up often in Excel skills for finance jobs.
Practise it#
Loan payment questions test whether you can match units and lock inputs under time pressure. Practise relative and absolute references in a live spreadsheet: you type the formula and it checks it instantly. Then practise ROUND to tidy financial results to the cent, and try the finance Excel test when you're ready.
Try it yourself
This is a live spreadsheet. Type the formula in the green cell and press Enter.
What is the monthly payment on this loan? Click B5 and use PMT with the monthly rate (B2/12), the number of months (B3 times 12) and the loan amount (B1). Put a minus in front of PMT and round to 2 decimals.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | ||||
| 2 | ||||
| 3 | ||||
| 4 | ||||
| 5 | Monthly payment | |||
| 6 | ||||
| 7 | ||||
| 8 |
Tip: =ROUND(-PMT(B2/12, B3*12, B1), 2). The minus turns the payment positive.
Practise this in a lesson
Frequently asked questions
How do I calculate a monthly loan payment in Excel?
Use =PMT(annual rate/12, years*12, loan amount). For 20,000 at 6% over 5 years, =PMT(6%/12,60,20000) returns -386.66 a month.
Why is the PMT result negative?
Excel treats money you receive as positive and money you pay as negative. The loan amount is positive because you receive it, so the payment comes out negative. Use =-PMT(...) to show a positive number.
What do rate and nper mean in PMT?
Rate is the interest rate for one payment period, and nper is the total number of payments. For monthly payments over 5 years at 6% a year, rate is 6%/12 and nper is 60.
How do I calculate total interest paid on a loan in Excel?
Multiply the payment by the number of payments and subtract the loan amount: =-PMT(6%/12,60,20000)*60-20000 returns about 3,199.36.
Can PMT calculate how much to save each month?
Yes. Leave pv as 0 and put the savings target in fv: =PMT(3%/12,36,0,10000) returns -265.81, the monthly deposit needed to reach 10,000 in three years at 3%.