Calculating Loan Payments with PMT in Google Sheets

August 7th, 2026

Loan math is the same problem over and over: given a principal, a rate, and a term, what do you pay each period — and how much of that payment is interest versus principal? Google Sheets packages that into a small family of functions: PMT, IPMT, PPMT, RATE, NPER, PV, and FV. This guide walks through them with a realistic amortization schedule in mind.

The one rule that keeps loan formulas honest

Financial functions expect rate and term in the same unit of time. For monthly payments on an annual percentage rate (APR), divide the APR by 12 and multiply years by 12:

=PMT(0.06/12, 30*12, 250000)

That is a $250,000 loan at 6% APR over 30 years of monthly payments. Sheets returns about -1,498.88 — negative because the cash is leaving your account. Wrap it in ABS (or put the principal as a negative and the payment as positive) if you prefer display signs that match how you think about the loan.

PMT — the fixed periodic payment

PMT answers: "What constant payment clears this loan?"

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

• rate — interest per period (APR/12 for monthly)
• nper — number of periods (years × 12)
• pv — present value / principal borrowed
• fv — optional balance left at the end (default 0)
• type — 0 pay at end of period (default), 1 at beginning

=PMT(0.05/12, 60, 20000)

A $20,000 car loan at 5% APR for 5 years (60 months) returns about -377.42 per month.

IPMT and PPMT — split one payment into interest and principal

Every fixed payment is two pieces that shift over time: early months are mostly interest; later months are mostly principal. IPMT and PPMT compute each piece for a given period number.

=IPMT(0.06/12, 1, 360, 250000)
=PPMT(0.06/12, 1, 360, 250000)

Period 1 of the $250k / 6% / 30-year mortgage: interest is about -1,250 and principal about -248.88. Together they equal PMT. By period 180 (year 15), interest has fallen and principal has risen — same total payment, different mix.

=IPMT(0.06/12, 180, 360, 250000)
=PPMT(0.06/12, 180, 360, 250000)

Check the identity any time:

=IPMT(B1, A2, B2, B3) + PPMT(B1, A2, B2, B3)

With B1 = monthly rate, A2 = period number, B2 = total periods, B3 = principal, that sum matches PMT(B1, B2, B3).

Building a simple payment schedule

A practical amortization layout:

| Cell | Meaning | Example | | --- | --- | --- | | B1 | Annual rate (APR) | 0.06 | | B2 | Years | 30 | | B3 | Principal | 250000 | | B4 | Monthly rate | =B1/12 | | B5 | Number of months | =B2*12 | | B6 | Monthly payment | =PMT(B4, B5, B3) |

Then rows for periods 1…n:

=IPMT($B$4, A10, $B$5, $B$3)
=PPMT($B$4, A10, $B$5, $B$3)

Put period numbers in column A starting at row 10. Absolute references on rate, nper, and principal let you fill down. Optional ending balance for period k:

=PV($B$4, $B$5-A10, $B$6)

That is the remaining principal after A10 payments (same sign convention as the rest of the model).

RATE — recover the interest rate from a known payment

You know the payment but not the APR — common when reverse-engineering a dealer offer:

=RATE(60, -450, 22000)*12

Sixty payments of $450 on $22,000 imply roughly 7.4% APR. RATE returns the per-period rate; multiply by 12 for a nominal annual rate comparable to quoted APR.

NPER — how many periods until paid off?

=NPER(0.06/12, -1500, 250000)

With a $1,500 monthly payment on $250,000 at 6% APR, you finish in about 258 months (~21.5 years) instead of 30 — the classic "pay extra and shorten the term" result.

PV and FV — value today vs value later

PV asks what a stream of payments is worth now; FV projects a balance forward.

=PV(0.05/12, 240, -1200)

Withdrawing $1,200/month for 20 years at 5% annual return requires about $183,000 on hand today.

=FV(0.07/12, 360, -500)

Saving $500/month for 30 years at 7% grows to roughly $610,000.

On a loan, PV is usually the amount borrowed and FV is often zero (fully paid). Leave a balloon with a non-zero fv on PMT if the loan does not amortize to zero.

Common mistakes

• Using annual rate with monthly periods. =PMT(0.06, 360, 250000) treats 6% as the monthly rate — wildly wrong. Always use APR/12 with month counts.
• Sign convention confusion. Money in and money out have opposite signs. Be consistent so PMT, IPMT, and PPMT line up.
• Off-by-one on period. IPMT/PPMT period is 1-based from the first payment, not a calendar month number unless your schedule starts at period 1.
• Comparing APR to monthly rate. When you solve with RATE, multiply by the payments-per-year before comparing to a quoted annual rate.

Which function should you pick?

| Goal | Function | | --- | --- | | Fixed payment amount | PMT | | Interest portion of one period | IPMT | | Principal portion of one period | PPMT | | Unknown APR from payment | RATE | | Unknown term | NPER | | Worth of payments today | PV | | Balance after contributions | FV |

Going further

For NPV, IRR, bonds, and the rest of the finance toolkit, see Mastering Financial Functions in Google Sheets. Full reference pages: PMT, IPMT, PPMT, RATE, NPER, PV, and FV.