Excel PMT function: build a real loan payment calculator

A $30,000 car loan at 6.5% APR over five years costs $587.06 a month. Every part of that answer sits inside one Excel function, and it comes back as a negative number for a specific reason. PMT looks simple — rate, periods, principal — but small mismatches (an annual rate against monthly periods, a positive pv where you expected a positive payment) quietly produce numbers that are off by orders of magnitude. Once you set it up cleanly, you can plug in any loan and read the answer straight off the cell. You can also split the payment into interest and principal, and see what happens when you send an extra $100 a month.

PMT syntax and the sign convention that trips everyone up

PMT takes three required arguments and two optional ones. The signature comes straight from the Microsoft PMT reference:

=PMT(rate, nper, pv, [fv], [type])

rate is the interest rate per period. nper is the total number of periods. pv is the present value — the loan principal you receive today. fv is the balance you want left after the last payment (0 for a fully amortized loan). type is 0 when payments hit at the end of each period, 1 when they hit at the beginning.

Excel treats cash flowing into your account as positive and cash flowing out as negative. A loan puts money in your pocket now (positive pv), and the payments leave your pocket later (negative PMT). That is why =PMT(0.065/12, 60, 30000) returns -587.06. If you prefer a positive number on the payment row, wrap the formula in a unary minus:

=-PMT(0.065/12, 60, 30000)

Match your rate and periods, or the answer is wrong

The single most common PMT mistake is a rate that does not match the period. Type an annual rate against a monthly nper and the formula computes as if each month carried an entire year of interest, so the payment balloons. Divide the annual rate by however many payment periods fit in a year, and multiply the years by the same number to get nper.

Pick your period first, then derive both rate and nper from it. If your loan agreement quotes an APR but interest actually compounds monthly, use APR/12 — that is the periodic rate the bank is billing. The tiny rounding differences that show up in the last row of the amortization schedule are a separate issue and rarely exceed a cent; the payment itself is exact.

A working loan calculator in one screen

The cleanest layout puts the inputs in column B, keeps every derived number one hop away, and shows the payment at the top. Drop this into a blank sheet and change any of the four inputs; the payment updates immediately.

Changing Payments per year in B4 flips the whole model — set it to 26 for biweekly and both the rate and the total periods adjust in one keystroke. That is what separates a real calculator from a one-off formula: every input has one home, and the payment formula reads from those cells instead of hard-coding numbers. Try B1 = 250000, B2 = 6.0%, B3 = 30, B4 = 12 to see a standard mortgage: $1,498.88 per period, $539,595 paid, $289,595 in interest.

Where the money goes: IPMT, PPMT, and an amortization table

PMT gives you a flat monthly number, but every payment splits into interest and principal — and the ratio shifts month by month. Two sibling functions handle that split. IPMT returns the interest portion of period N, PPMT returns the principal portion, and the two always sum to PMT.

=IPMT(rate, per, nper, pv)
=PPMT(rate, per, nper, pv)

The only new argument is per, the period number you want (1 for the first payment, 60 for the last on a 5-year monthly loan). Drop these into an amortization schedule where column A is the period number:

Fill row 3 down to row 61 and the balance column drives itself to zero on period 60. Absolute references on B1, B5, and B6 are load-bearing here — without them, autofill shifts the loan inputs and every row lies. If you would rather keep the argument list in one place, wrap the three functions in LET so rate and nper are named once and reused across all three sibling formulas.

Extra principal: what one extra $100 a month rewrites

PMT assumes every payment is exactly the same. Send more than the fixed payment and the amortization schedule from the previous section no longer matches reality — the balance drops faster, so future interest shrinks. Modeling this needs one extra column and a switch from IPMT/PPMT to a running-balance calculation.

1. Add a column F called Extra. Put your extra payment (say 100) in F2 and copy it down.
2. Replace the interest column with =E1*$B$5 — the previous balance times the periodic rate.
3. Replace the principal column with =B2+F2-C2 — base payment plus extra, minus that period’s interest.
4. Change the balance column to =E1-D2. Stop paying the row after E first goes at or below zero.

On the $30,000 / 6.5% / 5-year loan, an extra $100 a month cuts the loan to 51 payments and drops total interest from about $5,224 to $4,321. The fixed IPMT / PPMT formulas cannot capture this because they assume a rigid schedule; once you break that assumption, you have to walk the balance forward yourself. The tradeoff is worth naming out loud: you lose the closed-form Excel functions and gain the ability to model real repayment behavior.

Balloon loans and the fv argument

Not every loan pays down to zero. Commercial mortgages, some auto leases, and interest-only structures leave a residual balance due at the end — the balloon. That is exactly what fv models: the balance you want left after the last payment. Sign it negative because it is money leaving your pocket on the last day.

=-PMT(0.065/12, 60, 30000)
=> 587.06

=-PMT(0.065/12, 60, 30000, -10000)
=> 445.55

The $10,000 balloon lowers each monthly payment by about $142, but you still owe that lump sum on the last day — usually paid off with a refinance. The type argument works the same way in balloon and standard loans: set it to 1 only if the contract says payments are due at the start of the period, which is unusual for consumer loans and common for some commercial leases.

When PMT looks wrong: #NUM!, #VALUE!, and off-by-12 mistakes

Most PMT surprises fall into a small set. Run through this checklist before you assume the function is broken:

• ✓ Rate matches the period (monthly rate for monthly nper, not annual)
• ✓ pv is positive — the loan proceeds you received
• ✓ Payment result is negative; flip with a leading minus if you want a positive display
• ✓ nper is a positive whole number of periods, not a formula that returns a decimal
• ✓ Rate cell is formatted as percent (6.5%), not as the number 6.5
• ✓ For biweekly, rate = APR/26 and nper = years * 26, not APR/24

A #NUM! error usually means nper is zero or the rate arithmetic produced something Excel cannot solve — check that the cell you divided by is not blank. A #VALUE! error means one of the arguments is text; look for a stray apostrophe or a rate cell someone typed with a trailing percent sign into a text-formatted column. For a longer catalog of the same failure modes with screenshots, see Ablebits’ PMT function walkthrough.

Where to point PMT next

PMT is the entry point to a small family: PV solves for how much you can borrow given a payment budget, RATE reverses out an interest rate from a known payment, and NPER finds how many periods a payoff takes at a given payment. Once your inputs are laid out cleanly and the sign convention is under control, the rest of the family plugs in the same way. If you toggle often between two or three loan scenarios, wrap the calculator in SWITCH so a single input cell selects the active scenario — the payment formula itself never changes. For a ready-made single-formula template that follows the same input-in-one-column layout, see the Excel grade calculator.