How to Calculate a Monthly Loan Payment in Excel

How-to basics · Updated November 21, 2026

To calculate a monthly loan payment in Microsoft Excel, use the PMT function: with the loan amount in B1, annual interest rate in B2, and loan term in years in B3, enter =-PMT(B2/12,B3*12,B1) in B4. The minus sign displays the payment as a positive amount; the result estimates the regular principal-and-interest payment under the assumptions you enter.

What the PMT formula calculates

PMT calculates the fixed payment needed to repay a loan over a specified number of equal periods, assuming the interest rate stays constant and payments occur on a regular schedule. For a monthly loan, the periods are months. Excel needs three essential inputs: the interest rate for each payment period, the total number of payment periods, and the present value of the loan.

  • Rate: the interest rate for one month, not automatically the annual rate. If the quoted annual rate is nominal and payments are monthly, divide it by 12.
  • Nper: the total number of monthly payments. For a term in years, multiply the years by 12.
  • Pv: the amount borrowed, entered as a positive number in this example.

Excel follows a cash-flow sign convention: money received and money paid have opposite signs. With a positive loan amount as the present value, PMT returns a negative payment because it represents money leaving the borrower. Putting a minus sign before PMT changes the displayed result to positive without changing the calculation.

Set up the worksheet

A small input table makes the calculation easy to inspect and update. In a blank worksheet, enter labels in column A and the corresponding values in column B. The following layout uses a $25,000 loan, a 6% annual rate, and a five-year term:

  • A1: Loan amount; B1: 25000
  • A2: Annual interest rate; B2: 6%
  • A3: Term in years; B3: 5
  • A4: Monthly payment; put the formula in B4

With the loan amount in B1, the annual rate in B2, and the number of years in B3, enter =-PMT(B2/12,B3*12,B1) in B4. Excel returns approximately $483.32 per month. Format B1 and B4 as currency and B2 as a percentage so the inputs and result are easy to read. In Excel for Microsoft 365 on Windows, use Home > Number group to choose the appropriate number format. On Mac, the same number-format options are available on the Home tab, although the ribbon layout can differ slightly.

Enter the rate as 6% or 0.06, not as 6. If you type 6, Excel treats that as the number six; dividing it by 12 would produce a monthly rate of 0.5, or 50%, rather than 0.5%. The cell’s displayed format can be changed without altering its underlying value, so check both the value and the percentage format when a result looks unreasonable.

Check the worked example and total cost

For the example, B1 is 25000, B2 is 6%, and B3 is 5. The formula converts the annual rate to 0.5% per month and the term to 60 monthly payments. Under those assumptions, the monthly payment is about $483.32. The displayed amount may differ by a cent depending on how the result is rounded for presentation; the underlying calculation retains more precision.

A monthly payment alone does not show the full amount repaid. To calculate the sum of scheduled payments, use a separate cell for the result. With the payment in B4, the term in years in B3, and 12 monthly payments per year, enter =B4*B3*12. Using the displayed payment of $483.32 gives approximately $28,999.20 in payments over 60 months. The precise total based on the unrounded PMT result is slightly different, so avoid rounding the payment inside the formula if you need a more exact estimate.

To estimate interest paid over the full term, subtract the original loan amount from the sum of payments. With the original loan amount in B1 and the monthly payment in B4, enter =B4*B3*12-B1. Using the displayed monthly payment gives approximately $3,999.20 of interest. This is an estimate based on the rounded payment; the exact total will depend on the underlying unrounded amount and the lender’s payment schedule. Neither total includes fees or other charges unless you account for them separately.

Understand the assumptions behind the result

The calculation is useful because it turns three inputs into a comparable monthly estimate, but it is not a complete loan contract or payoff schedule. PMT assumes equal payments and a constant rate for the entire term. It also assumes that each payment is made at a regular interval and that the payment covers the amount due under the model.

For the common case where the first payment is made at the end of the first month, Excel uses the end-of-period assumption by default. Some arrangements call for payments at the beginning of each period. PMT has an optional fifth argument called type: 0 means payments are due at the end of each period, and 1 means they are due at the beginning. For example, with the loan amount in B1, monthly rate based on the annual rate in B2, and total months based on B3, enter =-PMT(B2/12,B3*12,B1,0,1) to model payments at the beginning of each month. The result will generally be lower than the end-of-month payment because the balance is reduced earlier.

