calculate-loan

01 Loan payments

Interest Schedule Excel: How to Build One

Calculator No. 01-27083 · 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 schedule in Excel is a table that shows how each loan payment splits into interest and principal, and how the balance shrinks over time. You enter the loan amount, annual interest rate, and term, and the schedule lists every payment period with the interest charged, principal paid, and remaining balance. This article explains how to build one from scratch, what formulas to use, and how to avoid common mistakes.

Formula

The foundation of any interest schedule is the fixed monthly payment formula for a fully amortizing loan:

PMT = P × (r × (1 + r)^n) / ((1 + r)^n − 1)

Where:

  • P = principal loan amount (in $)
  • r = monthly interest rate = annual rate (APR) ÷ 12 ÷ 100 (as a decimal)
  • n = total number of payments = term in years × 12
  • PMT = the level monthly payment (in $)

For each period k (starting at 1), the interest for that period is:

Interest_k = Balance_{k−1} × r

The principal paid is PMT − Interest_k, and the new balance is Balance_{k−1} − principal paid. The schedule simply repeats this for every period until the balance reaches zero.

Worked Example

Using the calculator defaults: loan amount $20,000, APR 8%, term 5 years.

  1. Convert APR to monthly rate: r = 8 / 1200 = 0.0066667
  2. Number of payments: n = 5 × 12 = 60
  3. Compute payment: PMT = 20000 × (0.0066667 × (1.0066667)^60) / ((1.0066667)^60 − 1)
  4. (1.0066667)^60 ≈ 1.48985
  5. Numerator: 20000 × (0.0066667 × 1.48985) = 20000 × 0.0099323 = 198.646
  6. Denominator: 1.48985 − 1 = 0.48985
  7. PMT = 198.646 / 0.48985 ≈ 405.53
  8. Total paid = 405.53 × 60 = 24,331.80
  9. Total interest = 24,331.80 − 20,000 = 4,331.80

The calculator shows a monthly payment of $405.53 and total interest of $4,331.80, matching the manual calculation.

Assumptions

  • Fixed interest rate for the entire term.
  • Payments are made at the end of each month (ordinary annuity).
  • Interest compounds monthly at APR ÷ 12.
  • The payment is rounded to the cent; the final payment may adjust slightly to clear the balance.
  • No fees, taxes, insurance, or other charges are included.

How to Build an Interest Schedule in Excel

You can create a full amortization schedule with a few columns and formulas. Here’s a step-by-step method:

  1. Set up inputs: In cells A1, A2, A3, enter the loan amount, annual interest rate (as a percentage), and term in years.
  2. Calculate monthly rate and payment: In a cell, compute r = A2/1200 and n = A3*12. Use Excel’s PMT function: =PMT(r, n, -A1) (the negative sign gives a positive payment).
  3. Create the schedule columns: Period (1 to n), Beginning Balance, Payment, Interest, Principal, Ending Balance.
  4. Fill the first row: Period 1, Beginning Balance = loan amount. Interest = Beginning Balance × r. Principal = Payment − Interest. Ending Balance = Beginning Balance − Principal.
  5. Drag down: For each next row, Beginning Balance = previous Ending Balance, and repeat the formulas. Continue until period n.
  6. Check the final balance: It should be $0.00 (or a few cents due to rounding).

Excel also has built-in functions: IPMT (interest portion) and PPMT (principal portion) for each period, which can simplify the schedule. For example, =IPMT(r, period, n, -loan) gives the interest for that period.

How to Choose the Right Schedule Format

There are several variations of interest schedules, and the right one depends on your goal:

  • Standard amortization: Fixed payment, decreasing interest, increasing principal. Best for comparing loans or tracking a typical mortgage or auto loan.
  • Interest-only schedule: Payments cover only interest for a set period, then switch to fully amortizing. Useful for understanding temporary cash-flow relief.
  • Balloon payment schedule: Regular payments are lower, but a large final payment is due. Helpful for loans with a lump-sum payoff at maturity.
  • Extra payment schedule: Shows how additional principal payments shorten the term and reduce total interest. Good for planning prepayment strategies.

