Excel Payment Calculator: Calculate Loan Payments With PMT

Formulas & functions · Updated November 18, 2026

To make an Excel payment calculator for a fixed-rate loan, use PMT: with the loan amount in B1, annual interest rate in B2, payments per year in B3, and term in years in B4, enter this in B5: =PMT(B2/B3,B4*B3,-B1). For a $20,000 loan at 6% over five years with monthly payments, the result is about $386.66 per month.

What Excel’s PMT function calculates

PMT calculates the regular payment needed to repay a loan, or reach a specified future value, when the interest rate and payment amount remain constant. It is useful for estimating a car loan, personal loan, mortgage payment, or another installment arrangement. It is a calculation function, not a built-in loan application or a source of lender-specific terms.

The function balances the amount borrowed against periodic interest and payments over a set number of periods. Its inputs correspond to the interest rate for one payment period, the total number of payments, and the present value of the loan. For the common case of a fully repaid loan, the future value is zero and the payment is made at the end of each period, which is PMT’s default.

In the example, B2 contains 6% as an annual rate, B3 contains 12 payments per year, and B4 contains 5 years. The monthly rate is therefore 6% divided by 12, and there are 5 multiplied by 12, or 60, payments. Keep the rate and payment frequency consistent: a monthly payment needs a monthly rate and a monthly payment count.

Set up the calculator in a worksheet

In a blank worksheet, add labels in column A and values in column B. For example, put Loan amount in A1 and 20000 in B1; Annual interest rate in A2 and 6% in B2; Payments per year in A3 and 12 in B3; and Term in years in A4 and 5 in B4. Put Monthly payment in A5. With those values in B1:B4, enter the PMT formula in B5:

=PMT(B2/B3,B4*B3,-B1)

The returned value is approximately $386.66. Format B1 and B5 as currency and B2 as a percentage using the number-format controls on the Home tab. Enter 6% as 6%, rather than 6, which Excel would interpret as 600%. You can change the principal, rate, frequency, or term and the payment updates automatically.

The minus sign before B1 is intentional. Excel represents money received and money paid as opposite cash-flow signs. Treating the borrowed principal as money received by the borrower makes the calculated payment positive. If you enter the principal as a positive value without changing the sign convention, PMT will normally return a negative payment. That negative result is not an error; it indicates money going out.

Calculate the total paid and interest

A monthly payment is only one part of the cost picture. With the example inputs in B1:B4 and the payment in B5, put Total paid in A6 and enter this in B6:

=B5*B4*B3

It returns approximately $23,199.36 over 60 payments, using the unrounded payment stored in B5. With the same layout, put Interest paid in A7 and enter this in B7:

=B6-B1

The result is about $3,199.36. The displayed monthly payment may be rounded to cents, but Excel retains more precision internally unless the workbook is specifically set to use displayed precision. For a reliable total, leave the underlying payment unrounded and format its display as currency rather than applying a rounding function to the calculation.

These totals assume the entire loan is paid through the same fixed payment and rate, with no fees, insurance, taxes, late charges, or changes in terms. A lender may calculate interest daily, charge an origination fee, or use a different compounding convention. The calculator is a useful estimate, but compare it with the lender’s payment schedule and verify the actual agreement. For financial or tax decisions, check the real terms with the lender or a qualified professional; this example is not financial, tax, investment, or legal advice.

Build an amortization schedule

An amortization schedule shows how each payment is divided between interest and principal, and how the balance declines. Keep the input values in B1:B5 as above. In row 9, create headings: Period in A9, Starting balance in B9, Payment in C9, Interest in D9, Principal in E9, and Ending balance in F9. The following formulas create the first payment row, row 10.

With the loan amount in B1 and the first schedule row in A10:F10, enter 1 in A10 and use this formula in B10 to bring in the starting balance:

=$B$1

With the payment stored in B5 and the schedule payment column in C, enter this in C10:

=$B$5

With the starting balance in B10, annual rate in B2, and payments per year in B3, enter this in D10 to calculate first-period interest:

=B10*$B$2/$B$3

With the payment in C10 and interest in D10, enter this in E10 to calculate the amount that reduces principal:

=C10-D10

With starting balance in B10 and principal repaid in E10, enter this in F10 to calculate the ending balance:

