Excel Tutorials · Lesson 44
PMT turns a loan amount, an interest rate and a term into the fixed payment you will actually make each month. This lesson uses the vehicle loan example from the video and covers the two things that trip everyone up: dividing the annual rate by 12, and understanding why Excel hands back a negative number.
The same free Excel Xpert companion workbook used in the video, one tab per lesson. No login, no email required.
PMT calculates the payment for a loan based on constant payments and a constant interest rate. The syntax is =PMT(rate, nper, pv, [fv], [type]), where rate is the interest rate per period, nper is the total number of payments, and pv is the present value, meaning the amount you are borrowing. The last two arguments are optional and most loans never need them.
The word "period" in that first argument is where nearly every wrong answer comes from. If you are making monthly payments, every argument has to be expressed in months. An annual rate of 6% is not 6, and it is not 0.06, it is 0.06/12. A five-year term is not 5, it is 5*12. Mix a yearly rate with a monthly count and Excel will still return a number, it will just be a number for a loan that does not exist.
The second surprise is the minus sign. PMT returns a negative result because Excel treats money leaving your pocket as a negative cash flow, the same convention used across every financial function in the application. The math is correct, the sign is just an accounting perspective. Put a minus in front of the present value, or in front of the whole formula, and you get a positive payment to display.
PMT gives you the total payment, principal and interest combined, which stays identical every month for the life of a fixed-rate loan. What changes month to month is the split between the two, and that is what its sibling functions IPMT and PPMT exist to break out.
Step-by-step, matching the video above.
Put the loan amount, the annual interest rate and the term in years into three separate cells rather than typing them inside the formula. You will want to change them, and a formula full of hard-coded numbers cannot be adjusted or audited.
Enter 6% rather than 6 or 0.06, and format the cell as a percentage. Entering a whole number like 6 tells Excel the rate is 600%, which is the most common cause of an absurd payment figure.
In your result cell, type =PMT(. Excel shows the argument list as you type, with rate, nper and pv in bold to indicate they are required.
Click your annual rate cell and divide it by 12, so the first argument reads B2/12. This single division is the step most people skip, and it makes every result wrong.
For the second argument, take the term in years and multiply by 12, giving B3*12. A five-year auto loan is 60 payments, not 5.
For the third argument, enter -B1. Making the present value negative flips the result to a positive payment, which is easier to read on a summary than a number in parentheses.
Press Enter, then apply currency formatting with Ctrl+Shift+$ or the Home tab. Your finished formula reads =PMT(B2/12, B3*12, -B1).
| Input | Cell | Value |
|---|---|---|
| Loan amount | B1 | 32,000 |
| Annual interest rate | B2 | 6.5% |
| Term in years | B3 | 5 |
| =PMT(B2/12, B3*12, -B1) | B5 | $626.10 |
| Total paid over the term | B6 | $37,566.00 |
| Total interest paid | B7 | $5,566.00 |
The rate becomes 0.065/12, the term becomes 60 payments, and the loan amount goes in negative so the payment displays positive. Multiply the payment by 60 to get the total paid, then subtract the original 32,000 to see the interest. That last line is usually the number that changes someone's mind about a term length.
Three required, two optional, and one of the optional ones matters more than people expect.
Not the annual rate, unless you are making one payment a year. Divide by 12 for monthly, 26 for biweekly, 4 for quarterly. The period of the rate and the period of nper must always match.
The full count across the life of the loan, not the number of years. Thirty years of monthly payments is 360, and a five-year auto loan is 60.
The amount being borrowed today. On a vehicle purchase this is the price minus your down payment and any trade-in value, not the sticker price.
The balance you want remaining at the end, which defaults to 0 because most loans are paid off completely. Set it to a residual value to model a lease or a balloon payment.
0 or omitted means payments are due at the end of each period, which is standard for loans. 1 means the beginning of the period, which is how most leases and rents work, and it produces a slightly smaller payment.
Not an error, a convention, and there are three ways to handle it.
Every Excel financial function treats money you receive as positive and money you pay out as negative. You receive the loan, so pv is positive, and you pay the installments, so PMT comes back negative.
Writing -B1 for the loan amount is the cleanest fix, because it keeps the sign logic inside the arguments where it belongs and returns a positive payment.
=-PMT(B2/12,B3*12,B1) works equally well and some people find it more readable. Pick one approach and use it consistently across the workbook.
In a cash flow model where outflows genuinely need to be negative, keep the sign and let accounting formatting show it in red or in parentheses instead of fighting it.
The payment stays flat, but what it buys changes every single month.
=IPMT(rate, per, nper, pv) returns the interest inside a specific payment. The extra per argument is which payment number you are asking about, so 1 is the first month.
=PPMT(rate, per, nper, pv) returns the principal in that same payment. IPMT plus PPMT for any given period always equals PMT exactly.
Early payments are mostly interest because interest is charged on a large remaining balance. As principal comes down, the interest share shrinks and the principal share grows, which is why paying extra early saves so much more than paying extra late.
Number the rows 1 to nper, then fill IPMT and PPMT down with the period column as the per argument and everything else locked with absolute references like $B$2. Add a running balance column subtracting each principal payment.
A stacked column chart of principal against interest across all periods makes the crossover point obvious, and it explains a loan faster than any table of numbers. Feed the schedule into a PivotTable to summarize interest by year.
| Payment # | Total Payment | Interest | Principal |
|---|---|---|---|
| 1 | $626.10 | $173.33 | $452.77 |
| 30 | $626.10 | $95.66 | $530.44 |
| 60 | $626.10 | $3.37 | $622.73 |
Same 32,000 loan at 6.5% over 60 months. The payment never moves, but the first one sends $173 to the lender as interest while the last sends barely $3. That is the entire argument for a shorter term or extra principal payments, visible in three rows.
Same family, same argument logic, solving for a different unknown.
Give it a payment you can afford, a rate and a term, and PV returns the loan amount that payment supports. This is the function to use when you are shopping by budget rather than by price.
=NPER(rate, pmt, pv) returns the number of payments required. Feed it a larger payment than PMT calculated to see how many months extra principal shaves off.
When a dealer quotes a price and a monthly payment but stays vague on the rate, RATE reverse-engineers it from the term, payment and amount. Multiply the result by 12 to get the annual figure.
The same machinery pointed forward instead of backward, projecting what regular deposits at a given return will be worth after a set number of periods.
One payment figure is useful. A grid of them is what actually informs a decision.
Put terms across the top and rates down the side, then use Data > What-If Analysis > Data Table to fill the whole grid from your single PMT formula. Every combination calculates at once.
A longer term always produces a smaller monthly payment and a larger total paid. Add a total interest row beside every payment so the tradeoff is visible rather than hidden.
Subtract the down payment and trade-in from the price before it reaches pv. Financing tax, title and fees means those amounts belong in the loan figure too, which is why quoted payments often exceed a quick estimate.
Identical syntax, identical arguments, identical sign convention, and IPMT, PPMT, NPER and RATE all exist there too. A loan model built in either application opens correctly in the other.
This is the single most common error. Passing 6.5% straight into a monthly calculation produces a payment several times too large. The rate period must always match the payment period.
A five-year loan is 60 monthly payments, not 5. Multiply the years by 12 inside the formula so the input cell can stay in the units a person thinks in.
Excel reads a bare 6 as 600%. Either type the percent sign or divide by 100, and format the cell as a percentage so the mistake is visible on sight.
PMT is supposed to return a negative number, because payments are cash leaving your account. Negate the present value to display a positive payment.
The present value is what you actually borrow, so subtract the down payment and trade-in, and add any financed tax and fees, before it reaches the formula.
Stretching a loan longer always lowers the payment and always raises the total interest. Calculate total paid alongside every payment figure or the comparison is misleading.
A financing decision shouldn’t live in a spreadsheet nobody else can open.
Free 14-day trial. No credit card required.
Use =PMT(rate/12, years*12, -loan amount). Dividing the annual rate by 12 and multiplying the years by 12 converts everything to monthly periods, and the negative loan amount returns a positive payment.
=PMT(rate, nper, pv, [fv], [type]), where rate is the interest per period, nper is the total number of payments, and pv is the amount borrowed. The last two arguments are optional.
Excel treats money paid out as a negative cash flow across all its financial functions. Enter the loan amount as a negative value, or put a minus in front of the whole formula, to display a positive payment.
Almost always the rate. Either the annual rate was not divided by 12, or a whole number like 6 was entered instead of 6%, which Excel reads as 600%.
Subtract your down payment and trade-in from the price, add any financed tax and fees, then use that figure as pv in =PMT(rate/12, years*12, -pv).
PMT returns the full payment, IPMT returns just the interest portion of a specific payment, and PPMT returns just the principal portion. IPMT plus PPMT always equals PMT for the same period.
Number the rows from 1 to the total number of payments, then fill IPMT and PPMT down using that row number as the period argument and absolute references for the rate, term and loan amount.
It sets when payments are due. 0 or omitted means the end of each period, which is standard for loans, while 1 means the beginning, which is typical for leases and produces a slightly lower payment.
It sets the balance remaining at the end of the term, defaulting to 0. Use it to model a lease residual or a balloon payment where the loan is not fully paid off.
Use the PV function instead. Give it the rate per period, the number of payments and the payment you can afford, and it returns the loan amount that supports it.
Use =RATE(nper, pmt, pv) when you know the term, payment and amount, then multiply the result by 12 to convert the monthly rate into an annual one.
Divide the annual rate by 26 and multiply the years by 26 instead of 12, since the rate period and the payment count must always use the same interval.
Yes, with identical syntax, arguments and sign convention, and IPMT, PPMT, NPER, PV and RATE are all available there as well.
Build a grid with terms across the top and rates down the side, then use Data > What-If Analysis > Data Table to calculate every combination from one PMT formula.
Every business starts with a spreadsheet. Updoot is where you scale past it, with financing and cash flow already in the picture.
Start Your Free Trial →