PMT Function
Financial
Work out the regular payment on a loan, or how much to save each month to reach a target.
Updated 12 Oct 2026 Ā· 5 min read Ā· By Sky Dashboards
PMT works out the regular payment on a loan, or how much to set aside each period to reach a target. Give it an interest rate, a number of payments and an amount, and it returns the payment per period. This guide covers the arguments, the units that catch people out, the sign of the result, a full loan example and a short amortisation table.
In this guide
PMT syntax
=PMT(rate, nper, pv, [fv], [type])| Argument | Required? | What it does |
|---|---|---|
rate |
Required | The interest rate per period. For monthly payments, divide the annual rate by 12. |
nper |
Required | The total number of payments. Monthly payments over 5 years make 5*12, which is 60. |
pv |
Required | The present value: the amount borrowed today. Use 0 when you are saving from nothing. |
fv |
Optional | The balance you want after the last payment. Leave it out (0) to pay a loan off completely. Use it for a savings target or a balloon payment. |
type |
Optional | When payments are made: 0 or omitted for the end of each period, 1 for the start. |
PMT returns the payment for one period, covering both interest and repayment of the amount borrowed. It ignores fees, insurance and anything else a lender may add.
Match the rate to the number of periods
The most common PMT mistake is mixing units. rate and nper must describe the same period. For a loan quoted at 7.2% a year and repaid monthly over 5 years:
- rate is
7.2%/12, which is 0.6% a month. - nper is
5*12, which is 60 payments.
Forget to divide and Excel charges 7.2% a month: =PMT(7.2%, 60, 28500) returns ($2,084.15) instead of ($567.03). For quarterly payments, divide the rate by 4 and multiply the years by 4.
Tip
Keep the annual rate and the years in their own cells and convert them inside the formula, as in =PMT(B2/12, B3*12, B1). The inputs stay easy to read, and you can test other rates with a data table.
Worked example: a delivery van loan
A florist borrows $28,500 for a delivery van at 7.2% a year over 5 years, repaid monthly.
| A | B | ||
|---|---|---|---|
| 1 | Loan amount | $28,500.00 | |
| 2 | Annual rate | 7.2% | |
| 3 | Years | 5 | |
| 4 | Monthly payment | ($567.03) |
B4 returns the monthly payment, shown in a currency format with negatives in brackets.
The van costs $567.03 a month. Over 60 payments that adds up to $34,021.64, of which $5,521.64 is interest.
Payments at the start of each month
=PMT(B2/12, B3*12, B1, 0, 1)Returns ($563.65). Paying in advance means each payment has one month less interest to cover.
A balloon payment at the end
=PMT(B2/12, B3*12, B1, -5000)Returns ($497.55): a lower monthly payment, with $5,000 still to pay at the end. The -5000 is negative because it is money you pay out.
Why PMT returns a negative number
Excel's financial functions follow the direction of the cash. The loan comes to you, so pv is positive. The payment goes from you, so PMT returns a negative number. That is not an error. To show the payment as a positive figure, either:
- put a minus in front of the function:
=-PMT(B2/12, B3*12, B1), or - enter the loan as a negative:
=PMT(B2/12, B3*12, -B1).
Both return $567.03.
Note
Pick one approach and use it throughout the workbook. Mixing them is how a total ends up adding payments that should have been subtracted.
Save towards a target with fv
PMT also answers the saving question: how much to put away each month to reach a sum. A ceramics studio wants $15,000 for a new kiln in 3 years, saving into an account that pays 4% a year.
=PMT(4%/12, 3*12, 0, 15000)Returns ($392.86): deposit $392.86 a month. pv is 0 because the account starts empty.
Over 36 months the deposits total $14,142.95, and interest makes up the other $857.05. Deposit at the start of each month instead (type 1) and the figure drops slightly, to ($391.55).
To work the other way, from a monthly budget to a loan size, use PV: =PV(7.2%/12, 60, -500) returns $25,131.07, the most you could borrow on these terms for $500 a month. Goal Seek does the same job for any formula.
IPMT, PPMT and an amortisation table
Every payment is part interest and part principal, the amount that reduces the balance. Early payments are mostly interest; later ones are mostly principal. Two functions split any single payment:
-
=IPMT(rate, per, nper, pv, [fv], [type])returns the interest part of payment number per. -
=PPMT(rate, per, nper, pv, [fv], [type])returns the principal part.
For any payment, the two add up to PMT. Here are the first three months of the van loan, with the inputs in B1:B4 as before:
| A | B | C | D | E | F | ||
|---|---|---|---|---|---|---|---|
| 1 | Loan amount | $28,500.00 | |||||
| 2 | Annual rate | 7.2% | |||||
| 3 | Years | 5 | |||||
| 4 | Monthly payment | ($567.03) | |||||
| 5 | |||||||
| 6 | Month | Opening | Payment | Interest | Principal | Closing | |
| 7 | 1 | $28,500.00 | $567.03 | $171.00 | $396.03 | $28,103.97 | |
| 8 | 2 | $28,103.97 | $567.03 | $168.62 | $398.40 | $27,705.57 | |
| 9 | 3 | $27,705.57 | $567.03 | $166.23 | $400.79 | $27,304.78 |
D7 is =-IPMT($B$2/12, A7, $B$3*12, $B$1) and E7 is the same with PPMT, both filled down. B7 is =B1, C7 is =-$B$4, F7 is =B7-E7, and B8 picks up the previous closing balance with =F7.
Interest is 0.6% of the opening balance: $171.00 in month 1, a little less each month as the balance shrinks. By month 60 it is just $3.38. To total the interest over the whole loan, use =CUMIPMT(B2/12, B3*12, B1, 1, 60, 0), which returns ($5,521.64).
Cents that do not add up
Excel keeps every figure at full precision, so the displayed cents can look a cent out: in month 2, $168.62 + $398.40 shows as $567.02. Lenders round each payment to the cent, so their schedule can differ in the last digit. Wrap each column's formula in ROUND(..., 2) if your table has to match a statement exactly.
Frequently asked questions
Why is my PMT result negative?
The payment is money going out, while the loan amount is money coming in. Put a minus before PMT, or enter the loan amount as a negative, to show a positive figure.
How do I get a monthly payment from an annual rate?
Divide the rate by 12 and multiply the years by 12: =PMT(annual_rate/12, years*12, amount).
Does PMT include fees or insurance?
No. It covers interest and principal only. Add any fixed monthly fees separately.
What does the type argument do?
It sets when payments happen: 0 (the default) at the end of each period, 1 at the start. Paying at the start gives a slightly lower payment.
Want the finished dashboard instead?
My Excel templates come with these formulas already wired up. Pick one, type in your own rows and the charts update.
Browse templates ā