Calculating Loan Payments in Excel: PMT and Examples

Formulas & functions · Updated November 21, 2026

To calculate a fixed monthly loan payment in Excel, enter the loan amount, annual interest rate, and term in years, then use the PMT function: =-PMT(B2/12,B3*12,B1). If B1 is $20,000, B2 is 6%, and B3 is 5, the result is about $386.66 per month, assuming monthly payments at the end of each month and no extra fees.

What the PMT function calculates

PMT calculates the regular payment needed to pay off a loan with a constant interest rate and equal payments over a set number of periods. It is useful for estimating a standard fixed-rate installment loan, such as a car loan or a fixed-rate personal loan. The same function can also calculate a mortgage payment for principal and interest, but it does not automatically include property taxes, insurance, or other housing costs.

The function takes three required inputs: the interest rate for one payment period, the total number of payments, and the present value, usually the amount borrowed. It also accepts an optional future value and a payment-timing argument. For an ordinary loan, future value is normally zero, and payments are normally made at the end of each period.

Excel treats money received and money paid as opposite cash-flow directions. If the borrowed amount is entered as a positive number, PMT usually returns a negative payment. Placing a minus sign before PMT, as in the opening example, displays the payment as a positive amount. Alternatively, enter the loan amount as a negative value. This is a display convention, not a change to the calculation.

Set up the loan inputs

A small, labeled input area makes assumptions visible and makes it easier to change them without rewriting a formula. In a blank worksheet, enter the following labels and values:

  • A1: Loan amount; B1: 20000
  • A2: Annual interest rate; B2: 6%
  • A3: Term in years; B3: 5
  • A4: Payments per year; B4: 12

Format B1 as Currency and B2 as Percentage using the Number group on the Home tab. The values themselves should remain numeric: type 6% or 0.06 for the annual rate, not the text 6 percent. The payment frequency matters. Twelve payments per year describes monthly payments; it is not appropriate for every loan arrangement.

With the loan amount in B1, annual nominal rate in B2, years in B3, and payments per year in B4, the general formula for a positive payment is =-PMT(B2/B4,B3*B4,B1). Dividing the annual rate by payments per year gives the rate per payment period, and multiplying the term by payments per year gives the number of payments. For the monthly example, B4 is 12, so this is equivalent to dividing the annual rate by 12 and multiplying years by 12.

Work through a monthly payment example

For the example, B1 is $20,000, B2 is 6%, B3 is 5, and B4 is 12. Put a label such as Monthly payment in A5. With those inputs in B1:B4, enter this formula in B5: =-PMT(B2/B4,B3*B4,B1). Excel returns approximately $386.66. The calculation uses a monthly rate of 0.5% and 60 total payments.

Multiplying the unrounded payment by 60 gives total scheduled payments of about $23,199.36. Subtracting the original $20,000 principal gives about $3,199.36 in interest over the full term, assuming the rate and payment schedule remain unchanged. These totals are estimates based on the mathematical payment amount; a lender may round payments to cents and adjust the final payment slightly.

To check the result conceptually, the monthly payment must cover that month’s interest and reduce some principal. The initial month’s interest is approximately $100: $20,000 multiplied by 0.5%. The remainder of the $386.66 payment, about $286.66, reduces the balance. In later months the balance is lower, so less interest accrues and more of the same payment goes toward principal.

Match the rate and payment frequency

A common source of a wrong answer is using an annual rate as if it were a monthly rate. PMT expects the rate for one payment period, not automatically the advertised annual rate. For monthly payments, divide a nominal annual rate by 12; for quarterly payments, divide it by 4. The number of periods must use the same frequency.

For instance, a 6% nominal annual rate with monthly payments is commonly modeled as 6% divided by 12, or 0.5% per month. This calculation assumes the lender quotes a nominal annual rate that can be divided across periods. Some products may use a different compounding convention, a daily rate, or other contract-specific terms. In those cases, the lender’s payment schedule and stated method take precedence over a simplified spreadsheet estimate.

Do not confuse biweekly with twice monthly. A biweekly schedule has 26 payments in a typical year, while twice-monthly payments total 24 per year. Although the spacing may seem similar, the annual number and timing of payments differ, so entering the wrong frequency changes the calculation. For irregular dates, variable rates, or payments that change over time, basic PMT is not a complete model.

Understand payment timing and optional arguments

PMT’s optional type argument specifies when payments occur. Omit it, or use 0, for payments at the end of each period, which is the usual assumption for an ordinary loan. Use 1 when payments are due at the beginning of each period, as can occur in some annuity or lease arrangements. Because an early payment accrues less interest before it is made, a beginning-of-period payment for the same loan inputs is generally lower.

With annual rate in B2, years in B3, principal in B1, and monthly payments assumed, the beginning-of-month version is =-PMT(B2/12,B3*12,B1,0,1). The fourth argument is the future value, set to zero here, and the fifth is the type. Use this only when the contract actually calls for payments at the beginning of each period; changing the argument simply to get a preferred result would misrepresent the loan.

