Excel Loan Payment Formula: How to Use PMT

Formulas & functions · Updated November 18, 2026

For a fixed-rate loan with monthly payments, Excel’s PMT function calculates the regular payment. If B1 contains the loan amount, B2 the annual interest rate, and B3 the term in years, enter =-PMT(B2/12,B3*12,B1) to show the monthly payment as a positive number. The example and guidance below explain how to set up the inputs, interpret the result, and account for the assumptions behind it.

What PMT calculates

PMT returns the payment for a loan or other arrangement with a constant interest rate and equal payments made at regular intervals. For a typical installment loan, each payment covers interest accrued during that period and reduces the amount owed. Early payments generally contain a larger interest share; later payments contain a larger principal share.

The function needs three main inputs: the interest rate for one payment period, the total number of payment periods, and the present value of the loan. In the opening formula, the annual rate is divided by 12 to get a monthly rate, and the number of years is multiplied by 12 to get the number of monthly payments. The loan amount is the present value, or amount borrowed.

The minus sign before PMT changes Excel’s cash-flow sign into a convenient positive payment amount. Without it, a positive loan amount will normally produce a negative payment because Excel treats money received and money paid as opposite cash flows. The sign does not change the size of the calculated payment.

Set up a worked monthly-payment example

Enter the example inputs in these cells: B1 is 25000, B2 is 6%, and B3 is 5. Format B2 as a percentage if it is not already displayed that way. This represents a $25,000 loan, a 6% annual rate, and a five-year term, with monthly payments. In B4, use the payment formula below.

With the loan amount in B1, annual interest rate in B2, and term in years in B3, put this formula in B4: =-PMT(B2/12,B3*12,B1). It returns approximately 483.32, so the modeled monthly payment is about $483.32.

There are 60 monthly payments in this example. Multiplying the unrounded payment by 60 gives total payments of approximately $28,999.20; subtracting the original $25,000 principal gives approximately $3,999.20 in interest over the full term. Because the displayed payment is rounded to cents, totals calculated from the displayed figure can differ slightly from totals based on Excel’s unrounded result. Actual lenders may also adjust the final payment or use different rounding practices.

To display the result as money, select B4 and use Home > Number Format to choose a currency or accounting format. Formatting changes how the value appears, not the underlying calculation. Keep the cell’s full precision for further calculations unless you have a specific reason to round it.

Choose the right rate and payment periods

The rate passed to PMT must match the length of a payment period. For a monthly payment schedule, a quoted annual rate is commonly divided by 12; for quarterly payments, the corresponding monthly conversion would not be appropriate. The number of periods must use the same schedule: five years of monthly payments means 60 periods, while five years of quarterly payments means 20.

Make sure the rate is entered as a percentage value. Typing 6% in B2 stores 0.06, which is the intended annual rate in the example. Typing 6 instead stores six as a decimal rate, equivalent to 600%, and produces a dramatically different answer. If the rate is provided as a decimal such as 0.06, that is also a valid stored value for 6%.

PMT assumes a constant rate and a consistent schedule of equal payments. A variable-rate loan, a loan with irregular payment dates, or a contract that calculates interest daily may not match this simple model exactly. Check the lender’s stated payment schedule and rate conventions before relying on a spreadsheet estimate.

Understand payment timing and optional arguments

PMT also accepts optional future-value and timing arguments. If omitted, the future value is treated as zero and payments are assumed to occur at the end of each period. That end-of-period assumption fits many standard installment loans. The timing argument can instead specify payments at the beginning of each period, as may happen with certain leases or annuities.

For a loan expected to be fully repaid, a zero future balance is usually the intended assumption. If a contract includes a planned balloon payment, however, the loan will not be paid down to zero through the regular installments alone. PMT can model a nonzero remaining balance when the future-value input is set appropriately, but the payment and balloon terms should be checked against the actual agreement.

Beginning-of-period payments accrue interest differently from end-of-period payments, so selecting the wrong timing assumption changes the result. Do not switch the timing option merely to make a result match a desired amount. Confirm when payments are due in the loan terms.

Handle a zero-interest loan

When the annual rate is zero, monthly interest is also zero and the payment is simply the principal divided by the number of payments. PMT can handle a zero rate, but a formula with an explicit zero-rate branch can make the calculation’s logic easier to inspect. It also avoids relying on a reader to recognize that the equal-payment result is just a division.

With B1 holding the amount, B2 the annual rate, and B3 the term in years, put this formula in B4: =IF(B2=0,B1/(B3*12),-PMT(B2/12,B3*12,B1)). At a zero rate for the same $25,000 and five-year term, it returns about $416.67 per month, because $25,000 is divided across 60 payments. At 6%, it returns the same approximately $483.32 as the basic PMT formula.

