calculate-loan

01 Loan payments

Excel Monthly Payment Formula and Worked Example

Calculator No. 01-1588 · Updated · Formula and assumptions shown below

Monthly payment $405.53
Total interest
$4,331.80
= Monthly payment × loan term × 12 − loan amount
Loan amount
$20,000
Input
Interest rate (APR)
8 %
Input
You enter
Loan amount ($), Interest rate (APR) (%), Loan term (years)
You get
Monthly payment ($), Total interest ($)
Rounding
2 dp

Estimate for education, not financial advice. The result follows from the numbers you enter and the formula shown. It is not a quote or an offer, and it does not include fees, taxes or insurance unless the calculator asks for them. Ask your lender for the exact figures.

Excel’s monthly payment formula is =PMT(rate, nper, pv). For a fixed-rate loan, enter the periodic interest rate, total number of payments, and loan principal; Excel returns the payment per period, usually as a negative cash flow unless you enter the loan amount as a negative value. The calculator below estimates a monthly payment and total interest from a loan amount, annual APR, and term in years.

Formula

For a loan with level monthly payments, the payment before rounding is:

PMT = P × r ÷ [1 − (1 + r)−n]

  • P is the principal, or amount borrowed, in dollars.
  • r is the monthly interest rate as a decimal. With an APR entered as a percentage, r = APR ÷ 1200.
  • n is the total number of monthly payments. When the term is in years, n = years × 12.

The calculator rounds the payment to cents, then calculates total interest as rounded monthly payment × n − P. Excel’s equivalent is =PMT(apr/12, years*12, -loan_amount) when APR is entered as a decimal such as 8% in a cell, or =PMT(apr/1200, years*12, -loan_amount) when APR is entered as the number 8. Use one convention consistently.

Worked Example

Using the calculator’s defaults—$20,000 principal, 8% annual APR, and a 5-year term—the inputs become:

  1. Monthly rate: 8 ÷ 1200 = 0.0066666667, or about 0.6667% per month.
  2. Number of payments: 5 × 12 = 60.
  3. Apply the payment formula: 20,000 × 0.0066666667 ÷ [1 − (1.0066666667)−60].
  4. Monthly payment, rounded to cents: $405.53.
  5. Total of the 60 rounded payments: $405.53 × 60 = $24,331.80.
  6. Estimated total interest: $24,331.80 − $20,000 = $4,331.80.

Excel may display a slightly different final total if it uses its full-precision payment in a separate calculation rather than the payment rounded to cents. The calculator deliberately uses the displayed, cent-rounded payment for its total.

Assumptions

  • The interest rate is fixed for the full term.
  • Payments are equal, made monthly, and treated as end-of-period payments, which is Excel PMT’s default.
  • Interest is compounded monthly using the APR divided by 12.
  • Fees, taxes, insurance, PMI, escrow, and extra payments are not included.

Entering the Formula in Excel

Suppose cell A1 contains the principal, A2 contains the annual APR as a percentage, and A3 contains the term in years. A formula that returns a positive payment is:

=-PMT(A2/12,A3*12,A1)

If A2 contains the number 8 rather than a percentage-formatted 8%, divide by 1200 instead: =-PMT(A2/1200,A3*12,A1). The minus sign reverses Excel’s cash-flow sign convention: receiving the loan and paying installments are opposite directions of cash flow. Alternatively, enter the principal as a negative number and omit the minus sign.

For monthly rate and term inputs, use =-PMT(monthly_rate, number_of_months, principal). If the loan begins with a payment due immediately rather than after one period, Excel’s optional type argument can be 1: =-PMT(rate,nper,pv,0,1). The usual end-of-month assumption corresponds to type 0, which can be omitted.

What the Payment Includes—and Excludes

PMT calculates a level payment that covers interest and repays principal over the chosen number of periods, assuming the stated rate and schedule remain unchanged. It does not by itself calculate the full amount due on a bill. For a mortgage, for example, property-related costs or insurance may be billed separately or combined with a payment; they are not part of this formula unless separately entered and added.

Total interest is also an estimate tied to the assumed schedule. A real loan can have fees, irregular payment dates, a different rate convention, or payments that vary. Get the applicable figures and payment schedule from the lender’s disclosure rather than treating a spreadsheet estimate as a quoted amount.

Common Excel Mistakes and Limits

  • Mixing percentage formats: If a cell contains 8%, Excel stores it as 0.08, so divide by 12. If it contains 8, divide by 1200. Dividing 8% by 1200 makes the rate far too small.
  • Using years as the payment count: For monthly payments, multiply years by 12. A 5-year term has 60 monthly periods, not 5.
  • Misreading a negative result: A negative PMT is Excel’s cash-flow sign convention, not a negative dollar obligation. Reverse the principal’s sign or put a minus sign before PMT for a positive display.
  • Rounding each period differently: Spreadsheet calculations may retain fractions of a cent internally, while actual installments are generally stated in cents. The last payment may need adjustment in a real schedule.
  • Assuming every loan is fixed-rate: A changing rate, balloon payment, skipped payment, or extra principal payment changes the repayment pattern, so a single fixed-payment PMT calculation will not fully model it.

PMT is a standard level-payment calculation, not a complete amortization schedule. To see how much of each installment goes to interest or principal, calculate each period’s interest from the opening balance, subtract it from the payment to find principal repaid, then reduce the balance. The result is an estimate, and the lender’s disclosure gives the exact figures.

Frequently Asked Questions

What is the Excel formula for a monthly loan payment?
Use =-PMT(annual_rate/12, years*12, loan_amount) when the annual rate is stored as a percentage such as 8%. The leading minus sign makes the returned payment positive.

Why does Excel PMT return a negative number?
PMT treats money received and money paid as opposite cash flows. A positive loan principal represents money received, so the payment comes back negative. Reverse the principal’s sign or negate the PMT result.

Does PMT include taxes or insurance?
No. PMT covers principal and interest under its stated assumptions. Add other costs separately if you need an estimate of a broader monthly bill.

How do I calculate total interest in Excel?
For a fixed payment schedule, multiply the payment by the number of periods and subtract the principal. If you use a rounded payment, use that same rounded value for the total to match the displayed installments.

What if the interest rate is zero?
At 0% interest, divide the principal by the number of payments: for example, =principal/(years*12). The calculator requires an APR of at least 0.01% because its standard formula divides by the periodic rate.

Related

Formula used
in p | Loan amount | $ | 20000 | 1 | 100000000
in apr | Interest rate (APR) | % | 8 | 0.01 | 100
in yrs | Loan term | years | 5 | 0.0833 | 50
out pmt | Monthly payment | $ | round(p * (apr / 1200) / (1 - (1 + apr / 1200) ^ -(yrs * 12)), 2) | 2
out int | Total interest | $ | pmt * yrs * 12 - p | 2

Each line is one input (in, sel, list) or one result (out: label, unit, formula, decimals). How the calculators work

Method: the standard loan formula for this calculation, fixed rate and monthly compounding unless stated, rounded as each result states. Not financial advice; no lender, rate or product is recommended. See formulas and assumptions, the disclaimer or report an error.

More in this section

The whole Loan payments section →