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. Practice with the free Excel practice test on PracticeTestGeeks (500+ questions with answer explanations).
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.
Facts verified Oct 17, 2026 against Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business. Dates that have passed are removed automatically.
Facts verified Oct 17, 2026 against Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business. Dates that have passed are removed automatically.
| Item | Detail | Official source |
|---|---|---|
| Number of questions (Excel Associate (MO-210)) | 33-37 questions | certiport.pearsonvue.com |
| Number of questions (Excel Expert (MO-211)) | 23-27 questions | certiport.pearsonvue.com |
| Time limit | 50 minutes (60 minutes seat time) | Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business |
| Passing score | 700 on a 1–1000 scale | certiport.pearsonvue.com |
| Retake policy | Wait 24h after 1st fail, 48h before each later retake; no annual limit | Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business |
| Validity | Valid 5 years (earned on/after Sept 23, 2025) | certiport.pearsonvue.com |
| Delivery | Proctored live-in-the-application exam via Certiport Authorized Testing Centers (in-center or remote-proctored) | Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business |
| Eligibility | No formal prerequisites; about 150 hours of instruction and hands-on experience recommended | Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business |
| Score release | Score report at end of exam; also in Certiport profile | certiport.pearsonvue.com |
Facts verified Oct 17, 2026 against Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business. Dates that have passed are removed automatically.
Interactive Excel resources on PracticeTestGeeks, free to start:
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.
For a deeper look at this topic, see our guide to pmt function excel.
Try these questions from our free Excel practice tests. The correct answer and an explanation follow each question.
Which function calculates the payment for a loan based on constant payments and a constant interest rate?
Answer: D. PMT
PMT(rate, nper, pv) calculates the fixed periodic payment required to pay off a loan over a set number of periods at a given interest rate.
What does the TEXT function do?
Answer: B. Formats a number as text using a format code
TEXT converts a numeric value to text with a specific format.
You have a formula that calculates a loan payment. You know the desired monthly payment amount, the loan term, and the interest rate, but you need to determine the maximum loan principal you can afford. Which Excel tool allows you to work backward from a desired result to find the required input value?
Answer: D. Goal Seek
Goal Seek is the ideal tool for this situation. It is part of the What-If Analysis tools and is used to find a specific input value that will produce a desired result in a formula. In this case, you set the payment cell to your desired value and tell Goal Seek to change the loan principal cell to find the correct input.
Which of the following formulations for the IF function is correct?
Answer: C. =IF(condition, condition if true, condition if false)
The IF function in Excel evaluates a logical test (condition) and returns one value if the condition is true, and another value if the condition is false. Its correct syntax is =IF(condition, value_if_true, value_if_false). This structure allows for conditional logic within spreadsheets, making it a powerful tool for decision-making.
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.