The PMT function in Excel calculates the periodic payment for a loan or investment based on constant payments and a fixed interest rate. If you've ever wondered how lenders figure out your monthly mortgage payment, or how much you'd need to set aside each month to hit a savings goal — PMT is the formula doing the heavy lifting. You feed it three core numbers: the interest rate, the number of payment periods, and the present value (the loan amount or initial investment). Out comes a single answer: what you'll pay (or save) each period.
This function shows up constantly in real-world Excel work. Mortgage brokers use it. Financial analysts use it. Anyone building a personal budget in a spreadsheet probably leans on it without realizing the formula has a name. Once you understand the four arguments — and the two optional ones — you can model almost any fixed-payment scenario in under a minute.
PMT returns a negative number by default. That throws people off the first time they see it. Why negative? Because Excel treats payments as cash leaving your pocket. If you borrow $200,000, the bank gives you a positive $200,000 (cash in), and your monthly payments are negative (cash out). The sign convention is consistent — and you can flip it with a minus sign if a positive result reads better in your spreadsheet.
The full syntax looks like this: =PMT(rate, nper, pv, [fv], [type]). Three required, two optional. Get the first three right and you're 90% of the way there.
Rate is the interest rate per period. This is the trap most beginners fall into — they plug in the annual rate and wonder why the answer is wildly off. If your loan has a 6% annual rate and you're making monthly payments, you need 6%/12, or 0.005, in the rate slot. Match the rate to the payment frequency. Always.
Nper stands for number of periods — the total count of payments. A 30-year mortgage paid monthly is 360 periods, not 30. Same logic as rate: match the unit. Quarterly payments on a 5-year loan? That's 20 nper, not 5.
Pv is present value — the loan principal or the starting balance. For a $250,000 mortgage, pv is 250000. For a savings goal calculation, pv is whatever you've already saved (often zero).
Fv (optional) is future value — what you want the balance to be at the end. For loans this defaults to 0 (you've paid it off). For savings, it's your target. Want $50,000 in the account in 10 years? Fv equals -50000 (negative because that money comes back to you).
Type (optional) tells Excel when payments happen. Use 0 for end of period (the default — this covers most loans), and 1 for beginning of period (common in leases and annuities due).
Say you're buying a house with a $300,000 loan, a 7% annual interest rate, and a 30-year term. The formula reads =PMT(7%/12, 30*12, 300000). The result? About -$1,995.91 per month. That's your principal-and-interest payment. Taxes and insurance ride on top, but PMT handles the loan portion cleanly.
Try changing the rate to 6.5% and watch the payment drop to roughly -$1,896. That hundred-dollar swing across 360 months is $36,000 — which is why mortgage shopping matters so much. PMT lets you model these scenarios in seconds.
Three errors trip people up over and over:
=-PMT(...). Or flip the sign on pv. Either works.PMT has cousins. IPMT tells you how much of a specific payment goes to interest. PPMT tells you how much goes to principal. CUMIPMT sums interest paid across a range of periods. PMT gives you the total payment — IPMT and PPMT split that payment into its two parts. Use them together when you're building an amortization schedule.
If you're handling more complex spreadsheet work — looking up data across tables, building dropdowns, summing cells based on conditions — PMT plays well with the rest of the formula library. Once your excel formulas toolkit covers PMT, VLOOKUP, IF, and SUMIF, you can build almost any financial model from scratch.
Here's a practical layout. Set up four columns: Loan Amount, Rate, Term, Monthly Payment. Drop the PMT formula in the fourth column referencing the others. Now you can compare three or four loan offers side-by-side, and see exactly how much each one costs per month. Add a fifth column with =PMT(...)*nper to see total cost over the loan's life — including all the interest. That's where the real eye-opener happens.
Some folks hate seeing red negative numbers in a budget. You've got options. Wrap the whole formula in ABS — =ABS(PMT(rate, nper, pv)) — to force a positive result. Or use custom number formatting to display negatives without the minus sign. Or just feed PMT a negative pv. If you enter the loan as -300000, the payment comes out positive. Pick whichever convention reads cleanest for your audience.
PMT assumes constant payments and a fixed rate. The moment those assumptions break — variable-rate mortgages, irregular payment schedules, balloon payments mid-term — you need different tools. NPV and XNPV handle irregular cash flows. IRR finds the rate that makes a series of payments balance out. PMT is the right answer for vanilla scenarios, which is roughly 80% of the loan and savings math anyone does. For the other 20%, reach for the more flexible functions.
If you're prepping for a Microsoft Office Specialist exam or any of the major Excel certification tracks, PMT shows up. It's a staple of the financial functions section. Test writers love it because it forces you to think about unit conversion (rate per period), sign conventions, and optional arguments — three skills that translate to dozens of other Excel functions. Master PMT and you've got most of the financial-functions section covered.
Practice questions on Excel functions help cement these patterns. Working through scenarios — different rates, different terms, with and without future values — builds the intuition that lets you pick the right argument order without thinking. That's what separates someone who's read about PMT from someone who can use it under exam pressure.
The fastest way to lock in PMT is to build a small loan calculator. Open a blank workbook, drop in cells for loan amount, annual rate, and term in years. Reference them in a single PMT formula. Now experiment — change the rate, change the term, watch the payment shift. After ten or fifteen scenarios, you stop thinking about argument order and just type the formula straight through.
From there, layer on the related functions. Add IPMT and PPMT columns to break each payment into its interest and principal pieces. Add a running balance column. Suddenly you've got an amortization table — the kind of thing that takes accountants hours to build by hand and Excel about two minutes. That's the real value of excel formulas: they collapse complex math into a single cell, repeatably.
One last note. PMT works in Google Sheets, Apple Numbers, and most other spreadsheet apps with identical syntax. So once you've got it down, the skill ports anywhere you need to do quick payment math. That's portable knowledge — worth the half-hour it takes to fully understand.