calculate-loan

01 Loan payments

Loan Payment Excel Formula and Calculator

Calculator No. 01-6470 · 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 Excel formula calculates the regular payment needed to repay a fixed-rate loan over a set term; this calculator uses the same standard amortization formula. Enter the loan amount, annual percentage rate (APR), and term in years to see the monthly payment and total interest, excluding fees and other charges.

Formula

For equal monthly payments on a fixed-rate loan, the payment is the principal multiplied by the monthly interest rate, divided by one minus the discount factor for all payments:

PMT = round(P × r / (1 − (1 + r)−n), 2)

  • P is the starting loan principal, in dollars.
  • APR is the annual percentage rate entered as a percentage, such as 8 for 8%.
  • r is the monthly rate as a decimal: APR / 1200.
  • n is the number of monthly payments: years × 12.
  • PMT is the monthly payment, rounded to the nearest cent.

Total interest in this calculator is PMT × n − P. It uses the rounded monthly payment for every scheduled installment, so the computed total reflects that cent-level payment assumption. A real final payment may differ slightly because lenders can calculate interest and adjust the last installment according to their contract.

Worked Example

Using the calculator’s defaults: a $20,000 loan, 8% APR, and a five-year term.

  1. Convert the annual rate to a monthly decimal: r = 8 / 1200 = 0.0066667.
  2. Find the number of payments: n = 5 × 12 = 60.
  3. Substitute into the formula: PMT = round(20000 × 0.0066667 / (1 − (1.0066667)−60), 2).
  4. The monthly payment is $405.53.
  5. Total of scheduled payments: $405.53 × 60 = $24,331.80.
  6. Estimated total interest: $24,331.80 − $20,000 = $4,331.80.

Assumptions

  • The interest rate remains fixed throughout the term.
  • Payments are equal, made monthly, and occur at the end of each monthly period.
  • Interest is modelled monthly at APR divided by 12.
  • The calculation excludes fees, taxes, insurance, PMI, escrow, and any lender-specific charges or payment adjustments.

Using the Formula in Excel

Excel’s PMT function can calculate a periodic loan payment. If A1 contains the annual APR as a percentage, A2 the number of years, and A3 the principal, a formula is:

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

For example, if A1 is formatted as 8% (the stored value is 0.08), the equivalent rate argument is A1/12. Excel normally returns a negative number for a payment when the loan amount is entered as a positive cash flow, so the leading minus sign displays the payment as positive. Alternatively, enter the principal as a negative value and omit that sign. Do not divide a cell already entered as 8% by 1200: percentage formatting and decimal input conventions matter.

The calculator above instead accepts APR as the number 8 for 8%, then converts it internally using APR/1200. Its inputs follow that convention. The formula and spreadsheet function agree when rate, period count, and cash-flow signs are entered consistently.

How to Read and Use the Result

The monthly payment is the amount allocated to each scheduled installment in the simplified model. Early payments generally include a larger interest share because interest is calculated on the outstanding balance; as principal falls, the interest portion typically falls too. The payment stays level under the fixed-rate assumptions, but its split between interest and principal changes over time.

Total interest is the estimated interest over all scheduled payments, not the total amount repaid. Add the principal to interest to get the modeled total paid. For comparing different loan terms or amounts, keep the inputs and assumptions clear: a longer term means more payment periods, and therefore can produce a different payment and total interest even at the same APR.

Use the lender’s quoted principal, APR, and term rather than an advertised or guessed rate. If the rate is variable, the payment can change; this fixed-rate formula cannot forecast those changes. If a loan has an irregular first period, nonmonthly payments, a balloon payment, or a different payment schedule, a standard monthly calculation may not match its contract.

Common Excel Errors and Formula Limits

  • Wrong rate units: An APR of 8% is 0.08 as a decimal. In Excel, use 0.08/12 or a cell containing 8% divided by 12. In this calculator, enter 8 and the conversion is automatic.
  • Wrong number of periods: A five-year monthly loan has 60 payments, not five. Excel needs years multiplied by 12 for its monthly period argument.
  • Sign confusion: Excel uses opposite signs for money received and paid. A negative PMT result usually reflects cash-flow sign convention, not a calculation failure.
  • Rounding each step: Keep full precision while calculating, then round the displayed payment to cents. Repeatedly rounding the monthly rate or intermediate values can create small differences.
  • APR is not every borrowing cost: The result does not automatically include charges or costs excluded from the inputs. Check the lender’s loan disclosure for the actual payment schedule and included amounts.

This formula is for level-payment amortizing loans with a positive periodic rate. It does not calculate a variable-rate schedule, determine eligibility, or account for fees unless those amounts are separately modelled. 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(APR/12, years*12, principal) when APR is stored as a decimal such as 0.08 and principal is positive. The minus sign displays the payment as positive.

Should I enter 8 or 8% for an 8% APR?
It depends on the formula. This calculator expects 8 and divides by 1200. Excel’s PMT function expects a decimal rate, so use 8% or 0.08 and divide by 12 for monthly periods.

Does the monthly payment include interest?
Yes. The calculated installment combines interest and principal repayment under the stated assumptions. It does not automatically include fees, taxes, insurance, or escrow.

Why is my Excel payment negative?
Excel treats money received and money paid as opposite cash-flow directions. If the principal is positive, PMT commonly returns a negative payment; put a minus sign before PMT to display it as positive.

Why does my lender’s payment differ from this result?
The lender may use different payment timing, rounding, fees, day-count conventions, or loan terms. Compare the exact principal, APR, number of payments, and disclosed schedule; this calculator models regular monthly payments at a fixed 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 →