Monthly Loan Payment Formula in Excel

Formulas & functions · Updated November 21, 2026

To calculate a fixed monthly loan payment in Excel, use PMT. With the loan principal in B1, the annual interest rate in B2, and the term in years in B3—for example, $20,000, 6%, and 5 years—enter =-PMT(B2/12,B3*12,B1). The result is about $386.66 per month, before any fees or other charges.

The Excel formula and its inputs

PMT calculates a regular payment for a loan or other financial arrangement with a constant interest rate and equal payments. Its arguments are the interest rate for one payment period, the total number of payment periods, and the present value: the amount borrowed. For a monthly loan payment, the rate and number of periods need to be monthly too.

  • Rate: Divide the annual rate by 12 when the loan is paid monthly and the quoted rate is a nominal annual rate. If B2 contains 6%, B2/12 is 0.5% per month.
  • Number of periods: Multiply a term in years by 12. A five-year loan has 60 monthly payments.
  • Present value: Use the amount borrowed, not the amount eventually repaid. In the example, that is $20,000.

The minus sign before PMT is intentional. Excel normally returns a negative payment when the amount borrowed is entered as a positive present value, because the payment is money flowing out. Putting a minus sign before the function displays the payment as a positive amount. You could instead enter the loan amount as a negative value, but using a positive loan amount and negating PMT is often easier to read.

For a loan whose term is already entered as a number of months, use this layout: B1 contains $20,000, B2 contains 6%, and B3 contains 60 months. Enter =-PMT(B2/12,B3,B1); it returns about $386.66. This version avoids converting years to months because the period count is already monthly.

Worked example: a $20,000 loan

Suppose a borrower takes out a $20,000 fixed-rate loan at a 6% nominal annual interest rate and repays it in equal monthly payments over five years. Set up the inputs like this:

  • B1: 20000, the principal
  • B2: 6%, the annual interest rate
  • B3: 5, the term in years

With those values in B1:B3, enter =-PMT(B2/12,B3*12,B1) in another cell. Excel calculates an unrounded payment of approximately $386.656, which displays as $386.66 when the cell is formatted as currency. There are 60 payments. Using the unrounded payment, the total is approximately $23,199.36, or about $3,199.36 above the amount borrowed. These are illustrative calculations; a lender’s actual payment and total cost can differ because of its terms, fees, and calculation method.

To enter a formula directly, select an empty cell, type the equals sign and formula, then press Enter. Excel for Microsoft 365 on Windows uses commas between function arguments in the formula shown. The result is a payment estimate for the assumptions in the cells, not a quote from a lender. Changing the principal, rate, or term updates the result automatically.

Build a useful loan calculator worksheet

A small input area makes the calculation easier to audit and reuse. Put labels such as Principal, Annual rate, and Term in years in A1:A3, then enter the values in B1:B3. Put Monthly payment in A4 and the PMT formula in B4. Format B1 and B4 as currency and B2 as a percentage. In Excel for Microsoft 365 on Windows, select the cells and use Home > Number to choose an appropriate number format. Formatting changes how values appear; it does not change the underlying calculation.

Consider adding a note beside the rate stating whether it is an annual rate and whether the payment interval is monthly. That helps prevent a common spreadsheet mistake: putting 6 in a rate cell that is meant to contain 6%. A value of 6 means 600% to Excel; enter 6% or 0.06 instead. Also check that a term entered as five years is multiplied by 12, while a term entered as 60 months is not.

If the cell shows a long decimal, apply currency formatting or set the number of displayed decimal places to two. Avoid rounding the PMT result inside the calculation unless the loan terms specifically require a rounded payment every month. Excel can retain additional precision while displaying cents, which may cause a small difference between the displayed payment multiplied by the number of payments and a total calculated from the full-precision value.

Payment timing and interest-rate assumptions

PMT assumes payments happen at regular intervals and that the rate stays constant. By default, it assumes each payment is made at the end of the period. That is the usual assumption for a standard monthly installment loan. The function also has optional future-value and payment-timing arguments; for an ordinary fully repaid loan, the future value is zero and the timing argument can be left out.

If a contract requires payments at the beginning of each month, use this layout: B1 is $20,000, B2 is 6%, and B3 is 5 years. Enter =-PMT(B2/12,B3*12,B1,0,1); the result is approximately $384.73 per payment. The final 1 tells Excel that payments are due at the beginning of each period. This timing changes the interest calculation, so do not use it merely to obtain a lower estimate; match the payment schedule in the loan agreement.

