Excel PMT Function — Calculate Loan & Mortgage Payments (Plus the Principal/Interest Split)

TL;DR — PMT(rate, nper, pv) returns the fixed periodic payment that pays off a loan of pv over nper periods at interest rate per period. The result is negative because it's money leaving your pocket. Two rules carry the whole function: the sign convention (cash out is negative, cash in is positive) and rate and period must match the payment frequency — a 6% annual mortgage paid monthly is PMT(6%/12, years*12, amount), not PMT(6%, ...). IPMT and PPMT split any single payment into its interest and principal parts. Works in every version of Excel.

=PMT(6%/12, 30*12, 200000)     ' monthly payment on a $200,000, 30-year, 6% mortgage -> -1199.10
=-PMT(6%/12, 30*12, 200000)    ' the same payment shown as a positive number -> 1199.10
=IPMT(6%/12, 1, 30*12, 200000) ' the interest portion of payment #1 -> -1000.00
=PPMT(6%/12, 1, 30*12, 200000) ' the principal portion of payment #1 -> -199.10

Ask a search engine "how do I calculate a loan payment in Excel" and you get a hundred pages that show you PMT and stop. But the two things that actually go wrong — why the answer is negative, and why it's sometimes off by a factor of twelve — are almost never the formula itself. This page leads with those, because getting them right is the difference between a mortgage payment of $1,199 and a nonsensical $12,000.

What you'll learn

• The mental model: PMT is a solver, not a formula
• The sign convention — why PMT is negative, and how to flip it
• The #1 numeric mistake: an annual rate with monthly periods
• All five arguments, including the optional fv and type
• Splitting a payment: IPMT (interest) and PPMT (principal)
• Building an amortization schedule that ties back to PMT
• When PMT isn't the function you need

The mental model: PMT is a solver

PMT doesn't "apply a formula" so much as solve an equation. Behind it sits the time-value-of-money relationship that links five quantities — the interest rate, the number of periods, the amount borrowed, any final balance, and the payment. You hand PMT four of them and it works out the fifth: the single, level payment that, made every period, drives the loan's balance to exactly zero on the last payment.

That framing matters because the other functions in this family — FV, PV, RATE, NPER — are the same equation solved for a different unknown. Learn PMT's conventions once and they transfer to all of them. (More on that in the FV & PV guide.)

The sign convention: why your payment is negative

The most common "bug" report about PMT isn't a bug at all:

=PMT(6%/12, 30*12, 200000)     ' -> -1199.10   (not a mistake!)

Excel's financial functions model cash flow from your point of view. Money coming in is positive; money going out is negative. When you take a 200000 loan, that cash arrives in your account, so pv is positive. The payments then flow out, so PMT reports them as negative. The minus sign is Excel telling you the direction of the money, not that something failed.

You have three clean ways to show a positive payment — pick one and be consistent across a model:

=-PMT(6%/12, 30*12, 200000)    ' negate the result -> 1199.10   (clearest)
=PMT(6%/12, 30*12, -200000)    ' make the loan negative -> 1199.10
=ABS(PMT(6%/12, 30*12, 200000))' force positive with ABS -> 1199.10

The rule you can carry to every function here: decide which direction is positive, keep every amount consistent with that choice, and the signs take care of themselves. Mixing them — a positive loan and a positive payment in the same relationship — is what produces impossible results.

Rate and period must agree — the #1 mistake

Loans are quoted as an annual rate but paid monthly. PMT has no idea which you mean; it just uses the numbers you give it, per period. So the rate and the period count must both be expressed in the same unit as the payment.

=PMT(6%/12, 30*12, 200000)     ' monthly: 6% a year is 0.5% a month, 30 years is 360 months -> -1199.10
=PMT(6%, 30, 200000)           ' yearly:  one payment a year for 30 years -> -14529.02
=PMT(6%, 30*12, 200000)        ' WRONG: 6% PER MONTH for 360 months -> -12000.86

That third line is the classic disaster — a "mortgage payment" of twelve thousand dollars a month. It happens because someone divided the years by nothing while keeping the annual rate. If the payment is monthly, divide the annual rate by 12 and multiply the years by 12. For weekly, use 52; for quarterly, 4. When a PMT result looks wildly too big or too small, check this first — it's almost always the culprit.

The five arguments in full

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

• rate — the interest rate per period (monthly rate for a monthly payment).
• nper — the total number of payments over the life of the loan.
• pv — the present value: the amount borrowed today.
• fv (optional) — the balance you want left after the last payment. Defaults to 0 (loan fully paid off). Set it to a non-zero value for a lease with a residual/balloon: =PMT(5%/12, 36, 30000, -5000) pays a car down to a 5000 buyout.
• type (optional) — 0 if payments are due at the end of each period (the default, an ordinary annuity), 1 if at the beginning (annuity-due, used for leases and rent paid in advance). Paying at the start saves a little interest, so type 1 gives a slightly smaller payment.

