calculate-loan

01 Loan payments

Payment Schedule Format in Excel: Build a Loan Table

Calculator No. 01-14590 · 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 payment schedule in Excel is a date-by-date table showing each payment, how much goes to principal and interest, and the remaining balance. For a standard fixed-rate loan, the calculator below estimates the monthly payment and total interest from the loan amount, APR, and term; use those figures to build and check the spreadsheet schedule.

Formula

The fixed monthly payment is calculated with the level-payment loan formula:

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

  • P is the original loan principal in dollars.
  • r is the monthly interest rate as a decimal: APR ÷ 1,200 when APR is entered as a yearly percentage.
  • n is the number of monthly payments: years × 12.
  • Payment is the monthly amount, rounded to cents.

Total interest in this calculator is monthly payment × number of payments − principal. Because the payment is rounded to cents before the total is calculated, this estimate uses the displayed payment throughout. A lender’s actual final installment may differ if its accounting uses unrounded amounts or a different payment schedule.

Worked Example

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

  1. Convert the APR to a monthly decimal rate: 8 ÷ 1,200 = 0.0066667.
  2. Find the number of monthly payments: 5 × 12 = 60.
  3. Apply the payment formula: $20,000 × 0.0066667 ÷ (1 − (1 + 0.0066667)−60).
  4. Round the result to cents: the monthly payment is $405.53.
  5. Calculate total interest using that rounded payment: $405.53 × 60 − $20,000 = $4,331.80.

Assumptions

  • The interest rate stays fixed for the entire loan term.
  • Payments are equal, monthly, and made at the end of each payment period.
  • Interest compounds monthly at APR ÷ 12.
  • Fees, taxes, insurance, escrow, and any lender-specific charges are not included.

How to Set Up a Payment Schedule in Excel

A typical Excel schedule has one row per payment period, plus a starting balance. Put the inputs in clearly labelled cells, such as loan amount, annual APR, term in years, and first payment date. Use cell references in formulas rather than typing the same assumption into many rows; this makes it easier to revise an input and see the schedule update.

Useful columns include:

  • Payment number and payment date.
  • Beginning balance for that period.
  • Scheduled payment.
  • Interest portion and principal portion.
  • Ending balance.

For a monthly schedule, the period’s interest is generally beginning balance multiplied by the monthly rate. Principal paid is the payment minus that interest, and ending balance is beginning balance minus principal paid. The next row’s beginning balance equals the prior row’s ending balance. In Excel, the payment can be calculated with the PMT function using a monthly rate and a negative present value, for example =-PMT(APR/12, Years*12, LoanAmount). Confirm the signs and cell references against your own layout.

For the example above, the first month’s interest is approximately $20,000 × 8% ÷ 12 = $133.33. Subtracting it from the $405.53 payment gives about $272.20 of principal, leaving an approximate balance of $19,727.80. Small differences can arise from rounding at each row. A schedule intended to reconcile exactly should use the lender’s calculation and rounding conventions.

How to Read and Check the Table

At the beginning of a level-payment loan, the outstanding balance is relatively high, so a larger share of each payment typically covers interest. As principal is repaid, the interest portion generally falls and the principal portion rises. The scheduled payment remains level in the simplified fixed-rate example, but the split changes over time.

Check that each row follows the balance relationship: beginning balance minus principal paid equals ending balance. Also check that principal portions add up to approximately the original amount, total payments equal the number of periods times the scheduled payment, and the final balance is near zero. A small residual can be caused by cents rounding; a large or growing residual often signals a formula, rate, or period mismatch.

Excel Functions, Templates, and Limits

Excel’s PMT function returns a level payment, while IPMT and PPMT can calculate the interest and principal components for a specified period. These functions need consistent units: for monthly payments, use a monthly rate and a total number of monthly periods. If a term is entered in years but used directly as the period count, the schedule will be wrong.

A template can save setup time, but inspect its assumptions before relying on its output. Some templates assume payments at period-end, a fixed rate, or regular monthly intervals. They may not model variable rates, irregular dates, fees added to the balance, extra payments, skipped payments, or a nonstandard final installment. Do not treat an amortization table as a substitute for a lender’s payment disclosure.

Common Excel Schedule Problems

  • Entering 8 instead of 8%: If a cell stores 8 as a number, divide by 100 before using it as a decimal rate; if it is formatted as a percentage and stores 0.08, divide by 12 for a monthly rate.
  • Mixing annual and monthly periods: A monthly rate must pair with the number of monthly payments. A yearly rate must not be paired with 60 monthly periods.
  • Rounding every intermediate value: Displaying two decimals is different from rounding the underlying balance at every step. Choose a consistent method and expect minor differences from lender figures.
  • Ignoring the final row: A fixed payment rounded to cents may leave a few cents of balance or overpay by a few cents. Adjusting the last payment may be necessary in a personal worksheet, but the lender’s actual amount controls the transaction.

Frequently Asked Questions

What columns should a payment schedule in Excel have?
A useful basic schedule includes payment number and date, beginning balance, payment amount, interest, principal, and ending balance. Add columns for extra payments or fees only if those are relevant and modelled correctly.

What is the Excel formula for a monthly loan payment?
Use =-PMT(APR/12, Years*12, LoanAmount) when APR is stored as a decimal, such as 0.08 for 8%, and the loan amount is a positive value. The negative sign makes the displayed payment positive under Excel’s cash-flow sign convention.

Does Excel calculate a payment schedule automatically?
Excel provides functions such as PMT, IPMT, and PPMT, but a full schedule usually requires rows that carry each ending balance into the next period. A template may do this for you, but its assumptions still need checking.

Why does my final balance not equal zero?
Common causes include rounding, using the wrong monthly rate, an incorrect payment count, or inconsistent formula references. A small difference may be rounding; a substantial difference warrants checking the inputs and each row’s balance calculation.

Can I use the same schedule for a variable-rate loan?
Not without updating the interest assumptions when the rate changes. A fixed-rate schedule holds the rate constant, so it does not predict future payments or balances for a loan whose rate can change.

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