calculate-loan

01 Loan payments

Loan Payment Equation in Excel: Formula and Example

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

The loan payment equation in Excel is =-PMT(APR/12, years*12, loan_amount), with the APR entered as a decimal; for example, enter 8% as 8%. It calculates a fixed monthly principal-and-interest payment from the amount borrowed, annual interest rate, and term. This calculator shows the same standard calculation and the total interest implied by its rounded monthly payment.

Formula

For a fixed-rate loan with equal monthly payments, the payment equation is:

Payment = P × r / (1 − (1 + r)−n)

  • P is the amount borrowed, in dollars.
  • r is the monthly interest rate, calculated as annual APR divided by 1,200. For an APR input of 8, r = 8 / 1200.
  • n is the number of monthly payments, calculated as years × 12.

The displayed payment is rounded to the nearest cent. Total interest here is calculated as the rounded monthly payment multiplied by the number of payments, minus the original loan amount. It is therefore the interest implied by making that same rounded payment for every scheduled month.

Worked Example

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

  1. Convert the APR to a monthly rate: r = 8 / 1200 = 0.0066666667.
  2. Calculate the number of payments: n = 5 × 12 = 60.
  3. Apply the payment formula: 20000 × 0.0066666667 / (1 − (1 + 0.0066666667)−60).
  4. Round the result to cents: the monthly payment is $405.53.
  5. Calculate the total of the rounded payments: $405.53 × 60 = $24,331.80.
  6. Subtract the amount borrowed: $24,331.80 − $20,000 = $4,331.80 in total interest.

Assumptions

  • The interest rate is fixed for the full term.
  • Payments are equal, made monthly, and paid at the end of each monthly period.
  • Interest compounds monthly at APR divided by 12.
  • The calculation excludes fees, taxes, insurance, PMI, and escrow.

Using the Payment Equation in Excel

Excel’s PMT function uses the form PMT(rate, nper, pv, [fv], [type]). The rate is the interest rate for each payment period, nper is the total number of periods, and pv is the present value—in this case, the principal borrowed. For monthly payments on an annual APR, divide the APR by 12 and multiply the number of years by 12.

If cells contain the annual APR, term in years, and loan amount, a formula might be =-PMT(B2/12,B3*12,B1) when B1 holds the amount, B2 the APR, and B3 the years. Enter the APR as a percentage, such as 8%, rather than the whole number 8. The minus sign makes the payment display as a positive amount because Excel treats money paid out and money received as having opposite signs. Another option is to enter the loan amount as a negative number and omit that minus sign.

To calculate the total of the scheduled payments in Excel, multiply the payment by the number of periods. To estimate total interest using the rounded payment, use =ROUND(-PMT(B2/12,B3*12,B1),2)*B3*12-B1. Small differences can arise if one spreadsheet calculation retains extra precision while another rounds each payment to cents.

How to Read the Results

The monthly payment is a level principal-and-interest amount under the stated assumptions, not necessarily the entire amount due on a bill. In an amortizing loan, each payment covers interest accrued for the period and reduces principal by the remainder. Early payments generally contain a larger interest portion than later payments because interest is calculated on the outstanding balance.

Total interest is the amount paid above the original principal under the calculator’s simplified schedule. It is not the total cost of every possible charge associated with borrowing. If the actual payment schedule includes a final adjustment, fees, irregular payment dates, or a different rounding method, the lender’s figures may differ.

Common Excel Errors and Formula Limits

  • Using the APR as a monthly rate: For an annual APR quoted as a percentage, use APR/12 in Excel. In the calculator, which accepts APR as a number of percentage points, the equivalent monthly decimal rate is APR/1200.
  • Mixing years and months: If the term is entered in years, use years × 12 for monthly payments. If it is already in months, do not multiply by 12 again.
  • Confusing percentage formats: A cell formatted as 8% stores 0.08. A cell containing the number 8 is not equivalent unless the formula divides it by 100 as well as by 12.
  • Expecting the payment to include every charge: The equation covers principal and interest only. A bill can include other items that are outside this calculation.
  • Applying it to changing rates or uneven payments: The standard equation assumes a constant rate and regular, equal monthly payments. Variable rates, skipped payments, extra principal, or fees require a more detailed schedule.

The APR and term are inputs, not forecasts or quotes. Use the figures supplied for the loan being evaluated; this calculator does not determine a rate, fees, eligibility, or approval.

Frequently Asked Questions

What is the Excel formula for a monthly loan payment?
Use =-PMT(APR/12, years*12, loan_amount) when APR is stored as a percentage such as 8%. The minus sign displays the payment as a positive number.

Why does Excel show a negative payment?
Excel’s financial functions use cash-flow signs: money received and money paid are opposite directions. Negate the PMT result, or enter the principal as a negative value, to display the payment with the sign you prefer.

Does the payment include taxes and insurance?
No. The standard PMT calculation covers principal and interest. Taxes, insurance, PMI, escrow, and fees are not included unless separately added to a calculation.

Why is my Excel result slightly different from a lender’s payment?
Possible reasons include payment rounding, fees or other amounts included in the loan, different timing conventions, or a rate or term that differs from the values entered. Compare the assumptions and figures in the lender’s disclosure.

Can I use this equation for a variable-rate loan?
It can estimate a payment for a particular rate and term, but it does not model future rate changes. Recalculate with updated terms when the rate or payment schedule changes.

This result is an estimate; 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 →