Excel PMT Function

PMT calculates the payment required for a loan or investment, based on a constant interest rate and a fixed number of payments. It's the function behind every "what's my monthly payment" calculator you've ever used. The result comes back as a negative number by default, which confuses almost every first-time user, so we'll deal with that immediately.

Syntax

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

Parameter	Required	Description
rate	Yes	The interest rate for one payment period. If payments are monthly, this must be the monthly rate, not the annual rate.
nper	Yes	The total number of payments over the life of the loan.
pv	Yes	The loan amount today. Also called the principal.
fv	No	The balance you want left over after the last payment. Defaults to 0, meaning the loan is fully paid off.
type	No	When payments are due. 0 (default) means end of period. 1 means beginning of period.

Basic Example

You're financing a $30,000 car at 6.5% annual interest over 4 years, paid monthly.

=PMT(6.5%/12, 4*12, 30000)

// 6.5%/12  = the monthly interest rate
// 4*12     = 48 total monthly payments
// 30000    = the loan amount

This returns -$711.45. The result is negative because it represents money leaving your account each month. Divide the annual rate by 12 because the payments are monthly, and multiply the term in years by 12 to get the total number of payments. Skip either conversion and the answer will be wildly wrong with no error to warn you.

How PMT Works

Payments are shown as negative by default

PMT follows Excel's standard cash flow model: money you pay out is negative, money you receive is positive. A loan payment is an outflow, so PMT returns a negative value when pv is positive. Flip the sign on either the whole formula or on pv if you want a positive figure for a report.

Rate and nper must use matching time units

If rate is monthly, nper must also count monthly payments. Mixing an annual rate with a monthly nper (or vice versa) produces a number that looks plausible but is wrong by roughly a factor of 12. There's no warning. The formula just returns a bad answer.

fv defaults to zero

Leave fv out and PMT assumes you want the loan balance at zero after the final payment, which is what a standard amortizing loan does. You'd only supply a nonzero fv for something like a lease with a balloon payment at the end.

type defaults to end-of-period

With type omitted or set to 0, PMT assumes each payment happens at the end of the period, which matches how almost every mortgage, auto loan, and personal loan works. Set type to 1 only for annuities or leases where payment is due upfront, such as most rent agreements.

Payment frequency changes both rate and nper

Quarterly payments mean dividing the annual rate by 4 and multiplying the term in years by 4, not 12. Biweekly payments use 26 periods per year. Get the frequency wrong and both arguments are off, compounding the error.

Common Use Cases

Monthly mortgage payment

You're estimating the monthly payment on a $340,000 mortgage at 6.75% over 30 years.

=PMT(6.75%/12, 30*12, 340000)   // returns -$2,205.23

Quarterly equipment loan

Your business is financing $85,000 in equipment at 8% annual interest, paid quarterly over 5 years.

=PMT(8%/4, 5*4, 85000)

// 8%/4   = the quarterly rate
// 5*4    = 20 quarterly payments
// 85000  = the equipment cost

Total interest paid over the life of a loan

PMT tells you the payment, but multiply it out to see the real cost of borrowing.

=PMT(6.5%/12, 4*12, 30000) * (4*12) + 30000

// PMT(...) * nper  = total amount paid across all payments, shown as a negative outflow
// + 30000          = adds the original principal back, since pv was entered as positive, isolating total interest

For the $30,000 car loan above, this returns roughly -$4,149.53 in interest over 4 years.

Working backward from a target payment

Neither PMT nor its siblings solve for the loan amount directly. If you know you can afford $600 a month and want to know the maximum loan you can take out, use PV instead:

=PV(6.5%/12, 4*12, -600)   // returns the maximum loan amount for a $600 payment

Handling Errors

PMT throws errors far less often than lookup functions, but it does fail in a few predictable situations, usually #NUM! or #VALUE!.

Common causes of these errors:

• A text value in rate, nper, or pv instead of a number
• nper equal to 0

Blank input cells are a different problem. They also produce #VALUE!, but that's not an error you want to suppress silently. In a reusable loan calculator template, check for blank inputs explicitly instead of catching every error type:

=IF(OR(rate_cell="", term_cell="", loan_cell=""), "", PMT(rate_cell/12, term_cell*12, -loan_cell))

This clears the display until a user fills in all three inputs, but it still lets a genuine #VALUE! error, like a typo or text entered where a number belongs, show up instead of getting hidden along with the blanks.

Notes & Gotchas

What is the PMT function in Excel?

PMT is a financial function that calculates the fixed periodic payment for a loan or investment, assuming a constant interest rate and a constant number of payments. It's the standard tool for building loan calculators, mortgage estimators, and lease payment schedules.

What arguments does PMT require?

PMT requires three arguments: rate (the interest rate per period), nper (the total number of payments), and pv (the loan amount or principal). Two more arguments are optional: fv (the balance you want left after the last payment, default 0) and type (whether payments fall at the start or end of each period, default 0).

Why does PMT return a negative number, and how do you make it positive?

PMT returns a negative number because Excel models a loan payment as cash leaving your account. To get a positive result, place a minus sign in front of pv instead of the borrowed amount: =PMT(rate, nper, -pv). You can also negate the entire formula with =-PMT(rate, nper, pv). Both produce the same displayed result.

How do you convert an annual interest rate to a monthly rate for PMT?

Divide the annual rate by 12 if payments are monthly, by 4 if quarterly, and leave it unchanged if payments are annual. Forgetting this conversion is the single most common PMT mistake. A $30,000 loan at 6.5% calculated with the annual rate instead of the monthly rate returns a payment nearly 12 times too high, and Excel gives no warning that anything's wrong.

How do you calculate total loan payments using PMT?

Multiply the PMT result by nper to get the total amount paid over the life of the loan, expressed as a negative cash outflow. Add the original pv back to that negative total to isolate the interest paid, since pv itself was entered as a positive value. This two-step calculation is the fastest way to compare the true cost of two loan offers with different rates and terms.

Why does switching type from 0 to 1 change the payment amount?

Changing type from 0 to 1 shifts every payment one period earlier, which means each payment accrues interest for one period less. The result is a slightly smaller payment amount. This matters for lease agreements and some annuities where the contract specifies payment in advance rather than in arrears; standard mortgages and auto loans almost always use type 0.

Does PMT round the rate argument automatically?

No, PMT uses the full precision of whatever value you feed into rate. Problems show up when a user manually divides and rounds a rate before typing it in, say entering 0.0054 instead of the full 6.5%/12. Over a 30-year mortgage that small rounding error compounds into a payment that's off by several dollars a month. Always divide inside the formula rather than pre-calculating and rounding by hand.

Can PMT handle a loan with a variable interest rate?

No. PMT assumes one constant rate for the entire term, so it can't model an adjustable-rate mortgage or any loan where the rate changes partway through. For a variable-rate loan, you'd need to calculate PMT separately for each rate period and rebuild the remaining balance between segments, effectively running a fresh PMT calculation each time the rate resets.

Related Functions

Function	Use this when...
PV	You know the payment you can afford and need to work backward to the loan amount.
FV	You're calculating the future value of a series of regular investments instead of a loan payoff.
RATE	You know the payment, loan amount, and term, and need to solve for the interest rate instead.
NPER	You know the payment, rate, and loan amount, and need to solve for how many payments it will take to pay it off.