Splitting a payment: IPMT and PPMT

Every fixed payment is really two things bundled together — some interest on the balance you still owe, and some principal that actually reduces the loan. Early on the balance is large, so most of the payment is interest; near the end it's almost all principal. IPMT and PPMT pull those two pieces apart for any payment number per:

=IPMT(6%/12, 1, 30*12, 200000)   ' interest in payment 1  -> -1000.00
=PPMT(6%/12, 1, 30*12, 200000)   ' principal in payment 1 -> -199.10
=IPMT(6%/12, 360, 30*12, 200000) ' interest in the LAST payment -> -5.97
=PPMT(6%/12, 360, 30*12, 200000) ' principal in the last payment -> -1193.13

Notice that IPMT + PPMT always equals PMT for the same period — they're the two halves of the whole. The extra argument versus PMT is per, the specific payment you're asking about (1 to nper). For running totals across a range of payments — say, total interest in year one — use CUMIPMT and CUMPRINC:

=CUMIPMT(6%/12, 30*12, 200000, 1, 12, 0)   ' total interest across payments 1-12 -> about -11933
=CUMPRINC(6%/12, 30*12, 200000, 1, 12, 0)  ' total principal across payments 1-12 -> about -2456

(CUMIPMT and CUMPRINC require the type argument — the trailing 0 — and will return #NUM! if you omit it or if start/end fall outside 1..nper.)

Building an amortization schedule

Because IPMT and PPMT take the payment number as an argument, an entire amortization table is just those two functions dragged down a column, with the payment number coming from a helper column:

The whole schedule reconciles back to PMT: every row's payment is identical, interest shrinks and principal grows as the balance falls, and the final balance lands on zero. If your table doesn't end at zero, the rate-and-period units are almost certainly mismatched somewhere.

When PMT isn't the function you need

PMT answers "what's the payment?" But sometimes the payment is known and a different piece is missing. Because it's all one equation, Excel gives you a sibling for each unknown:

• Unknown rate → RATE ("what interest am I actually being charged?")
• Unknown number of periods → NPER ("how long until this is paid off?")
• Unknown starting or ending balance → PV and FV

All five share PMT's sign convention and rate/period discipline. The FV & PV guide covers the other four and the "Rule of Five" that connects them. And when cash flows aren't a level payment — an investment with lumpy, irregular returns — you leave this family entirely for NPV and IRR.

How ExcelMaster helps

Loan math goes wrong in predictable places: a forgotten /12, a payment shown positive in one cell and negative in the next, an amortization table that doesn't reconcile to zero. Tell ExcelMaster "build a 30-year amortization schedule for a $200,000 loan at 6%," and it lays out the columns, wires IPMT and PPMT to a payment-number column, matches the rate and period units, and checks that the final balance hits zero. Paste a PMT that looks ten times too big and it spots the annual-rate-with-monthly-periods trap and fixes it.

Frequently asked questions

Why is my PMT result negative?

Because Excel treats money leaving your pocket as negative. A loan pv is cash you receive (positive), so the payments you make come back negative. It's not an error. To display a positive figure, negate the formula — =-PMT(...) — or enter the loan amount as a negative number.

How do I calculate a monthly mortgage payment in Excel?

Divide the annual rate by 12 and multiply the years by 12: =PMT(annual_rate/12, years*12, loan_amount). For a $200,000 loan at 6% over 30 years that's =PMT(6%/12, 30*12, 200000), which returns -1199.10. Wrap it in a minus sign, =-PMT(...), if you'd rather see a positive payment.

What is the difference between PMT, IPMT, and PPMT?

PMT returns the whole periodic payment. IPMT returns just the interest part of one specific payment, and PPMT returns just the principal part. For any given payment number, IPMT + PPMT = PMT. Use IPMT/PPMT (or the cumulative CUMIPMT/CUMPRINC) to build an amortization schedule.

Why is my mortgage payment coming out to a huge number?

You're almost certainly feeding an annual rate with a monthly period count — =PMT(6%, 30*12, 200000) treats 6% as a monthly rate. Divide the rate by 12: =PMT(6%/12, 30*12, 200000). Rate and periods must both be in the same unit as the payment.

What do the fv and type arguments do?

fv is the balance remaining after the last payment — leave it at 0 for a loan paid in full, or set it for a lease with a residual/balloon. type controls timing: 0 (default) means payments at the end of each period, 1 means the beginning (used for leases and rent paid in advance), which slightly reduces the payment.

Tested in

Tested in: Excel 365 (Windows 11) — last verified 2026-07-23.

Related guides: Excel FV & PV · Excel NPV & IRR · 3-Statement Financial Model · Excel ROUND · Excel IF