For most personal loans, a standard amortization schedule is the default. If you plan to make extra payments, a schedule with a column for “additional payment” is more flexible.

Common Mistakes and How to Avoid Them

  • Using the annual rate as the monthly rate: Always divide by 12, and remember to convert the percentage to a decimal (e.g., 8% → 0.08).
  • Forgetting to round the payment: If you use an unrounded payment in the schedule, the final balance may not be zero. In practice, lenders round to the cent, so your schedule should too.
  • Ignoring payment timing: The standard formula assumes payments at month-end. If payments are due at the beginning of the period, the interest calculation changes slightly.
  • Rounding intermediate balances: Keep full precision in your formulas; only round the final display. Otherwise, small errors accumulate over hundreds of periods.
  • Not checking the final payment: Due to rounding, the last payment often differs by a few cents. Adjust the final payment to exactly clear the balance.

Comparing Loans with an Interest Schedule

An interest schedule lets you compare loans side by side. For the same loan amount and term, a lower APR always results in a lower payment and less total interest. But when terms differ, the comparison is less obvious. For example, a 5-year loan at 8% has a higher monthly payment but much less total interest than a 10-year loan at 8%. The schedule shows the cumulative interest paid each year, which helps you see the long-term cost.

You can also use the schedule to see the effect of extra payments. If you add $50 to the principal each month, the balance declines faster, and the loan is paid off earlier. The schedule will show the new payoff date and the total interest saved. Many online calculators do this, but building it in Excel gives you full control over the assumptions.

Limitations of a Basic Interest Schedule

A standard schedule assumes a fixed rate and constant payments. Real-world loans may have variable rates, fees, or payment holidays. It also ignores the time value of money beyond the nominal interest—if you invest the money you would have used for extra payments, the comparison changes. For adjustable-rate mortgages, the schedule would need to be rebuilt each time the rate changes. For loans with fees, the APR (which includes fees) is a better comparison tool than the nominal rate used in the schedule.

Additionally, the schedule assumes you make every payment on time. Late fees and grace periods are not modeled. For most planning purposes, the schedule is a precise estimate, but the lender’s amortization statement is the authoritative record.

Frequently Asked Questions

How do I create an amortization schedule in Excel?

Enter the loan amount, APR, and term in separate cells. Use the PMT function to get the monthly payment, then create columns for period, beginning balance, interest (balance × monthly rate), principal (payment − interest), and ending balance. Fill down for all periods, and adjust the last payment to zero out the balance.

What is the difference between an interest schedule and an amortization schedule?

They are essentially the same. An interest schedule focuses on the interest portion of each payment, while an amortization schedule shows both interest and principal. In practice, the terms are used interchangeably for a loan repayment table.

Can I use an interest schedule for a credit card?

Credit cards typically have a daily periodic rate and a minimum payment that changes based on the balance, so a standard fixed-payment schedule does not apply. You can model a card by using a daily rate (APR ÷ 365) and a fixed payment you choose, but the schedule would only be an approximation.

Why does my Excel schedule not end at zero?

This usually happens because the payment is not rounded to the cent, or because you used the unrounded payment in the interest calculation. Always round the payment to two decimals, and in the final period, adjust the payment to exactly the remaining balance plus interest.

How do I add extra payments to an interest schedule?

Add a column for “extra payment” and subtract it from the balance after the regular principal payment. The next period’s interest is then calculated on the reduced balance. Continue until the balance reaches zero, and count the periods to find the new payoff term.

Is the interest schedule the same as the lender’s statement?

No. Lenders may use slightly different rounding, charge fees, or apply payments differently. The schedule is a close estimate based on the standard formula. The lender’s disclosure and monthly statements give the exact figures.

The result from this calculator is an estimate; your lender’s disclosure provides the exact figures for your loan.

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 →