Bank Loan Repayment Schedule Excel Calculator

A bank loan repayment schedule in Excel shows each payment’s split between principal and interest, plus the remaining balance. Enter the loan amount, annual APR, and term in the calculator below to estimate the monthly payment and total interest; you can also build a detailed schedule in Excel using the formulas explained here.

Formula

The calculator uses the fixed-payment amortization formula. It first finds the monthly payment, rounds that payment to cents, and then estimates total interest using the rounded payment across the full term.

• Monthly payment: M = round(P × r ÷ [1 − (1 + r)⁻ⁿ], 2)
• Total interest: I = M × n − P
• Monthly rate: r = APR ÷ 1200

P is the principal or starting loan balance 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, calculated as loan term in years × 12; M is the monthly payment in dollars; and I is estimated total interest in dollars. The formula assumes equal monthly payments and a constant rate. It does not calculate a lender’s exact payoff quote.

Worked Example

Using the calculator’s default values—$20,000, 8% APR, and 5 years—there are 5 × 12 = 60 monthly payments. The monthly rate is 8 ÷ 1200 = 0.0066667.

M = round(20,000 × 0.0066667 ÷ [1 − (1 + 0.0066667)⁻⁶⁰], 2) = $405.53 per month.

Total paid using the rounded payment is $405.53 × 60 = $24,331.80. Estimated total interest is $24,331.80 − $20,000 = $4,331.80. Actual statements may differ because a lender can use its own payment dates, rounding, and interest calculation conventions.

Assumptions

• The interest rate remains fixed for the entire term.
• Payments are level, made monthly, and treated as occurring at regular intervals.
• Interest compounds monthly at APR ÷ 12.
• Fees, taxes, insurance, PMI, escrow, late charges, and extra payments are not included.

Build a Repayment Schedule in Excel

A full amortization table applies interest and principal month by month. Start with the loan details in separate cells—for example, B1 for principal, B2 for APR, and B3 for the number of years. Put the payment formula in B4 as =ROUND(-PMT(B2/12,B3*12,B1),2). Excel’s PMT function returns a negative cash-flow value when the loan amount is positive, so the minus sign makes the displayed payment positive. This formula treats B2 as a decimal, such as 8% entered as 0.08; if you enter 8 as a whole number, use B2/1200 instead.

Set up columns for payment number, beginning balance, payment, interest, principal, and ending balance. In the first schedule row, use payment number 1 and the original principal as the beginning balance. Calculate interest as beginning balance × APR ÷ 12. Calculate principal repaid as payment minus interest, then ending balance as beginning balance minus principal repaid. For each following row, carry the prior ending balance forward as the next beginning balance and repeat the calculations. Fill rows down for the number of monthly payments.

For example, with the defaults above, the first month’s interest is $20,000 × 8% ÷ 12 = $133.33. Subtracting that from the $405.53 payment gives $272.20 of principal, leaving an estimated balance of $19,727.80. In a spreadsheet, formulas can reference the cells for APR and payment so changing an input updates the schedule. Use absolute cell references for fixed inputs, such as $B$2, before copying formulas down.

How to Read and Check the Schedule

Each row represents one payment period. Early in a standard amortizing loan, the balance is larger, so the interest portion is usually larger and the principal portion smaller. As the balance declines, the interest amount generally falls and more of the same payment goes to principal. The final balance should be zero or very close to it, subject to rounding and any lender-specific adjustments.

• Check that each row’s payment equals interest plus principal.
• Check that ending balance equals beginning balance minus principal repaid.
• Sum the interest column to estimate interest over the schedule; compare it with total payments minus original principal.
• If the last row has a small residual, it may come from rounding. Do not silently change a lender’s payment amount; confirm the actual final payment on the lender’s schedule.

Common Excel Issues and Limits

A frequent error is mixing percentage formats. Entering 8% as 8 in a formula that expects 0.08 multiplies the rate by 100 and produces an incorrect result. Another is using the annual rate directly as a monthly rate rather than dividing by 12. Confirm whether the APR cell contains a percentage value or a whole number, and make the formula match.

Spreadsheet results are estimates because the basic model assumes regular monthly periods and a fixed rate. Variable rates, irregular first periods, daily interest accrual, payment holidays, fees added to the balance, extra principal, and payment-date changes can alter the schedule. If an extra payment is made, model it separately and ensure the added amount reduces principal; the lender’s treatment may vary. For a variable-rate loan, a single fixed-rate schedule cannot predict future payments.

Frequently Asked Questions

How do I make a loan repayment schedule in Excel?
Calculate the payment with PMT, then create columns for beginning balance, interest, principal, and ending balance. Calculate interest from the current balance and monthly rate, subtract it from the payment to find principal, and carry the ending balance into the next row.

What Excel formula calculates a monthly loan payment?
Use =-PMT(monthly_rate, number_of_payments, loan_amount). For an APR entered as a decimal and a term in years, a common form is =-PMT(APR/12, years*12, principal). Apply rounding if you want payments shown to cents.

Does a repayment schedule show the total interest?
Yes. Sum the interest column for the scheduled periods. Alternatively, subtract the original principal from total scheduled payments, while accounting for payment rounding and any fees or extra payments that the schedule includes.

Why does my Excel loan balance not reach exactly zero?
Small differences can result from rounding each month, using a different rate convention, or an incorrect final-period formula. The lender may adjust the last payment. Check the formulas and compare with the lender’s disclosed schedule rather than treating a spreadsheet estimate as the payoff amount.

Can I include extra payments in the schedule?
Yes. Add a separate extra-principal column and subtract that amount from the balance after calculating the regular payment’s principal portion. The schedule is only an estimate unless it matches the loan terms and the lender’s handling of additional payments.

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

Related

• Small Personal Loan APR Calculator and Cost Guide
• Student Loan APR: Calculate Payments and Interest
• Small Business Loan Without Down Payment?

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