Loan Repayment Schedule in Excel: Formula and Example

A loan repayment schedule in Excel lists each payment, the interest charged, the amount applied to principal, and the balance remaining. This calculator estimates the fixed monthly payment and total interest from a loan amount, APR, and term; you can use those figures to build and check a spreadsheet schedule.

Formula

The fixed-payment formula calculates a payment that repays the principal and interest over the stated number of monthly periods. In symbols:

• Monthly payment: M = P × r ÷ [1 − (1 + r)⁻ⁿ]
• Monthly rate: r = APR ÷ 1,200
• Number of payments: n = years × 12
• Total interest: I = M × n − P

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 estimated interest in dollars. The calculator rounds the payment to cents before using it to calculate total interest.

Worked Example

Using the calculator’s default values—$20,000 principal, 8% APR, and five years:

1. Convert the annual percentage rate to a monthly decimal: r = 8 ÷ 1,200 = 0.0066667.
2. Find the number of payments: n = 5 × 12 = 60.
3. Substitute into the payment formula: M = 20,000 × 0.0066667 ÷ [1 − (1.0066667)⁻⁶⁰] ≈ $405.53 per month, rounded to cents.
4. Calculate estimated total interest using the rounded payment: I = ($405.53 × 60) − $20,000 = $4,331.80.

Assumptions

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

Build the Schedule in Excel

To turn the payment estimate into a row-by-row schedule, create columns for payment number, beginning balance, payment, interest, principal, and ending balance. Put the starting principal in the first beginning-balance cell and the calculated monthly payment in a separate fixed cell. For the first payment row, calculate interest as beginning balance multiplied by the monthly rate. Calculate principal as payment minus interest, then ending balance as beginning balance minus principal. The next row’s beginning balance should link to the previous row’s ending balance.

For example, if the beginning balance is in B2, the payment in C2, the interest in D2, the principal in E2, and the ending balance in F2, the logic is:

• Interest: beginning balance × APR ÷ 12
• Principal: payment − interest
• Ending balance: beginning balance − principal
• Next beginning balance: previous ending balance

Enter the formulas with cell references appropriate to your workbook, then fill them down for the full number of payments. Use an absolute reference (such as $C$1) for a payment or rate stored in a fixed cell so it does not shift when copied. Keep the unrounded calculation precision in intermediate cells and format displayed money to two decimal places. If you round every intermediate interest calculation, small differences can accumulate.

How to Read and Check the Results

Each schedule row divides the payment into interest and principal. Early in a standard amortizing loan, the balance is larger, so the interest portion is usually larger. As principal is repaid, the balance falls and so does the interest charged each month; more of the same payment then reduces principal. This is why a fixed monthly payment does not mean a fixed amount of principal is repaid each month.

Check that the beginning balance in each row matches the previous row’s ending balance, and that payment equals interest plus principal. The final balance should be zero or a small rounding difference. A lender may adjust the last payment to clear a few cents left by rounding. Under this calculator’s specified convention, multiplying the rounded monthly payment by the number of payments and subtracting principal gives the estimated total interest.

When the Estimate May Differ

A spreadsheet is only as accurate as its inputs and timing assumptions. The APR and principal should match the loan documents, and the term should reflect the number of scheduled payments—not simply the number of calendar years if payments are not monthly. If the actual contract uses a variable rate, unusual payment dates, daily interest accrual, a balloon payment, or a different compounding method, this fixed monthly model will not reproduce it exactly.

Also distinguish APR from a nominal interest rate or a quoted periodic rate. This calculator treats the entered APR as a nominal annual rate divided by 12. It does not infer a rate from fees, and it does not model how fees or other charges affect the amount financed. Get the actual rate, payment frequency, and payment amount from the lender’s disclosure or account information.

Common Spreadsheet Problems

• Wrong percentage scale: Enter 8 for 8%, not 0.08, because the formula divides the percentage by 1,200.
• Wrong term units: This calculator takes years and converts them to months. A schedule needs one row per payment period.
• Changing references when filling formulas: Lock input cells with absolute references where needed.
• Negative final balance: A small negative amount may result from payment rounding. Do not treat it as an additional scheduled payment without checking the lender’s actual payoff figure.
• Adding extra payments without updating the model: Extra principal reduces later balances and interest, so a standard schedule no longer describes the actual payment path.

Frequently Asked Questions

How do I calculate a loan payment in Excel?
Use the PMT function with the monthly rate and total number of monthly payments, for example =PMT(APR/12, years*12, -principal). The negative principal sign makes the payment display as a positive amount in a typical spreadsheet. Confirm that the APR is entered in the scale expected by the formula.

How do I calculate monthly interest in a repayment schedule?
Multiply that row’s beginning balance by the monthly rate. With a nominal APR expressed as a percentage, the rate is APR divided by 1,200. Subtract the interest from the payment to find the principal portion.

Why does my Excel schedule end with a small balance?
Rounding displayed values or rounding each row’s calculations can leave a small residual. Retain full precision in formulas and format cells to show cents. The actual lender may adjust the final payment, so verify the payoff amount rather than assuming the spreadsheet controls it.

Does this schedule include extra payments or fees?
No. It models equal monthly payments on principal and interest only. Add separate inputs and revise the balance calculations to model extra principal payments; fees and other charges require their own data and may follow different timing rules.

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

Related

• Consolidated Loan Repayment Schedule Calculator
• Car Loan Repayment Schedule Calculator
• Car Loan Repayment Schedule Calculator and 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