calculate-loan

01 Loan payments

Interest Amortization Schedule Excel: Build and Check It

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

An interest amortization schedule in Excel shows how each loan payment is divided between interest and principal, and how the balance changes over time. This calculator estimates a fixed-rate loan’s monthly payment and total interest from the loan amount, APR, and term; the steps below show how to build the matching schedule in Excel.

Formula

The fixed monthly payment is calculated using the standard level-payment loan formula, then rounded to cents:

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

  • P is the starting loan principal, in dollars.
  • r is the monthly interest rate as a decimal: APR ÷ 1200. For an 8% APR, r = 8 ÷ 1200.
  • n is the number of monthly payments: loan term in years × 12.
  • Payment is the monthly amount in dollars, rounded to the nearest cent.

The calculator’s total-interest estimate is Payment × n − P. It uses the rounded monthly payment for every scheduled month, so its total follows the calculator’s stated convention. A real loan’s final payment can differ because of lender rounding, payment dates, or other contract details.

Worked Example

Using the calculator defaults: principal P = $20,000, APR = 8%, and term = 5 years.

  1. Convert APR to a monthly decimal rate: r = 8 ÷ 1200 = 0.0066667.
  2. Convert the term to months: n = 5 × 12 = 60.
  3. Substitute into the payment formula: $20,000 × 0.0066667 ÷ (1 − (1.0066667)−60), then round to cents.
  4. The monthly payment is $405.53.
  5. Total scheduled payments using that rounded amount are $405.53 × 60 = $24,331.80; estimated total interest is $24,331.80 − $20,000 = $4,331.80.

Assumptions

  • The interest rate stays fixed throughout the loan.
  • Payments are made monthly, at the end of each monthly period, with the first payment one month after the starting balance is set.
  • Interest compounds monthly at APR divided by 12.
  • The estimate excludes fees, taxes, insurance, PMI, and escrow because they are not calculator inputs.

Build the Schedule in Excel

Excel can display the same calculation row by row. Put the inputs in separate cells, for example: B1 for principal (20000), B2 for annual APR (8%), and B3 for the term in years (5). Format B2 as a percentage. In B4, calculate the monthly payment with =ROUND(-PMT(B2/12,B3*12,B1),2). Excel’s PMT function returns a negative value when the amount borrowed is entered as a positive present value, so the leading minus sign makes the displayed payment positive.

Set up a table with columns for payment number, beginning balance, payment, interest, principal, and ending balance. In the first payment row, enter these formulas and copy them down for the full number of months:

  • Payment number: 1 in the first row, then increase it by 1 each row.
  • Beginning balance: the original principal for the first row; for later rows, link to the previous row’s ending balance.
  • Payment: the fixed payment cell, such as =$B$4.
  • Interest: beginning balance × monthly rate, such as =BeginningBalance*$B$2/12.
  • Principal paid: payment − interest.
  • Ending balance: beginning balance − principal paid.

Use cell references appropriate to your worksheet; the labels above are descriptive, not literal Excel names. Keep full precision in intermediate calculations and format dollar columns to two decimal places. If you round interest and principal in every row, small rounding differences can accumulate. A final payment may need adjustment to bring the balance to zero without paying more than the remaining amount owed.

Reading and Checking Each Row

At the start of the schedule, interest is calculated on the largest balance, so a larger share of the level payment usually goes to interest. As principal is repaid, the balance falls and the interest portion generally declines; the principal portion rises if the payment remains level. This is why a payment amount alone does not reveal the remaining balance or total interest.

For a quick check, the interest and principal columns should add to the payment on each ordinary row. The ending balance should equal the next row’s beginning balance, and the balance should approach zero after the final scheduled payment. Summing the interest column gives schedule-based total interest; minor differences from the calculator can arise if the spreadsheet rounds each period differently.

Common Excel Problems and Limits

  • APR entered as 8 instead of 8%: If the formula divides by 12, enter 8% as a percentage or use 8/100. A value of 8 interpreted as 800% changes the result dramatically.
  • Annual rate used as monthly rate: Divide an annual percentage rate by 12 for a monthly percentage rate, or by 1200 when converting a percent number such as 8 into a decimal monthly rate.
  • Wrong sign in PMT: Excel’s cash-flow convention makes borrowing and repayment signs opposite. Use a minus sign or enter the principal as a negative value, but avoid reversing both.
  • Extra payment omitted: Additional principal payments change later interest and balances. This calculator assumes only the fixed scheduled payment; an extra-payment schedule needs an added input and a rule for when the extra amount is applied.
  • Variable rates or irregular dates: The simple schedule assumes regular monthly periods and a fixed rate. It does not reproduce daily-interest calculations, changing rates, skipped payments, or lender-specific payment allocation.

For a fixed-rate loan with regular monthly payments, the schedule is useful for understanding principal reduction and estimated interest. It is not a substitute for the payment dates, rounding method, and payoff amount in the loan documents or a lender’s current account information.

Frequently Asked Questions

How do I calculate monthly interest in an Excel amortization schedule?
Multiply the beginning balance for that month by the monthly rate. If APR is stored as a percentage, a common formula is =BeginningBalance*APR/12; if it is stored as a whole number such as 8, use =BeginningBalance*APR/1200.

Does Excel have an amortization schedule template?
Excel versions and template availability can vary. You can create the schedule with the row formulas above, or use a spreadsheet template only after checking that its rate, payment timing, rounding, and extra-payment assumptions match the loan being estimated.

Why does my Excel balance not reach exactly zero?
Rounding each period, using a payment with too few decimal places, or entering a different payment count can leave a small residual. Keep full precision in formulas and, if needed, adjust the last payment to the remaining balance plus that period’s interest.

Is total interest the same as APR?
No. APR is an annualized rate expressed as a percentage; total interest is a dollar amount accumulated over the loan term. This estimate treats the input APR as the periodic-rate basis and does not separately model fees that may affect a disclosed APR.

Can this schedule show extra payments?
Not as currently configured. To model extra payments, add the extra amount to the principal reduction in the month it is made, cap the final payment at the amount due, and recalculate each later month’s interest from the updated balance.

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 →