calculate-loan

01 Loan payments

Repayment Schedule Excel Sheet: Formula and Calculator

Calculator No. 01-19015 · 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 repayment schedule Excel sheet shows each loan payment split between interest and principal, along with the balance remaining after each payment. Use the calculator below to estimate the monthly payment and total interest from a loan amount, annual APR, and term; the schedule can then be built in Excel using the same assumptions.

Formula

For a fixed-rate loan with equal monthly payments, the payment is the amount that amortizes the principal over the number of monthly periods:

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

  • P is the starting loan principal, in dollars.
  • r is the monthly interest rate, calculated as APR ÷ 1,200. APR is entered as a percentage per year.
  • n is the number of monthly payments, calculated as loan term in years × 12.
  • Payment is the monthly amount in dollars, rounded to cents.

In a repayment schedule, interest for a period is generally calculated from the balance at the start of that period: interest = starting balance × r. The principal paid is the payment minus that period’s interest, and the ending balance is the starting balance minus principal paid. Total interest in this estimate is the rounded monthly payment multiplied by the number of payments, less the original principal. Because the payment is rounded to cents, a real final payment may need adjustment.

Worked Example

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

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

Assumptions

  • The interest rate stays fixed throughout the term.
  • Payments are equal, made monthly, and applied at the end of each monthly period.
  • Interest compounds monthly at APR divided by 12.
  • Fees, taxes, insurance, PMI, escrow, late charges, and extra payments are not included.

Build the Repayment Schedule in Excel

Start with input cells for the principal, annual APR, and term in years. For example, put those values in B1, B2, and B3. In B4 calculate the monthly payment with a spreadsheet formula such as =ROUND(-PMT(B2/1200,B3*12,B1),2). The minus sign makes the result display as a positive payment under Excel’s cash-flow sign convention.

Create columns for payment number, opening balance, payment, interest, principal, and closing balance. Put payment number 1 in the first schedule row, and use these formulas, adjusting cell references to match your layout:

  • Opening balance: the original principal on the first row; thereafter, the previous row’s closing balance.
  • Payment: the calculated monthly payment.
  • Interest: opening balance × monthly rate, such as =ROUND(BalanceCell*$B$2/1200,2).
  • Principal: payment − interest.
  • Closing balance: opening balance − principal.

Fill the formulas down for the number of monthly payments. With the example inputs, the first month’s interest is about $133.33; approximately $272.20 of the $405.53 payment reduces principal, leaving a balance near $19,727.80. Small discrepancies can arise if intermediate values are rounded differently. To keep the schedule internally consistent, decide whether to display rounded values only or round each period’s interest and principal to cents.

Reading and Checking the Schedule

Early in a standard amortizing loan, the balance is larger, so the interest portion tends to be larger. As principal is repaid, the interest portion generally falls and more of each level payment goes toward principal. The payment itself remains level in this simplified model, while the balance moves toward zero.

Useful checks include confirming that each row’s payment equals interest plus principal, and that closing balance equals opening balance minus principal. The final balance should be zero or very close after the last payment. If rounding leaves a few cents, a lender’s actual final payment may differ. Do not assume that a spreadsheet estimate overrides the payment amount or schedule provided in a loan disclosure.

Common Problems and Limits

  • Entering APR as 8 instead of 0.08: The formulas here expect APR entered as 8 for 8%, then divide by 1,200 to get the monthly decimal rate. Do not divide by 100 a second time.
  • Mixing years and months: The payment formula uses the total number of monthly periods. Multiply a term in years by 12.
  • Using the wrong balance: Period interest is based on the opening balance for that period, not the original principal every month.
  • Ignoring payment timing or fees: Actual calculations can differ if payment dates, rate changes, fees, or lender-specific methods differ from these assumptions.

This is a fixed-rate, level-payment estimate, not a complete model for every loan. It does not calculate variable-rate changes, irregular payment dates, skipped payments, additional principal, or fee financing. To model extra payments, subtract them from the balance in the period they are made and recalculate subsequent interest and payoff timing. Obtain the actual APR, payment terms, and any included charges from the lender rather than treating the example values as a quote.

Frequently Asked Questions

How do I make a loan repayment schedule in Excel?
Enter the loan inputs, calculate the monthly payment, and create columns for opening balance, interest, principal, payment, and closing balance. Fill the row formulas down for each monthly period.

What is the formula for a monthly loan payment in Excel?
For a fixed rate, use Excel’s PMT function, for example =-PMT(APR/1200,Years*12,Principal). Add ROUND around the result if you want a payment rounded to cents.

Why does the interest portion decrease over time?
In a standard amortizing schedule, interest is calculated on the outstanding balance. As principal falls, the interest calculated for later periods generally falls too.

Why does my Excel balance not reach exactly zero?
Rounding each period to cents can leave a small residual balance. The exact final amount can also differ because a lender may use different payment dates, rounding, or interest calculations.

Does this schedule include extra payments or fees?
No. The estimate assumes level monthly payments and excludes extra payments, fees, taxes, insurance, and other charges. Add separate inputs and formulas only when their amounts and timing are known.

The result is an estimate, and 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 →