Loan Repayment Schedule Calculator in Excel

A loan repayment schedule calculator in Excel shows each payment’s interest, principal, and remaining balance over the loan term. Enter the loan amount, annual APR, and term to calculate an estimated monthly payment and total interest; the schedule formula and a worked example are below.

Formula

The calculator uses the standard fixed-payment amortization formula. It assumes monthly payments and a fixed annual percentage rate converted to a monthly rate:

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

• P is the amount borrowed, in dollars.
• APR is the annual percentage rate entered as a percentage, such as 8 for 8%.
• r is the monthly rate, calculated as APR ÷ 1,200. For an 8% APR, r is 0.0066667 per month.
• n is the number of monthly payments, calculated as years × 12.

The monthly payment shown here is rounded to cents for display. Total interest is calculated as the displayed payment multiplied by the number of payments, minus the original principal: Total interest = monthly payment × n − P. A real loan’s final payment may differ because of rounding, payment dates, fees, or the lender’s calculation method.

Worked Example

Using the calculator’s defaults—$20,000 borrowed, 8% APR, and a five-year term:

1. Convert the annual APR to a monthly rate: 8 ÷ 1,200 = 0.0066667.
2. Find the number of payments: 5 × 12 = 60.
3. Substitute the values: $20,000 × 0.0066667 ÷ [1 − (1 + 0.0066667)⁻⁶⁰].
4. The monthly payment is approximately $405.53.
5. Total of scheduled payments: $405.53 × 60 = $24,331.80.
6. Estimated total interest: $24,331.80 − $20,000 = $4,331.80.

Assumptions

• The interest rate remains fixed for the full term.
• Payments are equal, monthly, and made at the end of each payment period.
• Interest compounds monthly using APR divided by 12.
• Fees, taxes, insurance, mortgage insurance, escrow, late charges, and extra payments are not included.

Building a Repayment Schedule in Excel

Excel can expand the payment estimate into an amortization table, with one row for each month. A basic layout might have columns for payment number, opening balance, scheduled payment, interest, principal, and closing balance. Put the loan amount, annual APR, term in months, and payment formula in separate input cells so the schedule can refer to them consistently.

For example, if the annual APR is in cell B2 and the loan term in months is in B3, the monthly interest rate is B2/12/100 when B2 contains a percentage entered as a whole number such as 8. If Excel stores the rate as a percentage such as 8%, use B2/12 instead. That distinction prevents a common hundredfold error.

1. Set the first row’s opening balance equal to the principal.
2. Calculate that row’s interest as opening balance × monthly rate.
3. Calculate principal repaid as scheduled payment − interest.
4. Calculate closing balance as opening balance − principal repaid.
5. For the next row, link opening balance to the prior row’s closing balance, then fill the formulas down for the remaining months.

In a standard amortizing loan, interest is usually a larger part of early payments because the balance is higher. As the balance falls, the interest portion generally falls and more of each level payment reduces principal. Excel’s PMT function can calculate a periodic payment, but its result is negative under its cash-flow sign convention when the principal is entered as a positive value. A formula such as =-PMT(monthly_rate, number_of_months, principal) returns a positive payment for that setup.

How to Read the Schedule

The payment amount is not the same as the principal reduction. Each payment is divided between interest and principal, while the remaining balance tracks how much principal is still unpaid under the model. The interest column is calculated from the opening balance for that period. The closing balance should decline over time and approach zero after the final scheduled payment.

For a schedule with rounded currency values, small residual balances can appear in the last row. This happens when Excel rounds interest or principal each month instead of retaining full precision. Do not silently treat a negative last balance as extra money owed: check the formulas and rounding, and if the table is intended to mirror a real contract, use the lender’s stated payment and final-payment method.

Common Errors and Limits

• Entering APR in the wrong form: The calculator expects 8 for 8%, not 0.08. In a spreadsheet, confirm whether the cell is formatted as a percentage before choosing the formula.
• Mixing years and months: A five-year term means 60 monthly payments. Feeding 5 into a formula expecting months produces a misleading result.
• Using the wrong payment timing: The formula assumes payments at the end of each month. A different schedule or irregular payment dates can change interest calculations.
• Ignoring fees or a variable rate: This estimate does not model origination charges, payment-specific fees, rate changes, or other contract terms.
• Rounding every row: Rounding intermediate calculations can make the final balance differ slightly from zero. Keep full precision in formulas and format cells to display cents, unless the contract specifies another method.

The formula is for a fully amortizing loan with a positive fixed rate and equal monthly payments. It does not calculate a repayment schedule for interest-only periods, a changing rate, skipped payments, or a balloon balance. For those cases, the schedule must represent the actual payment rules rather than relying on the level-payment formula.

Frequently Asked Questions

Can Excel calculate a loan repayment schedule?
Yes. Use PMT or the payment formula to calculate the regular payment, then calculate monthly interest, principal repaid, and the balance in successive rows. Check that the APR and term units match the spreadsheet formulas.

What Excel formula calculates a monthly loan payment?
Excel’s PMT function can calculate it: =-PMT(APR/12, years*12, principal) when APR is stored as a decimal, such as 8% rather than 8. The minus sign makes the payment positive for a positive principal input.

Why does the principal portion increase over time?
With a fixed-rate amortizing loan and level payments, interest is calculated on the remaining balance. As that balance declines, the interest charge generally declines too, leaving more of the same payment to reduce principal.

Does total interest include fees or insurance?
No. This calculation estimates interest from the principal, APR, term, and payment formula. It does not include fees, taxes, insurance, escrow, or other charges unless they are explicitly incorporated into the inputs.

Why is my Excel balance not exactly zero at the end?
Displayed values may be rounded while calculations retain more decimal places, or each period’s interest may have been rounded separately. The lender may also adjust the final payment. Review cell precision and the contract’s repayment method.

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

Related

• UWM Loan APR: How to Calculate Your Monthly Payment
• Used Car Without Down Payment Loan? Estimate Payments
• Used Car Loan APR Calculator and Payment Formula

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