calculate-loan

01 Loan payments

Loan Payment Calculator Excel: Formula and Example

Calculator No. 01-5043 · 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.

A loan payment calculator in Excel can estimate a fixed monthly payment from the amount borrowed, annual APR, and loan term. Enter those three figures below to calculate the monthly payment and total interest; the estimate assumes equal monthly payments and a fixed rate.

Formula

The standard fixed-payment formula converts the annual percentage rate to a monthly rate and spreads repayment over the total number of monthly payments:

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

  • P is the loan principal, in dollars.
  • r is the monthly interest rate as a decimal: APR ÷ 12 ÷ 100.
  • n is the number of monthly payments: term in years × 12.

The calculator rounds the monthly payment to the nearest cent. Total interest is then calculated as the rounded payment multiplied by the number of payments, minus the original principal. This makes the displayed total consistent with the displayed payment. A lender’s final payment may differ slightly because real accounts can use specific dates, day-count conventions, or payment adjustments.

Worked Example

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

  1. Monthly rate: 8 ÷ 12 ÷ 100 = 0.0066667.
  2. Number of payments: 5 × 12 = 60.
  3. Substitute into the formula: $20,000 × 0.0066667 ÷ [1 − (1.0066667)−60].
  4. The unrounded payment is about $405.53; rounded to cents, the monthly payment is $405.53.
  5. Total interest using that rounded payment: $405.53 × 60 − $20,000 = $4,331.80.

Assumptions

  • The interest rate stays fixed throughout the term.
  • Payments are equal, made monthly, and treated as occurring at the end of each monthly period.
  • Interest compounds monthly using APR divided by 12.
  • Fees, taxes, insurance, PMI, escrow, and other charges are not included.

How to Use the Result in Excel

Excel offers a built-in payment function, PMT, which can calculate the same kind of level payment. With a monthly rate in cell B2, number of payments in B3, and loan amount in B1, a formula is =-PMT(B2,B3,B1). The minus sign displays the payment as a positive number because spreadsheet cash-flow functions treat money paid out and money received as opposite signs.

For the example above, you could place 8%/12 in B2, 5*12 in B3, and 20000 in B1. The result is approximately $405.53. Format the result as currency and use two decimal places. The calculator formula shown here makes the rate conversion and payment calculation explicit; Excel’s PMT function is a convenient alternative, not a different loan structure.

Keep the APR as a percentage input such as 8%, or enter 8 and divide by 100 in the formula. Do not enter 8 as a decimal rate: that would mean 800% per year. Likewise, use the monthly rate in the payment formula, not the annual rate directly. The actual APR and any lender-specific fees should be obtained from the lender rather than assumed from this example.

When This Calculator Is Useful—and What It Omits

This calculation is useful for understanding the scheduled principal-and-interest payment on a fixed-rate, fully amortizing loan. It can also show how changing the loan amount, term, or APR changes that mathematical payment. It does not determine whether a loan is available, whether an application will be approved, or what payment fits a person’s finances.

It is not a complete estimate of every bill associated with borrowing. A mortgage payment may involve taxes, insurance, or escrow; an auto loan can include fees or add-ons; and a loan with a variable rate can have payments that change. Those items require separate inputs or lender disclosures. This page does not model them.

A useful distinction: APR and the interest rate are related but are not always identical concepts in loan disclosures. This calculator treats the entered APR as a nominal annual rate divided evenly across 12 months. If a disclosed APR includes fees or uses different calculation conventions, this simplified formula may not reproduce the lender’s schedule exactly.

Common Excel Mistakes and Limits

  • Mixing annual and monthly values: Convert APR to a monthly decimal and years to months before calculating.
  • Reversing cash-flow signs: PMT may display a negative payment unless the result is negated or the principal is entered with the opposite sign.
  • Rounding too early: Keep full precision during intermediate calculations and round the payment only to cents. A lender’s final schedule may adjust the last payment.
  • Using PMT for a different loan type: Equal-payment calculations do not represent interest-only periods, balloon payments, irregular payment dates, or changing rates without additional modelling.

The formula also assumes a positive APR, a positive principal, and a term expressed in whole monthly payment periods. A term entered as a fraction of a year is converted into months; unusual partial-month schedules may not match a lender’s method.

Frequently Asked Questions

What is the Excel formula for a loan payment?
Use =-PMT(annual_rate/12, years*12, loan_amount) when the annual rate is entered as a percentage and the loan amount is positive. The minus sign makes the payment display as a positive amount.

Does Excel PMT include interest?
Yes. PMT calculates a level payment that covers principal and interest under the entered rate and number of periods. It does not automatically add taxes, insurance, lender fees, or other costs.

Why does my Excel payment differ from the lender’s amount?
Check that APR, principal, term, and payment frequency match. Differences can also result from fees, payment dates, rate conventions, rounding, or charges excluded from the basic calculation.

How do I calculate total interest in Excel?
For this fixed-payment estimate, multiply the monthly payment rounded to cents by the number of monthly payments, then subtract the principal. A lender’s exact total can differ if the schedule or final payment is adjusted.

Can this formula calculate a variable-rate loan?
Not as a single fixed schedule. The formula assumes the rate remains unchanged. A variable-rate estimate needs the rate changes and their timing modelled across the relevant payment periods.

This result is an estimate, and the lender’s disclosure gives the exact figures.

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 →