Sample Loan Repayment Schedule Excel: Formula and Example

A sample loan repayment schedule in Excel shows each payment split between interest and principal, along with the remaining balance. Enter the loan amount, annual interest rate, and term in the calculator below to see an estimated monthly payment and total interest; the example and formulas can also be built into an Excel worksheet.

Formula

The calculator uses the standard level-payment formula for a fixed-rate loan with monthly payments. The payment is calculated as:

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

• P is the starting principal, in dollars.
• r is the monthly interest rate as a decimal: annual APR ÷ 1,200. For an 8% APR, r = 0.08 ÷ 12.
• n is the number of monthly payments: term in years × 12.

Total interest is estimated as monthly payment × n − P. The displayed payment is rounded to cents, and total interest uses that displayed payment. In an actual amortization schedule, rounding each payment and interest amount to cents can create a small difference from an estimate based on unrounded calculations.

Worked Example

Using the calculator’s default inputs—$20,000 principal, 8% annual APR, and a 5-year term—the schedule has 60 monthly payments. The monthly rate is 0.08 ÷ 12 = 0.0066667.

1. Calculate the payment: $20,000 × 0.0066667 ÷ [1 − (1.0066667)⁻⁶⁰] = about $405.53 per month.
2. Calculate total paid using the rounded payment: $405.53 × 60 = $24,331.80.
3. Subtract the original principal: $24,331.80 − $20,000 = $4,331.80 total interest.

The payment covers interest for the period first; the remainder reduces principal. In the first month, estimated interest is $20,000 × 0.0066667 = $133.33. About $272.20 of the $405.53 payment therefore reduces the balance, leaving approximately $19,727.80. Later payments generally contain less interest and more principal because interest is calculated on the declining balance.

Assumptions

• The interest rate stays fixed for the full term.
• Payments are equal, made monthly, and applied at the end of each monthly period.
• Interest is calculated monthly using APR divided by 12.
• Fees, taxes, insurance, PMI, and escrow are not included; the calculator has no inputs for them.

Build the Schedule in Excel

A practical spreadsheet places the loan inputs in separate cells, then creates one row per payment. For example, put principal in B1, annual APR in B2 as a percentage, term in years in B3, and the monthly payment in B4. In Excel, the payment formula can be entered as =-PMT(B2/12,B3*12,B1). The minus sign makes the result display as a positive payment when principal is entered as a positive amount. This function assumes regular payments and a fixed periodic rate.

Use columns for payment number, opening balance, payment, interest, principal paid, and closing balance. If row 8 is the first payment row, formulas can be arranged as follows:

• Payment number: enter 1, then continue the sequence.
• Opening balance: link to the original principal on the first row; thereafter link to the previous row’s closing balance.
• Payment: link to the monthly payment cell.
• Interest: opening balance × APR ÷ 12.
• Principal paid: payment − interest.
• Closing balance: opening balance − principal paid.

For a cent-rounded schedule, wrap interest and principal calculations in Excel’s ROUND(...,2) function. The final row may need a payment adjustment so the balance does not become slightly negative from rounding. Do not force a negative balance to zero without checking the last payment: the actual final amount due can differ from a standard payment by a few cents.

How to Read and Check a Schedule

Each row describes one payment period. The opening balance should equal the prior row’s closing balance. Interest plus principal should equal the payment, and the balance should decline when the payment exceeds that period’s interest. Across the full schedule, total principal paid should return the starting balance to approximately zero, while all interest amounts sum to the total interest.

A schedule is useful for seeing the timing of repayment, estimating a remaining balance after a particular number of payments, and checking how payment allocation changes over time. It does not by itself tell you the exact amount required to settle a loan on a particular date; lender calculations may account for payment dates, accrued interest, fees, or other contract terms.

Common Excel Errors and Alternatives

• Entering 8 instead of 8%: Excel may interpret 8 as 800%. Enter 8% or 0.08, and divide the annual rate by 12 for monthly periods.
• Mixing years and months: A five-year monthly schedule needs 60 periods, not five. The APR period and payment frequency must match.
• Using a changing balance incorrectly: Calculate each period’s interest from that row’s opening balance, not the original loan amount.
• Ignoring fees or variable rates: A fixed-rate payment formula does not include charges or future rate changes. Add separate inputs and calculations only when you know how the loan applies them.

Excel’s PMT, IPMT, and PPMT functions can calculate the payment, interest portion, and principal portion for a regular fixed-rate loan. A manually built schedule makes each calculation visible and easier to adapt, while these functions can reduce repetitive formulas. Neither approach substitutes for the lender’s own payment schedule.

Frequently Asked Questions

What should a loan repayment schedule include?
A basic schedule lists each payment period, opening balance, payment amount, interest, principal repaid, and closing balance. It can also show cumulative interest and principal.

How do I calculate loan interest in Excel?
For a simple monthly fixed-rate schedule, multiply the opening balance by the annual APR divided by 12. For an 8% APR, use 8%/12 as the monthly rate, then round the result to cents if matching a cent-based payment ledger.

Why does the Excel balance not reach exactly zero?
Rounding each period’s interest and principal can leave a small residual. Check the payment count, rate units, and formulas, then adjust the last payment to the remaining balance plus final-period interest if the contract’s calculation supports that approach.

Does this schedule work for a variable-rate loan?
Not unchanged. It assumes one fixed APR throughout the term. A changing rate requires the applicable rate for each period and may change the payment or payoff timing.

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 repayment. Fees and the method used to disclose APR are not modeled here.

This result is an estimate for education, and the lender’s disclosure gives the exact figures.

Related

• High-Interest Debt Snowball Calculator and Payoff Guide
• Loan Repayment Schedule Calculator and Example
• Free Debt Snowball Sheet and Payoff Calculator

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