Do not assume every lender calculates interest by simply dividing an advertised rate by 12. That approach is appropriate for a standard nominal annual rate with monthly periods, but a lender may use a different compounding convention, daily interest accrual, fees, or a variable rate. The amount shown on a real payment schedule can therefore differ from an Excel estimate even when the inputs appear similar.

Compare loan amounts, rates, and terms

Once the cells are linked, you can change the loan amount, rate, or term and Excel will recalculate the payment. This is useful for comparing scenarios consistently. A larger principal generally increases the payment; a higher interest rate generally increases it; and a longer term generally reduces the required monthly payment while increasing the number of payments.

For a fair comparison, keep the assumptions consistent and vary one input at a time. For example, compare two term lengths using the same amount and rate, then compare two rates using the same amount and term. You can type each scenario into a copy of the input table or replace one input and record the results. Remember that a smaller payment from a longer term does not, by itself, mean the loan costs less overall: it may mean paying interest for more months.

Excel’s What-If Analysis tools can help compare alternatives. In Microsoft 365 for Windows, Data > What-If Analysis > Data Table can calculate results for a range of input values when the worksheet is arranged for a one-variable or two-variable data table. For a quick manual comparison, however, changing the input cells one scenario at a time is often clearer and less prone to layout mistakes.

Include fees and distinguish payment from borrowing cost

The basic PMT result is a scheduled payment for the principal and interest represented by the inputs. It does not automatically include an origination fee, insurance, taxes, optional services, or other charges. Some loans collect certain costs separately, while other arrangements may add charges to the amount financed. If a charge is financed, the amount used as the principal in the worksheet may need to reflect the amount actually borrowed; if it is paid separately, adding it to the PMT result would be a separate estimate rather than part of PMT’s calculation.

Also distinguish the interest rate used in the formula from an annual percentage rate or other cost measure quoted by a lender. A quoted APR can reflect certain costs and calculation rules that are not equivalent to simply using that figure as a monthly interest rate in PMT. Check the lender’s disclosures and payment schedule to identify the rate, financed amount, payment timing, and any charges relevant to the real loan. For personal financial decisions, consult the lender or a qualified professional; this spreadsheet example is an illustration, not financial, tax, investment, or legal advice.

Common errors and ways to troubleshoot them

If the result is unexpectedly large, small, or negative, check the inputs before changing the formula. PMT’s negative result with a positive loan amount is normal; use the leading minus sign if you want to display a positive payment. Then confirm the rate is stored as a percentage or decimal, the term is in years before multiplying by 12, and the loan amount is in the intended currency and scale.

  • Annual rate entered as a monthly rate: If B2 contains 6%, use B2/12 for monthly payments. Passing B2 directly would treat 6% as the monthly rate.
  • Term not converted to months: A five-year monthly loan has 60 periods, not five. Multiply years by 12.
  • Wrong sign: With a positive present value, PMT returns a negative cash outflow. Use the leading minus sign for a positive display.
  • Unexpectedly high payment: Check whether a whole-number rate such as 6 was entered instead of 6%, and verify the loan amount and term cells.
  • Difference from a lender quote: Check whether the quoted amount includes fees, uses a different rate convention, has a different first-payment date, or includes non-loan charges.

For reliable results, keep the input values separate from the formula, label units clearly, and avoid typing a rounded payment back into the calculation. A worksheet can show two decimal places while retaining the full underlying result, which is preferable when calculating totals.

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. If the annual rate is in B2, the number of years is in B3, and the amount borrowed is in B1, enter =-PMT(B2/12,B3*12,B1). The minus sign makes the displayed payment positive.

Should I divide the interest rate by 12 in Excel?

For a standard nominal annual rate and monthly payments, yes: divide the annual rate by 12 to get the monthly rate used by PMT. This is not universal for every lender or rate convention, so check the loan terms if the lender calculates interest differently.

Why does PMT show a negative number?

Excel uses positive and negative signs to represent opposite cash flows. A positive loan amount is money received, while each repayment is money paid out, so PMT returns a negative payment. Add a minus sign before the function when you want the payment displayed as a positive amount.

Does PMT include taxes, insurance, or fees?

No. The standard calculation estimates the payment implied by the principal, rate, term, and payment timing you provide. Add separate charges only if you want a broader estimated monthly outlay, and check the lender’s documents to see which costs are included in the quoted payment.

How do I calculate a weekly or biweekly payment?

Use a rate and number of periods that match the payment frequency and the lender’s interest calculation. Do not automatically divide an annual rate by 12 or multiply years by 12 for a nonmonthly schedule. Confirm how the lender defines the periodic rate and payment dates before adapting PMT, because those details affect the result.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.