Dividing an annual rate by 12 is suitable when the rate is quoted as a nominal annual rate and interest is calculated monthly. Some lenders use daily accrual, different compounding conventions, or other specific methods. If a lender supplies a periodic monthly rate, use that rate directly rather than dividing it by 12 again. When the contract describes a different interest or payment convention, confirm the appropriate inputs with the lender.

See how each payment changes the balance

The PMT result is the combined payment, not the amount of principal repaid in every month. Early in a loan, a larger portion usually covers interest; as the balance falls, the interest portion typically shrinks and more of the same payment goes toward principal. A simple schedule can make this pattern visible and help check the inputs.

For this layout, place the inputs in B1:B4: principal in B1, annual rate in B2, term in years in B3, and monthly payment in B4. Put Opening balance, Payment, Interest, Principal repaid, and Ending balance in C8:G8. In the first schedule row, row 9, enter the opening balance in C9 with =$B$1; it returns $20,000. Enter the payment in D9 with =$B$4; it displays about $386.66. Enter the first month’s interest in E9 with =C9*$B$2/12; it returns $100.00. Enter principal repaid in F9 with =D9-E9; it displays about $286.66. Finally, enter the ending balance in G9 with =C9-F9; it displays about $19,713.34.

Those row-9 figures are rounded for display; the formulas use the unrounded payment stored in B4. For later rows, the opening balance should link to the previous row’s ending balance. With row 10 as the next month and the ending balance in G9, enter =G9 in C10; it returns the prior month’s ending balance, approximately $19,713.34. Copy the payment, interest, principal, and ending-balance calculations down for the remaining months, adjusting the row references as Excel fills them. The final balance should be close to zero, though display rounding can leave a few cents. A large leftover balance usually points to an incorrect term, rate, payment timing, or copied formula.

What the monthly payment does not include

PMT calculates a scheduled principal-and-interest payment from the values you provide. It does not automatically add an origination fee, mortgage insurance, property tax, homeowners insurance, service charges, or other costs. Some of those expenses may be collected alongside a loan payment, but they are not part of the basic PMT result unless you deliberately model them separately. A lender’s quoted amount due each month can therefore be higher than the spreadsheet’s payment.

The basic formula also assumes a fixed rate and level payments over the whole term. It does not model a variable rate that changes later, skipped or late payments, extra principal payments, a balloon payment, or a changing payment schedule. For an adjustable-rate loan, a single PMT result can describe one assumed rate scenario, but it cannot predict future payments without assumptions about future rate changes. Check the loan documents or ask the lender which charges and payment components are included in a quoted figure.

Use the worksheet as a way to understand how principal, rate, and term affect a payment—not as financial, tax, investment, or legal advice. Loan terms and lender calculations vary. Check real payment amounts and contract details with the lender, or consult a qualified professional when you need guidance about your circumstances.

Common errors and how to fix them

  • Using the annual rate as the monthly rate: For a monthly schedule and a nominal annual rate, divide by 12. Entering 6% as PMT’s period rate instead of 6%/12 produces a much larger payment.
  • Mixing years and months: If the input is five years, multiply by 12. If it is already 60 months, use 60 directly. Do not multiply the month count by 12 again.
  • Getting a negative result: This is Excel’s cash-flow sign convention. Negate PMT when the principal is positive, or enter the principal as negative and leave PMT unnegated.
  • Expecting the payment to include every cost: Check whether fees, insurance, taxes, and other charges are included in the lender’s quoted monthly amount. The basic formula covers only the loan calculation described by its inputs.
  • Rounding each step too early: Format the result to display cents, but keep full precision in the formulas unless the agreement specifies a rounded payment method. Early rounding can make the final balance or total differ slightly.

Frequently Asked Questions

What is the Excel formula for a monthly loan payment? Use PMT with a monthly rate, the number of monthly payments, and the loan amount. For a principal 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.

How do I calculate a loan payment when the term is in months? Keep the principal in B1 and annual rate in B2, and put the number of months in B3. Enter =-PMT(B2/12,B3,B1). For $20,000 at 6% over 60 months, the result is about $386.66.

Why does PMT return a negative number? Excel treats money received and money paid as opposite cash flows. If the loan amount is entered as a positive value, the scheduled payments are returned as negative values. Add a minus sign before PMT to display a positive payment amount.

Does PMT include taxes, insurance, or loan fees? No. The basic calculation uses the interest rate, number of periods, and present value. Add other costs separately if you want a broader estimate, and verify what a lender includes in its quoted payment.

Can PMT calculate payments for a variable-rate loan? It can calculate a payment for one rate assumption, but it does not forecast future rate changes. Recalculate scenarios at different rates for comparison, and check the loan documents or ask the lender how future payments are determined.

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.