calculate-loan

01 Loan payments

Interest Amortization Calculator Excel: Formula and Example

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

Monthly payment $405.53
Total interest
$4,331.67
= 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.

An interest amortization calculator in Excel can show a loan’s fixed monthly payment, the interest and principal in each payment, and the remaining balance. Enter the loan amount, annual interest rate, and term to estimate the payment and total interest; the schedule below explains how to build the calculation and what it leaves out.

Formula

The standard fixed-payment amortization formula calculates a level monthly payment from the principal, monthly interest rate, and number of monthly payments:

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

  • P is the amount borrowed, in dollars.
  • r is the monthly rate as a decimal: APR ÷ 12 ÷ 100. For an 8% annual rate, r = 0.08 ÷ 12.
  • n is the number of monthly payments: loan term in years × 12.

Total interest in this estimate is the rounded monthly payment multiplied by the number of payments, minus the original principal: Total interest = payment × n − P. The calculator rounds the payment to cents before using it for this total. An actual lender may calculate the final payment or interest differently because of rounding, payment dates, fees, or other contract details.

Worked Example

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

  1. Convert the annual rate to a monthly decimal: 8 ÷ 1,200 = 0.0066667.
  2. Find the number of payments: 5 × 12 = 60.
  3. Substitute into the payment formula: 20,000 × 0.0066667 ÷ [1 − (1.0066667)−60] ≈ $405.53 per month.
  4. Calculate estimated total interest using the rounded payment: $405.53 × 60 − $20,000 = $4,331.80.

These are estimates based on the displayed formula and defaults. The monthly payment is rounded to cents; a loan’s final scheduled payment can differ by a small amount.

Assumptions

  • The interest rate remains fixed for the full term.
  • Payments are monthly, level in amount, and made at the end of each monthly period.
  • Interest is compounded monthly using APR ÷ 12.
  • Fees, taxes, insurance, PMI, and escrow are not included.

Build an Amortization Schedule in Excel

Excel’s PMT function can calculate a fixed periodic payment. For monthly payments, use a formula such as =-PMT(annual_rate/12, years*12, principal), replacing the names with cell references or defined names. For example, if the annual rate is in B2, years in B3, and principal in B1, enter =-PMT(B2/12,B3*12,B1) when B2 contains 8% as a percentage, not the number 8. The negative sign makes the payment display as a positive cash amount under Excel’s cash-flow sign convention.

To make the schedule, create columns for payment number, starting balance, payment, interest, principal repaid, and ending balance. Put the number of the first payment in the first row. Use these relationships for each period:

  • Interest = starting balance × monthly rate.
  • Principal repaid = payment − interest.
  • Ending balance = starting balance − principal repaid.
  • The next row’s starting balance equals the previous row’s ending balance.

Fill the formulas down for the full number of payments. Excel’s IPMT and PPMT functions can also return the interest and principal portions for a specified period; each uses the period number, periodic rate, total periods, and present value. Their cash-flow signs may be negative when the loan amount is entered as a positive value, so check signs before interpreting results. Avoid rounding each intermediate formula to whole dollars: display cents with cell formatting, while retaining precision in the underlying calculations.

How to Read the Schedule and When to Use It

At the beginning of a typical fully amortizing loan, the outstanding balance is larger, so more of each fixed payment goes to interest. As the balance falls, the interest portion generally falls and the principal portion rises. The payment itself stays level in the simplified fixed-rate model. The schedule is useful for understanding this pattern, estimating cumulative interest, or seeing a projected balance after a given number of payments.

For example, the first month’s estimated interest on $20,000 at 8% APR is about $133.33: $20,000 × (0.08 ÷ 12). With a $405.53 payment, about $272.20 goes toward principal, leaving an estimated $19,727.80 balance. This illustrates why multiplying the opening balance by the annual rate does not give one month’s interest: the rate must first be converted to a monthly rate.

Common Excel Errors and Limits

  • Entering 8 instead of 8%. Excel interprets 8 as 800% if the formula divides it by 12. Either enter 8% and use B2/12, or enter 8 and use B2/1200.
  • Mixing years and months. PMT needs the same period for rate and term. With a monthly rate, use the number of monthly payments, not the number of years alone.
  • Ignoring cash-flow signs. Excel’s financial functions distinguish money paid out from money received. A negative PMT result does not necessarily mean the arithmetic is wrong; it may reflect the sign convention.
  • Rounding every row. Rounding interest and balances repeatedly can create a small difference in the final balance. Keep full precision in formulas and format cells to show cents.
  • Assuming every loan is level-payment. Variable rates, interest-only periods, irregular payment dates, extra payments, fees, and a different compounding convention require a more detailed model.

This calculation is an estimate, not a loan offer or a complete account ledger. Use the rate and payment terms supplied by the lender; the lender’s disclosure gives the exact figures.

Frequently Asked Questions

What is an interest amortization calculator?
It estimates a fixed loan payment and can separate each payment into interest and principal, while tracking the projected balance over time.

How do I calculate a monthly loan payment in Excel?
Use PMT with the monthly rate, total number of monthly payments, and principal. For an annual rate entered as a percentage, a common form is =-PMT(rate/12, years*12, principal).

Does Excel have an amortization schedule template?
Excel may provide templates depending on the version and available template catalog. You can also create a schedule with formulas for each period’s interest, principal, and ending balance; verify any template’s assumptions against the loan terms.

Why does the Excel payment differ from my lender’s amount?
Possible reasons include rounding, payment timing, fees included in the financed amount, a different rate convention, or loan features that the basic fixed-payment formula does not model. Compare the inputs and assumptions with the lender’s disclosure.

Does a larger payment always reduce total interest?
In a standard amortizing loan, paying principal earlier generally reduces the balance on which later interest is calculated. This calculator does not model extra payments or contract-specific rules, so use a schedule that includes those details to estimate their effect.

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 | $ | p * (apr / 1200) / (1 - (1 + apr / 1200) ^ -(yrs * 12)) | 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 →