Build an amortization schedule

A payment amount alone does not show how the balance changes over time. An amortization schedule separates each payment into interest and principal, then tracks the remaining balance. For a simple monthly schedule, use columns A through F for payment number, opening balance, payment, interest, principal repaid, and ending balance. Keep the loan amount in B1, annual rate in B2, term in B3, and monthly payment in B4.

With that layout, enter 1 in A8 for the first payment period. With the original loan amount in B1 and the first opening balance in B8, enter =$B$1 in B8. With the annual rate in B2 and the first opening balance in B8, enter =B8*$B$2/12 in D8 to calculate first-period interest. This assumes interest is calculated monthly at one-twelfth of the annual rate.

With the payment amount in B4 and first-period interest in D8, enter =$B$4 in C8 for the payment. With the payment in C8 and interest in D8, enter =C8-D8 in E8 for principal repaid. With opening balance in B8 and principal in E8, enter =B8-E8 in F8 for the ending balance.

For the next period, with the prior ending balance in F8, put =F8 in B9. With the fixed payment stored in B4, put =$B$4 in C9. With the next opening balance in B9 and annual rate in B2, put =B9*$B$2/12 in D9. Then, with payment in C9 and interest in D9, put =C9-D9 in E9; with opening balance in B9 and principal in E9, put =B9-E9 in F9. Copy the formulas in row 9 down for later periods, updating the payment number in column A. Minor rounding differences can leave a tiny balance in the final row; lender payoff calculations may use their own rounding and interest rules.

What PMT leaves out

PMT calculates a scheduled payment from the values supplied; it does not identify every cost associated with borrowing. The result does not automatically include origination charges, late fees, optional insurance, property taxes, homeowners insurance, or other amounts that may be billed alongside a loan payment. For a mortgage, for example, the principal-and-interest estimate is not necessarily the full amount due each month.

A quoted annual percentage rate may reflect certain costs differently from the interest rate used to calculate periodic interest. Use the rate and method that match the lender’s payment schedule, and compare the spreadsheet result with the lender’s disclosed payment details. Fees and charges should be entered separately if you want to model a broader cash cost; simply adding them to the principal is not necessarily how the lender calculates them.

This is an illustrative spreadsheet calculation, not financial, tax, investment, or legal advice. Loan agreements can use different conventions, and a spreadsheet estimate is not a payoff quote. Check the actual terms with the lender or a qualified professional before making decisions based on a payment or total-interest estimate.

Common errors and how to fix them

  • A negative result: This is the normal cash-flow sign convention when the amount borrowed is positive. Put a minus sign before PMT if you want the payment displayed as a positive number.
  • A payment that is far too large: Check whether the rate was entered as 6 rather than 6%, and confirm that the annual rate was converted to a monthly rate.
  • A term that is too short or too long: For monthly payments, multiply years by 12. Entering five periods instead of 60 would describe five monthly payments, not five years.
  • A payment that differs from the lender’s quote: Verify the principal, payment frequency, rate, term, payment timing, and any fees included in the quote. The lender may also use a different interest calculation or rounding convention.
  • An unexpected formula error: Check that the referenced cells contain numeric values and that parentheses are balanced. In some regional settings, Excel uses a semicolon rather than a comma as the formula argument separator; use the separator configured for your installation if Excel rejects the formula.

The PMT function is available in Microsoft Excel for Microsoft 365 on Windows, and the same function and calculation are available in Excel for Mac and Excel for the web. Differences in regional settings can affect how formulas are entered, but not the underlying payment logic.

Frequently Asked Questions

What is the Excel formula for a monthly loan payment?
Use PMT with a monthly rate, monthly period count, and loan amount. If B1 is principal, B2 is annual rate, and B3 is years, enter =-PMT(B2/12,B3*12,B1) to return a positive payment amount.

Why does PMT show a negative number?
Excel uses cash-flow signs: borrowed funds received are positive, while payments made are negative. The negative sign before PMT reverses the returned sign so the payment displays as a positive amount.

Does PMT include taxes, insurance, and fees?
No. PMT calculates a payment from the values and assumptions you provide. It does not automatically add taxes, insurance, lender fees, or other charges that may be part of the total bill.

Can PMT calculate a weekly or biweekly payment?
Yes, if the rate and number of periods use the same schedule. Convert the rate to the rate per payment period and calculate the number of payments in the term. Do not use a monthly rate with a weekly period count; confirm the lender’s rate-conversion method first.

Can I use PMT when the interest rate changes?
PMT assumes a constant rate for the periods being calculated. For a variable-rate loan, a single PMT result is only an estimate under the selected rate. Recalculate using updated terms or model changing periods separately, and compare the result with the lender’s schedule.

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.