=B10-E10

For row 11, enter =A10+1 in A11. With the prior row’s ending balance in F10, enter =F10 in B11. Copy C10:F10 down alongside these rows, and copy the row number formula down until there are 60 payment rows. Excel adjusts relative references for each new row while the dollar signs keep references to the assumptions fixed. The last balance should be close to zero, though a small difference can occur because displayed cents are rounded. Do not force the lender’s final payment to match this estimate without checking the loan’s actual schedule.

Change the timing or type of payment

PMT’s optional timing argument distinguishes a payment at the end of a period from one at the beginning. Leaving it out, or setting it to 0, means end-of-period payments. Setting it to 1 means beginning-of-period payments. For an ordinary installment loan, end-of-period is commonly the relevant assumption, but the contract controls. Beginning-of-period payments are more typical in some recurring payment arrangements, such as rent-style or lease-style examples.

The optional future-value argument is useful when the loan is not fully paid off by the final payment. A lease or balloon arrangement may leave a residual balance, but that figure and the payment timing must reflect the real terms. Do not use a zero future value automatically if the agreement requires a final balloon payment. PMT can model a fixed periodic payment under stated assumptions; it does not determine what those assumptions ought to be.

Common errors and limits to check

  • Mixing annual and monthly units: Dividing the annual rate by 12 while using a number of periods in years makes the payment wrong. Convert both rate and term to the payment frequency.
  • Entering a percentage incorrectly: A cell containing 6% has the decimal value 0.06. A plain entry of 6 means 600% unless formatted or entered differently.
  • Misreading a negative answer: Check the signs of the amount borrowed and payment before treating a negative result as a calculation failure.
  • Assuming the payment includes every cost: PMT does not automatically add property taxes, insurance, fees, or other charges. Include only costs that are actually part of the payment being modeled, and keep separate estimates clearly labeled.
  • Rounding each period: Rounding interest and principal in every schedule row can create a larger final balance difference. Format cells to show cents, but retain full precision in the formulas.

PMT assumes a fixed rate and equal payments at regular intervals. It is not appropriate by itself for a variable-rate loan, irregular payment dates, skipped payments, or a changing payment amount. A real schedule for those products may need period-by-period rates and dates, and should be checked against the lender’s calculation.

Excel alternatives and practical comparisons

For a quick answer, a PMT formula is simpler and easier to update than manually entering a payment equation. For a transparent breakdown, combine PMT with the amortization schedule above. Excel also has IPMT and PPMT functions for calculating the interest and principal portions for a specified period, but a row-by-row schedule makes the changing balance visible and is often easier to audit.

Excel’s web version and Mac version support the PMT function as well. The formula is the same, although ribbon placement or appearance can differ from Excel for Microsoft 365 on Windows. If Excel displays a formula as text, check that the cell is not formatted as Text and that the entry begins with an equals sign; change the format to General and re-enter the formula. If your regional settings use a different list separator, Excel may show a semicolon in formulas instead of a comma, depending on those settings.

Frequently Asked Questions

What is the Excel formula for a monthly loan payment?
Use PMT with the monthly interest rate, total number of monthly payments, and loan amount. For a fixed-rate loan with the amount in B1, annual rate in B2, payments per year in B3, and years in B4, use =PMT(B2/B3,B4*B3,-B1).

Why does Excel show my payment as a negative number?
PMT follows cash-flow signs. If the loan amount is entered as money received, the payment is money paid and therefore has the opposite sign. Use a negative principal in the PMT calculation, as in the example, to display the payment as a positive amount.

Does PMT include interest?
Yes. The payment calculation accounts for interest at the rate and interval supplied. It does not automatically include separate charges such as fees, taxes, or insurance.

How do I calculate the total interest on a loan in Excel?
Multiply the regular payment by the total number of payments, then subtract the original principal. In the example, the total is about $23,199.36 and the principal is $20,000, so estimated interest is about $3,199.36.

Can Excel calculate a mortgage payment?
PMT can estimate the fixed principal-and-interest payment when the rate, term, and payment frequency are fixed. It does not automatically include escrow items, lender fees, rate changes, or all mortgage-specific terms, so compare the result with the lender’s official estimate.

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.