PMT Function in Excel: Loan Payments Step by Step
· 7 min read
The PMT function Excel provides returns the fixed payment on a loan with a constant interest rate. For monthly payments, divide the annual rate by 12 and multiply the years by 12, so the rate and the number of payments are both per month:
=PMT(rate, nper, pv, [fv], [type])
=PMT(annual_rate/12, years*12, -loan_amount)The minus sign in front of the loan amount makes the payment come out as a positive number. Without it, Excel shows the payment as negative, which is correct but often confusing. Both points are explained below.
A worked example: a car loan
An $18,000 loan at 6.5% a year, repaid monthly over 4 years. Column B holds the inputs and the results; column E shows what the most common mistakes do to the answer.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Input | Value | Version | Monthly payment | |
| 2 | Loan amount | $18,000 | Correct | $426.87 | |
| 3 | Annual interest rate | 6.5% | Annual rate, monthly periods | $1,229.85 | |
| 4 | Years | 4 | Years as periods | $4,561.10 | |
| 5 | No minus sign | -$426.87 | |||
| 6 | Monthly payment | $426.87 | |||
| 7 | Number of payments | 48 | |||
| 8 | Total paid | $20,489.72 | |||
| 9 | Total interest | $2,489.72 |
The payment in B6 is:
=PMT(B3/12, B4*12, -B2)Result: $426.87 a month. Over 48 payments that is $20,489.72 in total, so the loan costs $2,489.72 in interest (B8 and B9). Totals like these are often the number people actually want, and they come straight from the payment.
The three arguments you always need
- rate: the interest rate per payment period. Monthly payments need a monthly rate, so
B3/12, which is about 0.54% here. - nper: the total number of payments. Four years of monthly payments is
B4*12, or 48. - pv: the present value, meaning the amount borrowed today.
The two optional arguments are fv, a balance left at the end (0 if you leave it out), and type, which is 0 for payments at the end of each period and 1 for payments at the start.
Match the rate to the periods
Column E shows what happens when the units do not match. Using the annual rate with monthly periods (E3) treats 6.5% as the interest per month, and the payment jumps to $1,229.85. Using years as the number of periods (E4) asks for the loan to be repaid in four monthly payments: $4,561.10.
The rule is simple: rate and nper must use the same period. Monthly payments, monthly rate, number of months. Quarterly payments would be rate/4 and years*4. A quick sense check catches most errors. A monthly payment can never be less than the loan divided by the number of months ($375 here), and with a normal interest rate it should not be far above it.
Why PMT returns a negative number
E5 leaves out the minus sign and returns -$426.87. That is not an error. Excel's finance functions follow a cash flow convention: money you receive is positive and money you pay out is negative. You receive the $18,000 loan, so the payments going out are negative.
There are two ways to show a positive payment:
=PMT(B3/12, B4*12, -B2)
=-PMT(B3/12, B4*12, B2)Both return $426.87. The first is more common. What matters is consistency: once you flip the sign of one cash flow, keep the others in the same direction, especially when you use FV, PV and NPER, which take payments as an argument.
Rounding the payment
PMT returns the full value, 426.869152…, and the cell format only hides the extra decimals. If a later formula multiplies the payment, the hidden decimals come along. When the payment should be the real amount charged, round it: =ROUND(PMT(B3/12, B4*12, -B2), 2). Our round to 2 decimal places guide explains the difference between rounding and formatting.
A balloon payment or payments at the start
To leave $5,000 to pay in one lump at the end, put it in the fv argument:
=PMT(B3/12, B4*12, -B2, 5000)Result: $335.38 a month, plus the $5,000 at the end. For payments due at the start of each month, as with many leases, set type to 1: =PMT(B3/12, B4*12, -B2, 0, 1) returns $424.57.
FV, PV and NPER: the same maths, solved for something else
PMT is one of a family. Each one solves the same equation for a different unknown:
| Function | Answers | Needs |
|---|---|---|
| PMT | What is the payment? | rate, nper, pv |
| FV | What will it grow to? | rate, nper, pmt |
| PV | How much can I borrow? | rate, nper, pmt |
| NPER | How many payments? | rate, pmt, pv |
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Question | Annual rate | Years | Monthly amount | Loan or balance | Result |
| 2 | Payment on an $18,000 loan | 6.5% | 4 | $18,000 | $426.87 | |
| 3 | Savings after 3 years at $250 a month | 4.0% | 3 | $250 | $9,545.39 | |
| 4 | Loan you can borrow at $400 a month | 6.5% | 4 | $400 | $16,867.00 | |
| 5 | Months to repay $18,000 at $500 a month | 6.5% | $500 | $18,000 | 40.2 |
FV (future value): saving $250 a month for 3 years at 4% a year grows to $9,545.39. You put in $9,000; the rest is interest.
=FV(B3/12, C3*12, -D3)PV (present value): if you can afford $400 a month for 4 years at 6.5%, the most you can borrow is $16,867.00. This is PMT run backwards, which makes it useful for budgets.
NPER (number of periods): paying $500 a month on the $18,000 loan clears it in 40.2 months. Notice the signs: the payment is negative and the loan positive, because they flow in opposite directions. Round up to 41, as the last payment will be smaller than the rest: =ROUNDUP(NPER(B5/12, -D5, E5), 0).
There is also RATE, which finds the interest rate from the payment, term and loan amount, and IPMT and PPMT, which split a single payment into its interest and principal parts.
Common mistakes
Annual rate with monthly payments. By far the most common PMT error, and a typical one in AI-written formulas: =PMT(B3, B4*12, -B2) gives a payment almost three times too high. Whenever you check a finance formula, look at the rate argument first and ask whether it matches the payment period.
Years instead of payments. nper is the number of payments, not the number of years. A 4-year monthly loan has 48.
Mixed signs in FV, PV and NPER. If NPER returns #NUM!, or FV returns a negative balance you did not expect, the payment and the loan probably have the same sign. Money in and money out need opposite signs.
Typing the rate as a whole number. 6.5% is 0.065. A cell containing 6.5 means 650%.
Breaking the references when you build a payment table. A grid of payments for several rates and terms needs mixed references such as $A4 and B$3, so each cell reads its own row's rate and its own column's term. See the absolute reference guide.
FAQ
What does the PMT function do in Excel?
It calculates the fixed payment per period for a loan with a constant interest rate, from the rate, the number of payments and the loan amount.
How do I calculate a monthly loan payment in Excel?
Use =PMT(annual_rate/12, years*12, -loan_amount). For $18,000 at 6.5% over 4 years that is $426.87.
Why is my PMT result negative?
Excel treats money you pay as negative and money you receive as positive. Put a minus sign in front of the loan amount, or in front of PMT, to show the payment as a positive number.
What is the difference between PMT and IPMT?
PMT returns the whole payment. IPMT returns only the interest part of one payment, and PPMT the principal part. IPMT plus PPMT equals PMT.
How do I find how many months it takes to pay off a loan?
Use NPER with a negative payment: =NPER(rate/12, -payment, loan). Round the result up to a whole number of months.
Practise this on a realistic dataset
Solve it yourself on one company's data, with instant feedback that points at what's wrong without giving away the answer.
- Monthly payment on a delivery van
- A shelving loan that costs too muchAudit
- How many months to pay off the forklift
- Biggest forklift loan the budget allows