Excel PMT Function for Mortgage Payments

Formulas & functions · Updated November 22, 2026

Use Excel’s PMT function to estimate a fixed, regular mortgage payment for principal and interest. For a $300,000 loan at 6.5% annual interest over 30 years, enter =PMT(6.5%/12,30*12,300000); Excel returns about -$1,896.20 because it treats the payment as cash flowing out. Use a minus sign before PMT if you want the result displayed as a positive payment.

What PMT calculates

PMT calculates the constant payment needed to repay a loan or reach a savings goal when the interest rate stays the same and payments occur at regular intervals. For a conventional fixed-rate mortgage, it can estimate the recurring principal-and-interest amount. It does not determine whether you qualify for a loan, predict a lender’s approval terms, or calculate every cost of owning a home.

The function’s syntax is PMT(rate,nper,pv,[fv],[type]). The first three arguments are required:

  • rate is the interest rate for one payment period, such as one month.
  • nper is the total number of payment periods.
  • pv is the present value, usually the amount borrowed.

The optional fv argument is the balance you want left after the final payment; for a fully repaid mortgage it is usually zero. The optional type indicates when payments are due: 0 means at the end of each period, and 1 means at the beginning. If omitted, Excel uses 0, which matches the usual assumption for a standard monthly mortgage payment.

Set up the mortgage inputs in Excel

It is easier to audit a formula when the loan details are in separate cells rather than typed directly into it. In a blank worksheet in Excel for Microsoft 365 on Windows, enter labels in A1:A4 and values in B1:B4:

  • A1: Loan amount; B1: 300000
  • A2: Annual interest rate; B2: 6.5%
  • A3: Term in years; B3: 30
  • A4: Monthly principal-and-interest payment; leave B4 for the formula

With the loan amount in B1, annual rate in B2, and term in years in B3, enter =-PMT(B2/12,B3*12,B1) in B4. It returns approximately $1,896.20 as a positive number. The minus sign reverses PMT’s normal cash-flow sign; without it, the result is approximately -$1,896.20. Both formulas describe the same payment.

In this setup, dividing the annual rate by 12 converts it to a monthly rate, and multiplying years by 12 converts the term to a number of monthly payments. Format B4 as Currency if you want a dollar sign and two decimal places. Formatting changes how the number appears, not the underlying calculation.

Worked example: a $300,000, 30-year mortgage

Assume a fixed annual rate of 6.5%, monthly payments, and a loan principal of $300,000. There are 30 × 12, or 360, payments. Excel calculates a payment of about $1,896.20 per month for principal and interest. The first argument is 6.5% divided by 12, not 6.5% for every month.

Over 360 payments, the total paid for principal and interest is roughly $682,633, using the unrounded payment amount. Subtracting the $300,000 borrowed gives about $382,633 in interest over the full term. These totals are estimates based on the stated assumptions; a lender’s payment schedule can differ because of its exact rate, payment dates, rounding, and loan terms.

The payment remains level in this fixed-rate example, but the mix changes. In the first month, interest is approximately $1,625: $300,000 multiplied by 6.5% and divided by 12. The remaining roughly $271.20 of the $1,896.20 payment reduces principal. Later, interest is calculated on a smaller balance, so more of each level payment goes toward principal.

Match the rate and payment periods

The rate supplied to PMT must use the same time unit as the payment periods. For monthly payments, use a monthly rate and a monthly period count. For quarterly payments, use a quarterly rate and the number of quarters. A mismatch is one of the most common reasons a result looks implausible.

For a typical fixed-rate mortgage quoted with a nominal annual note rate and monthly payments, dividing that annual rate by 12 is the common estimate. Do not divide the rate by 12 if the cell already contains a monthly rate. Also enter a percentage as a percentage: 6.5% is stored as 0.065 in Excel. Typing 6.5 into a cell and treating it as 6.5% would make the calculation wildly different unless the value is first converted.

The quoted annual percentage rate (APR) may include certain costs beyond the note rate. It is not automatically the rate to use for calculating the scheduled principal-and-interest payment. Check the lender’s stated note rate and payment terms rather than substituting APR without understanding what it represents.

What the PMT amount includes—and leaves out

PMT calculates a payment from the loan amount, interest rate, term, and payment timing. It does not automatically add property taxes, homeowners insurance, mortgage insurance, homeowners association dues, closing costs, or lender fees. Those expenses may substantially change the amount a household pays each month, even when the principal-and-interest estimate is correct.

Some lenders collect property taxes and insurance through an escrow account, combining them with the mortgage bill. In that case, the bill can be higher than the PMT result. The PMT result is still useful for isolating principal and interest, but it is not a complete estimate of housing costs. Ask the lender for a payment breakdown and verify which charges are included in any quoted monthly figure.

