Loan Repayment Schedule Excel Sheet: Formula and Example

A loan repayment schedule Excel sheet shows each payment’s interest, principal, and remaining balance over time. This calculator estimates a fixed monthly payment and total interest from the loan amount, APR, and term; you can use the same figures to build a schedule in Excel.

Formula

The calculator uses the standard fixed-payment amortization formula. It calculates the payment before rounding for display; the total-interest output uses the displayed monthly payment rounded to cents.

• Monthly payment: M = P × r / (1 − (1 + r)⁻ⁿ)
• Total interest: I = (M × n) − P
• Monthly rate: r = APR / 1200
• Number of payments: n = years × 12

P is the starting loan principal in dollars, APR is the annual percentage rate entered as a percentage, r is the monthly rate as a decimal, n is the number of monthly payments, M is the monthly payment in dollars, and I is total interest in dollars. The formula assumes equal payments at regular monthly intervals. The calculator’s payment output is rounded to two decimal places; its interest calculation uses that rounded payment, so actual lender schedules can differ slightly because of rounding conventions and payment dates.

Worked Example

Using the default inputs—$20,000 principal, 8% APR, and a five-year term—the schedule has 60 monthly payments.

1. Convert APR to a monthly decimal rate: 8 ÷ 1200 = 0.0066667.
2. Calculate the payment: $20,000 × 0.0066667 ÷ [1 − (1 + 0.0066667)⁻⁶⁰] ≈ $405.53 per month, rounded to cents.
3. Calculate total paid using the rounded payment: $405.53 × 60 = $24,331.80.
4. Subtract the original principal: $24,331.80 − $20,000 = $4,331.80 total interest.

The result is an estimate based on the stated inputs and assumptions, not a lender’s account-specific payoff figure.

Assumptions

• The interest rate is fixed for the full term.
• Payments are made monthly, in equal amounts, at the end of each period.
• Interest is modelled monthly at APR divided by 12.
• Fees, taxes, insurance, PMI, escrow, late charges, and extra payments are not included.

How to Build the Schedule in Excel

A spreadsheet schedule applies the same payment calculation to each period. Start with columns for payment number, opening balance, scheduled payment, interest, principal repaid, and closing balance. Put the loan inputs in separate cells so you can update them without rewriting every row.

1. Enter principal, annual APR, and term in years in input cells.
2. Calculate the monthly payment with Excel’s PMT function. For example, if APR is in B2, years in B3, and principal in B1, use =-PMT(B2/12,B3*12,B1). The minus sign makes the payment display as a positive amount when principal is entered as positive.
3. In the first schedule row, set opening balance equal to the original principal. Calculate interest as opening balance multiplied by APR divided by 12.
4. Calculate principal repaid as payment minus interest. Calculate closing balance as opening balance minus principal repaid.
5. For the next row, link opening balance to the previous row’s closing balance, then copy the formulas down for the number of payments.

For example, the first month on the default loan starts at $20,000. Interest is about $133.33 ($20,000 × 8% ÷ 12); from a $405.53 payment, roughly $272.20 reduces principal. The remaining balance is about $19,727.80. Subsequent interest generally declines as the balance falls, while more of each level payment goes toward principal.

Reading and Checking an Amortization Schedule

Each row describes one payment period, not a separate fee or extra charge. “Interest” is the portion attributed to borrowing for that period; “principal” is the amount that reduces the debt. The closing balance should become the next row’s opening balance. Over the full term, principal repaid should add up to the original amount, subject to rounding, and the final balance should be zero or very close to it.

In Excel, small residual balances can arise when each row is rounded to cents. A schedule may handle the last payment by adjusting it slightly to clear the balance. Avoid forcing every intermediate value to two decimals unless the lender’s method does that: calculating with full precision and displaying cents often produces a closer mathematical schedule, while actual statements may round interest each period. Check the lender’s stated method if matching an account statement matters.

Common Spreadsheet Mistakes and Limits

• Using 8 instead of 8%: If the APR input is stored as the number 8, divide by 1200 to get the monthly decimal rate. If Excel stores it as 8%, divide by 12 instead; dividing a percentage-formatted 8% by 1200 would make the rate far too small.
• Mixing years and months: A five-year term means 60 monthly payments. The PMT period count and the monthly rate must use the same period.
• Sign confusion: Excel’s PMT function commonly returns a negative value when the present value is positive, because it treats payments as cash flowing in the opposite direction. Use consistent signs or negate the result for display.
• Assuming every loan has fixed monthly amortization: Variable rates, irregular payment dates, interest-only periods, balloon payments, daily interest, and fees require different calculations.
• Treating the schedule as a payoff quote: A payoff amount may account for accrued interest through a particular date and other account-specific items that this basic model does not include.

Extra payments can be modelled as additional principal reductions, but their timing and treatment matter. A lender may apply an extra amount differently depending on the account instructions, and it may change the payoff date rather than the scheduled payment. This calculator does not model those details.

Frequently Asked Questions

How do I make a loan repayment schedule in Excel?
Calculate the level payment with PMT, then make a row for each month showing opening balance, interest, principal paid, and closing balance. Link each row’s closing balance to the next row’s opening balance.

What Excel formula calculates a monthly loan payment?
For APR stored as a decimal, use =-PMT(APR/12,years*12,principal). For example, enter 8% as 0.08 or as a percentage-formatted cell. The periods must be monthly if the rate is divided by 12.

Why does the interest portion shrink over time?
With a fixed rate and level payments, interest is calculated on the remaining principal. As principal is repaid, the balance falls, so the interest portion usually declines and the principal portion rises.

Why does my Excel total differ from the lender’s schedule?
Possible causes include payment dates, daily rather than monthly interest, rounding rules, fees, rate changes, or extra payments. Compare the schedule’s assumptions with the lender’s loan documents and account figures.

Does this estimate include taxes or insurance?
No. It estimates principal-and-interest payments only. Taxes, insurance, escrow, fees, PMI, and other charges are excluded unless entered separately in a different model.

This result is an estimate for educational use; the lender’s disclosure gives the exact figures.

Related

• Zero Down Payment Car Loan Calculator and Costs
• Zero Down Payment Car Loan in Qatar: What to Know
• Zero APR Car Loan Calculator and Payment Guide

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