Build an amortization schedule

A payment amount alone does not show how the balance changes. An amortization schedule separates each payment into interest and principal, then tracks the remaining loan balance. This is useful for seeing how much principal is outstanding at a particular payment date and for checking that a fixed-payment estimate eventually pays the balance down.

Using the input cells above, create headings in A8:D8: Period, Payment, Interest, and Principal. Enter 1 in A9, then 2 in A10 and continue down to 60. With period number in A9, the annual rate in B2, term in B3, and principal in B1, the first-period interest is =-IPMT($B$2/12,A9,$B$3*12,$B$1). It returns approximately $100.00 for period 1 in the example.

With the same inputs and the period number in A9, the principal portion for that period is =-PPMT($B$2/12,A9,$B$3*12,$B$1). It returns about $286.66 for the first payment. The minus signs display the outflows as positive figures. The payment in the example is the sum of those components, subject to unrounded values: about $100 interest plus $286.66 principal.

To continue the schedule, fill the interest and principal formulas down alongside the period numbers. Add a balance column if you want to track the unpaid principal: the first closing balance is the original principal less the first principal portion, and each later opening balance is the preceding closing balance. Avoid rounding the formula results to cents at every step; formatting cells to show two decimals is usually better, because rounding intermediate calculations can cause the final balance to differ. A real lender’s final payment can still vary due to dates, rounding, or contract rules.

What PMT leaves out

PMT estimates a payment for a level-payment loan under the assumptions entered. It does not discover the lender’s fees or translate an advertised rate into the complete cost of borrowing. Application or origination fees, required insurance, taxes, balloon payments, late charges, and variable-rate changes may affect what a borrower actually pays. Some costs are paid up front; others are included in a periodic bill, so they should not be treated as interchangeable.

For a mortgage, distinguish principal and interest from the full monthly housing payment. Escrow for property taxes and homeowners insurance may be collected with the mortgage payment, and mortgage insurance or association charges may also apply. Those items are not included in a PMT result unless they are separately added to a budget calculation. Likewise, a loan with a balloon payment or a changing rate needs a model that reflects those terms instead of assuming 60 or 360 identical payments.

Use Excel’s estimate to understand the mechanics or compare clearly defined scenarios, then check the actual payment schedule, fees, and rate method with the lender. This is general spreadsheet information, not financial, tax, investment, or legal advice; consult the lender or a qualified professional for terms that affect a real borrowing decision.

Common errors and ways to check the worksheet

  • Entering 6 instead of 6%: A numeric 6 means 600% when interpreted as a rate. Enter 6% or 0.06.
  • Leaving the rate annual: Convert the rate to the payment period, such as B2/12 for monthly payments under the usual nominal-rate assumption.
  • Using years as the number of periods: A five-year monthly loan has 60 payments, not five.
  • Getting a negative result: This is often Excel’s cash-flow sign convention. Reverse the sign of either the present value or the displayed PMT result.
  • Expecting the result to include every charge: Check whether the quoted amount includes insurance, taxes, fees, or other items beyond principal and interest.

For a basic validation, confirm that the payment is greater than the first month’s interest when the loan has a positive rate and a finite repayment term; otherwise, the balance may not decline as expected. Check that the final schedule balance is close to zero before considering lender-specific rounding. If a large balance remains, review the annual-versus-periodic rate, payment count, principal, and timing assumption before assuming Excel is at fault.

Frequently Asked Questions

What is the Excel formula for a monthly loan payment?
Use PMT with a monthly rate and the total number of monthly payments. If B1 contains the principal, B2 the annual nominal rate, and B3 the term in years, enter =-PMT(B2/12,B3*12,B1). The minus sign shows the payment as positive.

Why does Excel show my loan payment as a negative number?
PMT uses cash-flow signs: the loan proceeds and payments go in opposite directions. If the principal is entered as a positive present value, Excel can return a negative payment. Put a minus sign before PMT to display the payment as a positive number, or enter the principal as negative.

Does PMT include interest?
Yes. PMT calculates a level payment that covers principal and interest under the entered rate, term, and timing assumptions. It does not automatically include separate fees, taxes, insurance, or other charges.

How do I calculate total interest in Excel?
For a fixed-rate loan with equal payments, multiply the unrounded periodic payment by the number of payments, then subtract the original principal. For the example, about $386.656 multiplied by 60, less $20,000, is about $3,199.36. A lender’s exact total can differ because of rounding, dates, fees, or contract terms.

Can Excel calculate a loan with a variable interest rate?
PMT assumes one constant rate for all periods. For a variable-rate loan, you would need to model each rate period and the lender’s recalculation rules, including how the payment or term changes. Review the contract or ask the lender for the applicable schedule rather than treating one PMT result as the actual variable-rate payment.

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.