Excel’s PMT function calculates the regular payment on a fixed-rate mortgage when you give it the interest rate per payment period, the total number of payments, and the loan amount. For a monthly mortgage, enter the annual rate divided by 12 and the loan term in years multiplied by 12; for example, a $300,000 loan at 6.5% for 30 years has a principal-and-interest payment of about $1,896.20 per month.
What PMT Calculates
PMT answers a specific question: what equal payment, made at regular intervals, will pay off a loan over a set number of periods at a stated interest rate? The function is based on the mathematics of amortization. Each payment covers interest accrued since the prior payment and reduces the remaining principal. Early in a long mortgage, the balance is large, so a greater share of each payment goes to interest. As the balance falls, the interest portion usually shrinks and more of the payment reduces principal.
The calculation assumes a constant rate and equal payment intervals. It returns the payment needed to bring the balance to the stated future value, normally zero for a fully repaid mortgage. It does not determine whether a lender will approve a loan, and it does not by itself represent every amount a borrower may owe each month.
The Math Behind the Payment
For a loan with a positive periodic interest rate, the payment is calculated as P × r ÷ [1 − (1 + r)−n]. Here, P is the starting principal, r is the interest rate for one payment period, and n is the number of payments. This formula accounts for the fact that payments made at different times have different values: a dollar paid later has less present value than a dollar paid sooner when interest is positive.
The same relationship can be understood by tracking the balance. During each period, the outstanding principal accrues interest; then the payment reduces the balance. A payment that is too small will leave a balance after the final period, while a sufficiently large payment pays it down to zero. PMT solves for the equal payment that makes the scheduled ending balance zero. If the periodic rate is zero, the calculation simplifies to principal divided by the number of payments.
PMT Syntax and Its Inputs
PMT uses this syntax: PMT(rate, nper, pv, [fv], [type]). The first three arguments are required. The optional arguments let you specify a remaining or target balance and whether payments occur at the beginning or end of each period.
- rate: the interest rate for one payment period, not automatically the annual rate.
- nper: the total number of payment periods.
- pv: the present value, or amount borrowed. Excel uses cash-flow signs: money received and money paid generally have opposite signs.
- fv: the desired balance after the final payment. For a mortgage intended to be fully paid off, this is normally zero.
- type: 0 means payment at the end of each period; 1 means payment at the beginning. If omitted, Excel uses 0.
For an ordinary monthly mortgage calculation, type 0 is usually the appropriate modeling assumption. The borrower’s actual first due date and payment schedule are set by the loan documents, so use the lender’s schedule for exact timing rather than treating PMT as a substitute for it.
Set Up a Mortgage Example in Excel
In Excel for Microsoft 365 on Windows, enter labels in column A and values in column B: put the loan amount in B2, annual interest rate in B3, and term in years in B4. For this illustration, use $300,000, 6.5%, and 30. The same formulas work in Excel for Mac and Excel for the web; only the surrounding interface differs, and no special menu command is required.
With the annual rate in B3, the monthly rate in B5, and monthly payments assumed, use this formula in B5:
=B3/12
It returns 0.5416667% per month, or 0.005416667 as a decimal. The rate must be expressed for the same period as the payments: dividing an annual rate by 12 is the standard nominal-rate conversion for a monthly-payment calculation.
With the loan term in years in B4 and the number of monthly payments in B6, use this formula in B6:
=B4*12
For a 30-year term, the result is 360 monthly periods. Then, with the monthly rate in B5, payment count in B6, and positive loan principal in B2, enter this formula in B7:
=-PMT(B5,B6,B2)
The result is approximately $1,896.20. The minus sign in front of PMT flips Excel’s cash-flow result to a positive number for display: borrowing is treated as cash received, while repayments are cash paid. Without that minus sign, this setup returns approximately -$1,896.20. The underlying calculation is not different; only the sign presentation is.
What the Worked Result Includes
The $1,896.20 figure is the scheduled monthly principal-and-interest payment under the stated assumptions. It is not necessarily the amount that will leave a homeowner’s bank account each month. Mortgage bills may also include property taxes, homeowners insurance, mortgage insurance, homeowners association charges, or other costs. Those items are not included in PMT unless they are separately modeled, and they are not all necessarily collected by the lender.
Because Excel stores more precision than it displays, a payment cell formatted to show two decimal places may contain additional decimal places internally. Use the unrounded PMT result when calculating totals; round only if the purpose of the worksheet is to model actual cent-based payments and the lender’s rounding rules are known. Small differences can accumulate across many periods.
With the positive payment in B7, payment count in B6, and original principal in B2, enter this formula in B8 to estimate total interest over the full scheduled term:
=B7*B6-B2
Using the unrounded payment, the result is about $382,633.44 in interest, and the total of all scheduled payments is about $682,633.44. These totals assume every payment is made as scheduled, the rate remains fixed, and the loan is fully repaid at the end. They do not include taxes, insurance, fees, or the effects of extra payments or refinancing.
Assumptions and Limits to Check
A PMT result is only as useful as its inputs. A fixed-rate mortgage is a natural fit for the calculation because its rate and scheduled payment are stable under the assumptions. An adjustable-rate mortgage can change its rate and payment, so one PMT result cannot describe the entire loan unless it is clearly labeled as a snapshot using a particular rate and period.
- Confirm whether the quoted annual rate is the nominal note rate or another measure. The annual percentage rate can include certain borrowing costs and is not simply a replacement for the note rate in a payment formula.
- Match the rate period and payment period. For monthly payments, use a monthly rate and a count of months; do not enter an annual rate with a monthly payment count.
- Check whether the loan term means the full original term or the remaining term. A refinance or a calculation partway through a loan needs the remaining balance and remaining periods, not the original principal and term.
- Remember that PMT assumes equal intervals and does not account for irregular first periods, skipped payments, fees, or a lender’s specific daily-interest conventions.
For exact amounts, compare your spreadsheet with the lender’s Loan Estimate, amortization schedule, or servicing information. This is an educational calculation, not financial, tax, or legal advice; check the actual loan terms with the lender or a qualified professional.
Common PMT Errors and Misconceptions
The most common mistake is entering 6.5% as the rate while requesting 360 monthly payments. That tells Excel to charge 6.5% every month, not every year, and produces a dramatically different result. The rate should be converted to the payment period, such as annual rate divided by 12 for this monthly example.
Another frequent issue is mixing years and months. A 30-year mortgage has 360 monthly payments, not 30. Entering 30 as nper would ask Excel to repay the loan in only 30 monthly installments. Also watch the sign of pv: a positive loan amount normally makes PMT negative because the payment is an outgoing cash flow. Negate the result for a positive display, or enter pv as negative if that better suits the rest of a cash-flow model.
A related misconception is that PMT gives the complete monthly housing cost. It does not. It gives the loan payment specified by the inputs, normally principal and interest. Another is that the payment tells you how much principal is repaid in every month; the principal portion changes over the life of an amortizing loan even when the total payment stays level.
PMT Compared with Other Mortgage Tools
PMT is useful when the question is the regular payment for a known amount, rate, and term. If instead you know the payment and want to solve for the loan amount, Excel’s PV function is designed for present value. If you want the number of periods, NPER can solve for that variable. These functions use related time-value-of-money assumptions, so their rate and period inputs must still be aligned.
For a period-by-period breakdown, Excel’s IPMT function calculates the interest portion for a specified period, while PPMT calculates the principal portion. An amortization table built from those values can show how the balance changes over time. PMT alone is simpler for a quick payment estimate; a table is more useful for examining the timing of interest and principal, extra payments, or a remaining balance. Any schedule should be checked against lender terms if it is being used to reconcile a real mortgage.
Frequently Asked Questions
What is the Excel formula for a monthly mortgage payment?
Use PMT with the annual rate divided by 12, the loan term in years multiplied by 12, and the principal. For the example above, the worksheet uses B5 for the monthly rate, B6 for the number of monthly payments, and B2 for principal, then calculates the positive display with =-PMT(B5,B6,B2).
Why does Excel show a negative PMT result?
Excel applies cash-flow signs. If the loan amount is entered as money received with a positive sign, the repayments are outgoing cash flows and PMT returns a negative value. Add a leading minus sign to the function when you want the displayed payment to be positive.
Does PMT include property tax and insurance?
No. PMT calculates the payment based on the loan’s rate, principal, term, and optional timing or future value. Taxes, insurance, mortgage insurance, and other charges must be calculated or entered separately if you want to estimate a broader monthly housing bill.
Can PMT calculate an adjustable-rate mortgage?
It can calculate a payment for a selected rate and period, but a single result will not represent future payment changes. For a changing-rate loan, model each rate period and its applicable balance and remaining term, then verify the method against the loan documents or lender schedule.
How can I calculate total mortgage interest in Excel?
For a fully amortizing fixed-rate example, multiply the unrounded regular payment by the number of payments and subtract the original principal. This assumes no extra payments, fees, or payment changes; lender-specific rounding can cause a small difference from the actual amount.
Related
- Unhide Rows in Excel: Shortcut Keys and Other Methods
- Unhide Columns in Excel Using the Keyboard
- Excel Unhide Shortcut: Reveal Hidden Rows and Columns
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Sort Excel Oldest to Newest: Dates, Data, and FixesTo sort dates in Excel from oldest to newest, select a cell in the date column, then choose…
- Excel MID Function: Extract Text from the Middle of a CellExcel’s MID function extracts a chosen number of characters from the middle of a text string. Its syntax…
- SUMPRODUCT Excel: Calculate a Weighted AverageIn Excel, calculate a weighted average with =SUMPRODUCT(A2:A5,B2:B5)/SUM(B2:B5), assuming A2:A5 contains the values and B2:B5 contains their weights.…
- ISBLANK Formula Examples in ExcelIn Excel, ISBLANK checks whether a referenced cell is genuinely empty and returns TRUE or FALSE. For example,…
- Excel IFS Function Examples for Multiple ConditionsExcel’s IFS function checks conditions in order and returns the value paired with the first condition that is…
- Excel Present Value Formula: PV Function and ExamplesIn Excel, use the PV function to calculate the present value of future cash flows: it discounts them…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.