FormulaSmith
Free tools

PMT

PMT(rate, nper, pv, [fv], [type]) returns the fixed periodic payment on a loan — the amount paid each period to clear the balance. Two details decide whether it is right. The rate and the number of periods must be in the same unit: a 6% annual rate on a monthly loan is 6%/12 over 30*12 periods, never 6% over 30. And the result comes back negative, because PMT signs cash flows from your side of the transaction and the payment is money leaving you; put a minus in front, or enter pv as a negative number, if you want it positive.

Back to FinanceAll guides

The formula

=PMT(B1/12, B2*12, B3)monthly payment: annual rate / 12, years * 12, loan amount=-PMT(B1/12, B2*12, B3)the same figure expressed as a positive number=PMT(B1, B2, B3)annual payments - rate and nper are both already annual=PMT(B1/12, B2*12, B3, 0, 1)payments at the START of each period (type = 1)=PMT(B1/12, B2*12, B3, -C1)a balloon of C1 still owing at the end; fv takes the opposite sign to pv=IPMT(B1/12, 1, B2*12, B3)the interest slice of payment number 1=PPMT(B1/12, 1, B2*12, B3)the principal slice of that same payment

A worked example

B1 holds the annual rate of 6%, B2 the term of 30 years, and B3 the loan amount of 250,000.

=PMT(B1/12, B2*12, B3)

-1,498.88 — the monthly payment, negative because the money leaves you. =-PMT(B1/12,B2*12,B3) gives 1,498.88 instead. The instructive failure is =PMT(B1, B2, B3): leaving the rate annual and the term in years returns -18,162.23, a perfectly valid annual payment that has nothing to do with the monthly one you asked for, and Excel raises no error because both arguments were internally consistent.

Which one do I need?

If you want to…Use
The monthly payment on a loan or mortgage=PMT(annual_rate/12, years*12, loan_amount) — the rate is divided and the periods multiplied together, or not at all
The answer is negative and you wanted a positive figurePrefix the call with a minus: =-PMT(...), or enter the loan amount as a negative pv. The sign is a cash-flow convention, not a mistake.
How much of a given payment is interest=IPMT(rate, period, nper, pv) — the same arguments plus which payment number you are asking about
How much of that payment reduces the balance=PPMT(rate, period, nper, pv) — IPMT and PPMT for the same period always sum back to PMT
A payment-by-payment schedule rather than one numberAn amortization table: PMT once at the top, then IPMT and PPMT filled down one row per period
You know the payment and want the term=NPER(rate, pmt, pv) — the same equation solved for the number of periods, so the rate must still be per period
You know the payment and want the interest rate=RATE(nper, pmt, pv) — returns the rate per period, so multiply by 12 to read it as an annual rate
You know what you can afford monthly and want the loan it buys=PV(rate, nper, pmt) — present value is PMT run in reverse

Frequently asked questions

Why is the PMT result negative?

Excel's financial functions sign every cash flow from your point of view. You receive the loan, so pv is positive, and you hand back the instalments, so the payment is an outflow and shows as negative. Read the minus as "paid out", negate the call with =-PMT(...), or enter pv as a negative number — all three produce the same magnitude.

What rate should I use in PMT for a monthly payment?

The rate for one period, not the annual rate. A 6% annual rate paid monthly is 6%/12, which is 0.5%, and nper has to match at years multiplied by 12. Pairing 6% with 360 periods asks for 6% per month over thirty years and returns a payment several times too large, with no error to flag it.

What do the fv and type arguments actually do?

fv is the balance still outstanding after the final payment — omit it for a loan repaid in full, or supply the balloon amount with the opposite sign to pv. type is 0 by default, meaning payments fall at the end of each period; set it to 1 for payments at the start, which lowers the payment slightly because every instalment then has one more period to earn its keep.

Does PMT work the same in Google Sheets?

Yes. PMT(rate, number_of_periods, present_value, [future_value], [end_or_beginning]) takes the same arguments in the same order and returns the same negative sign, so a formula copied either way needs no editing. IPMT, PPMT, NPER, RATE and PV all carry across unchanged as well.

PMT Function Excel3600PMT Excel Formula1300What Is PMT Function in Excel170

Other sub-topics

Formulas · 454Cells · 429Percent · 52CAGR · 19COUNT · 52Subtract · 25Compound Interest · 21Fn COUNTIF · 20Multiplication · 11P Value · 12COUNT Duplicates · 4Show Formulas · 19