PMT Loan Payment Calculator
Work out the fixed payment on a loan from its interest rate, number of payments, and amount borrowed.
PMT
Work out the fixed payment on a loan from its interest rate, number of payments, and amount borrowed.
Annual rate divided by payments per year. Use 5%/12 for a 5% loan repaid monthly.
Total payments over the life of the loan, not the number of years. 30*12 is a 30-year monthly loan.
The amount borrowed. Enter it as a negative number so the payment comes back positive.
=PMT(5%/12, 30*12, -250000)Worked Example
A $25,000 car loan at 6% a year, repaid monthly over 5 years.
=PMT(6%/12, 5*12, -25000)Returns: 483.32 - the monthly payment, positive because the loan amount was entered as a negative present value.
Checks Before You Paste
- •The single most common PMT error is mixing units - an annual rate with a term in months. Divide the rate by the payments per year and multiply the years by the same number: =PMT(5%/12,30*12,-250000) returns 1,342.05 a month, while =PMT(5%,30*12,-250000) prices a 5% monthly rate and returns 12,500.
- •PMT returns a negative number because it models cash leaving your pocket. Either enter the loan amount as negative, as the default does, or negate the whole formula with =-PMT(...). Doing both flips the sign back to negative.
- •PMT covers principal and interest only - no taxes, insurance, or fees. Its optional 4th and 5th arguments are fv (balance still owed at the end, default 0) and type (0 = payment at the end of each period, 1 = at the start, as with most leases). For uneven cash flows use NPV and IRR instead, or XNPV and XIRR when the dates are irregular.
How PMT works
Availability: PMT is in every version of Excel, in Excel for Mac and for the web, and in Google Sheets with identical arguments and the same sign convention. The related IPMT, PPMT, CUMIPMT, CUMPRINC, NPER, RATE and PV functions are equally universal, so a repayment model built with them opens anywhere.
PMT returns the constant payment on a loan or annuity: the same amount every period, covering interest and principal, that clears the balance exactly at the end of the term. It assumes a fixed interest rate and equal periods, which is the standard structure for a mortgage, a car loan or an equipment lease. It is not a general cash-flow tool - if the payments vary, PMT is the wrong function and NPV, IRR or an amortisation schedule is the right one.
Everything hinges on the period. The rate and the number of payments must be expressed in the same unit, and this is where nearly every wrong answer comes from. A 5% annual loan repaid monthly over 30 years is =PMT(5%/12, 30*12, -250000), which gives 1,342.05 a month. Write =PMT(5%, 30*12, -250000) and Excel prices a 5% monthly rate over 360 months and returns 12,500 - a number that is not wrong so much as answering a question nobody asked.
PMT follows the cash-flow sign convention: money coming in is positive, money going out is negative. Enter the loan amount as a positive present value and the payment comes back negative, because you are paying it out. Enter it as a negative present value, as the calculator above does, and the payment is positive, which is easier to read in a report. Negating the formula with a leading minus achieves the same thing. Doing both cancels out and puts the sign back where it started.
The two optional arguments cover the cases the basic loan does not. fv is the balance still outstanding after the last payment - zero for a normal repayment loan, and the residual value for a balloon payment or a lease with a buyout. type says whether the payment falls at the end of the period (0, the default, and how mortgages work) or at the start (1, and how most leases and rentals work); paying at the start reduces the payment slightly because each instalment has a period less of interest accruing on it.
Syntax
=PMT(rate, nper, pv, [fv], [type])| Argument | Required | What it does |
|---|---|---|
rate | Required | The interest rate per period, not per year. For a monthly-paid loan quoted annually, divide: 4.5%/12. For quarterly payments, 4.5%/4. Enter it as a percentage or a decimal - 5% and 0.05 are the same value to Excel. |
nper | Required | The total number of payments over the life of the loan, in the same period as the rate. A 25-year mortgage paid monthly is 25*12, or 300 - not 25. |
pv | Required | The present value: the amount borrowed today, or the value of the asset being financed. Enter it as a negative number to get a positive payment back, or as a positive number to get the payment as a negative cash outflow. |
fv | Optional | Default 0. The cash balance remaining after the final payment. Zero means the loan is fully repaid. Use it for a balloon payment or a lease residual - the amount that will still be owed when the scheduled payments end. |
type | Optional | Default 0. Set to 0 when payments fall at the end of each period, which is how loans and mortgages work, or 1 when they fall at the beginning, which is how most leases, rentals and insurance premiums work. Paying in advance lowers the payment slightly. |
More worked examples
A £320,000 mortgage at 4.5% a year, repaid monthly over 25 years, with nothing outstanding at the end.
=PMT(4.5%/12, 25*12, -320000)Returns: 1778.66 - the monthly repayment.
Multiply by 300 for the total repaid, 533,599.18, of which 213,599.18 is interest. Format the result as currency; PMT returns a raw number and will show as 1778.663929 in a General cell.
A $40,000 equipment loan at 7% over four years, with a $10,000 balloon still owed when the scheduled payments finish.
=PMT(7%/12, 4*12, -40000, 10000)Returns: 776.72 a month, with the $10,000 due as a lump sum at the end.
The fourth argument carries the sign opposite to pv, because it is a balance still owed rather than money received. Without it, the same loan fully amortised costs 957.85 a month.
An $18,000 three-year equipment lease at 6%, where the first payment is due on signing rather than a month later.
=PMT(6%/12, 36, -18000, 0, 1)Returns: 544.87 a month.
The same lease with payments in arrears, type 0, costs 547.59. The gap is one period of interest on each instalment, which is small monthly and worth checking when comparing quotes that state the convention differently.
Common mistakes
- Annual rate with a monthly term
- =PMT(5%, 30*12, -250000) returns 12,500 rather than 1,342.05, because it applies 5% per month across 360 months. Excel gives no warning - the result is a perfectly valid answer to a different loan. Divide the rate and multiply the years by the same number, every time.
- The payment comes back negative
- =PMT(4.5%/12, 300, 320000) with a positive loan amount returns -1778.66. That is the sign convention working correctly: cash leaving you is negative. Enter pv as negative, or put a minus in front of the whole formula. Doing both restores the negative, which is the usual cause of a sign that will not behave.
- Treating PMT as the full cost of ownership
- PMT covers principal and interest only. Property taxes, insurance, service charges, arrangement fees and maintenance are outside it, so a mortgage quote built on PMT alone always understates the monthly outlay. Model the extras as separate line items rather than trying to bury them in the rate.
- Comparing loans with different payment frequencies
- A weekly-paid loan and a monthly-paid loan at the same headline rate do not cost the same, and comparing the two PMT figures directly is meaningless because they cover different lengths of time. Convert both to an annual total - PMT multiplied by the payments per year - or compare total interest over the full term.
- Rounding the payment and expecting the balance to clear
- PMT returns a value with full precision, and a schedule built on the rounded figure leaves a few pounds outstanding or overpaid at the end. Real lenders resolve this by adjusting the final instalment. If you are building an amortisation table, either keep full precision in the calculation and round only the display, or explicitly compute the last payment as whatever clears the remaining balance.
Frequently Asked Questions
Because Excel's financial functions use a cash-flow sign convention: money you receive is positive and money you pay out is negative. Entering the loan as a positive present value means the repayments flow the other way, so they come back negative. Enter the loan amount as a negative number, or write =-PMT(...), to get a positive payment - but not both, or the two negatives cancel.
Multiply the payment by the number of payments and subtract the amount borrowed: =PMT(rate, nper, -pv)*nper - pv. On the 25-year mortgage above that is 1778.66 × 300 − 320,000, or 213,599.18 in interest. For the interest in one specific period rather than the total, use IPMT, and CUMIPMT for the interest across a range of periods.
fv is the balance still owed after the final scheduled payment - leave it at 0 for a loan that fully repays, or set it to the residual for a balloon or lease buyout, which lowers the regular payment. type moves the payment from the end of the period (0) to the beginning (1); paying in advance means each instalment accrues one period less interest, so the payment falls slightly.
IPMT and PPMT take the same arguments plus a period number: =IPMT(rate, per, nper, pv) gives the interest portion of payment number per, and =PPMT(rate, per, nper, pv) gives the principal portion. The two always add up to PMT for that period. Filling both down a column of period numbers is how an amortisation schedule is built.
No. PMT assumes one fixed rate for the whole term, so a tracker or a fixed-then-reverting mortgage has to be modelled in stages: run PMT for the fixed period, work out the balance at the end of it with FV, then run PMT again on that balance at the new rate for the remaining term. Trying to average the rates gives a payment that matches neither stage.