PMT also assumes its rate and payment pattern remain consistent. It is not a full model of an adjustable-rate mortgage, where the interest rate and payment can change under the loan’s adjustment rules. Nor does a basic PMT formula account for a late payment, a skipped payment, extra principal payments, or a change in the loan balance.

Use PMT to compare loan scenarios

Once the inputs are in cells, you can change one assumption at a time and see how the estimated payment responds. For example, keep the same loan amount and rate while changing the term from 30 years to 15 years. The shorter term generally produces a larger monthly principal-and-interest payment, but fewer scheduled payments and less total interest under otherwise identical assumptions. A longer term generally lowers the scheduled payment while extending repayment and increasing total interest.

You can also compare different principal amounts or rates. A larger down payment may mean borrowing less, so try the smaller loan amount in B1 rather than changing the rate. To compare rates, change B2. Keep the assumptions consistent across scenarios, and record each result so that you do not accidentally compare a 15-year payment with a 30-year total or mix different loan amounts.

PMT can estimate the payment for a loan with a remaining balance as well as a new loan, provided you enter the balance as the present value and use the remaining number of periods. This can help illustrate how a refinance payment might differ under assumed terms, but it does not include refinancing costs or establish whether refinancing is worthwhile. Confirm actual offers, fees, and payment schedules with the lender or a qualified professional. This spreadsheet calculation is educational, not financial, tax, investment, or legal advice.

Common PMT errors and how to fix them

  • Payment is far too large: Check whether the rate is entered as 6.5 rather than 6.5%, or whether you forgot to divide an annual rate by 12 for monthly payments.
  • Payment is far too small: Check that the loan term was converted to months. Entering 30 as nper means 30 payment periods, not 30 years of monthly payments.
  • The answer is negative: That is normal under Excel’s cash-flow convention when the borrowed amount is positive. Add a minus sign before PMT to display the payment as positive, or keep the negative sign if you want inflows and outflows represented consistently.
  • The formula gives an error or unexpected result: Check that the input cells contain numbers, that the rate and period units match, and that the loan amount is positive. Avoid typing currency symbols directly into a number stored as text.
  • The result differs slightly from the lender’s figure: Compare the exact note rate, loan balance, payment frequency, start date, rounding method, and any included charges. A small difference can arise from rounding or timing assumptions.

When using formulas from a web page or another spreadsheet locale, note that Excel commonly uses commas between function arguments in the configuration assumed here. Some regional settings use different separators. If Excel rejects a formula with commas, check the list-separator setting for your installation rather than changing the mortgage calculation itself.

PMT versus a full amortization schedule

PMT gives one repeated payment amount; it does not show the balance after each payment. An amortization schedule lists each period’s beginning balance, interest, principal paid, and ending balance. That detail is useful when you want to see when the balance reaches a particular amount or model extra payments. A lender’s schedule is the better reference for the actual contract because it reflects the loan’s specific terms and dates.

For a quick estimate or a comparison of fixed-rate scenarios, PMT is usually the simpler tool. For irregular payments, changing rates, fees financed into the loan, or additional principal contributions, a more detailed cash-flow model may be needed. Excel also has other financial functions, but choosing a different function does not remove the need to align rates, periods, and cash-flow assumptions.

Frequently Asked Questions

What is the Excel formula for a monthly mortgage payment?
For a loan amount in B1, annual rate in B2, and term in years in B3, enter =-PMT(B2/12,B3*12,B1). The minus sign displays the payment as positive. The result covers principal and interest, not every housing expense.

Why does Excel PMT show a negative mortgage payment?
Excel uses cash-flow signs: money received as a loan is positive in the formula, while payments going out are negative. Using =-PMT(B2/12,B3*12,B1) with the same cell layout makes the displayed payment positive.

Should I use APR or the interest rate in PMT?
Use the periodic rate that matches the loan’s payment calculation, commonly the stated annual note rate divided by 12 for monthly payments. APR can incorporate certain costs and is not necessarily the rate used to set the scheduled payment. Check the lender’s disclosures.

Does PMT include taxes and homeowners insurance?
No. A standard PMT calculation estimates principal and interest only. Taxes, insurance, mortgage insurance, escrow amounts, and other charges must be considered separately unless you are using a different model that explicitly includes them.

Can PMT calculate a mortgage with extra payments?
PMT alone assumes the same regular payment each period and does not model extra principal payments. You can use it as a baseline, but a period-by-period amortization schedule is needed to represent extra payments and their effect on the balance or payoff date.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.