Loan Payment Schedule Excel: Formula and Example

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

Formula

For a fixed-rate loan with equal monthly payments, the payment is the loan principal multiplied by the monthly interest rate, divided by one minus the discount factor:

PMT = round(P × r / (1 − (1 + r)−n), 2)

• P is the starting principal, in dollars.
• r is the monthly interest rate as a decimal: APR ÷ 1200.
• n is the number of monthly payments: loan term in years × 12.
• PMT is the monthly payment, rounded to the nearest cent.

Total interest in this calculator is PMT × n − P, also in dollars. It uses the rounded monthly payment, so it represents the sum of the scheduled payments minus the original principal. A real final payment can differ slightly if a lender adjusts it to clear a small rounding balance.

Worked Example

Using the calculator’s defaults: principal P = $20,000, APR = 8%, and term = 5 years.

1. Convert the annual percentage rate to a monthly decimal: r = 8 ÷ 1200 = 0.0066666667.
2. Convert the term to payments: n = 5 × 12 = 60.
3. Substitute into the payment formula: PMT = round(20,000 × 0.0066666667 ÷ (1 − (1.0066666667)−60), 2).
4. The monthly payment is $405.53. Total scheduled payments are $405.53 × 60 = $24,331.80.
5. Total interest is $24,331.80 − $20,000 = $4,331.80.

Assumptions

• The interest rate is fixed for the full term.
• Payments are equal, made monthly, and occur at the end of each monthly period.
• Interest accrues monthly using APR divided by 12; the APR is entered as a percentage per year.
• The calculation does not include fees, taxes, insurance, PMI, escrow, late charges, or extra payments.

Building the Schedule in Excel

After calculating the payment, a spreadsheet can split each payment between interest and principal. Put the starting balance in the first row, then calculate that period’s interest as beginning balance multiplied by the monthly rate. Principal paid is the payment minus interest, and ending balance is beginning balance minus principal paid. The next row’s beginning balance equals the prior row’s ending balance.

• Interest: beginning balance × monthly rate.
• Principal: scheduled payment − interest.
• Ending balance: beginning balance − principal.

For the example, the first month’s interest is approximately $20,000 × 0.0066666667 = $133.33. Of the $405.53 payment, about $272.20 goes to principal, leaving a balance near $19,727.80. Later interest amounts generally decline as the balance falls, while the principal portion rises. Calculations displayed to cents can produce small differences if intermediate values are rounded; retain full precision in formulas and format cells as currency instead.

Excel’s built-in PMT function can calculate a level payment. Its rate argument must be the rate per payment period and its number-of-periods argument must use the same period: for monthly payments, use APR divided by 12 and years multiplied by 12. If APR is entered as 8 rather than 0.08, divide it by 1200 for a monthly decimal. Check whether the function returns a negative value due to its cash-flow sign convention; the payment magnitude is the positive amount paid.

Reading and Checking the Results

A payment schedule is useful for seeing how a payment is allocated over time, estimating the balance after a given number of payments, or checking the arithmetic in a fixed-payment loan illustration. It is not necessarily the lender’s official account schedule. Dates, business-day conventions, daily interest, variable rates, payment processing, and adjustments can cause the actual figures to differ.

Useful spreadsheet checks include confirming that each row’s ending balance becomes the next row’s beginning balance, and that principal plus interest equals the payment. Across the schedule, total principal should approach the starting loan amount, and the final balance should approach zero. If the last balance is a few cents above or below zero, inspect rounding and the final payment rather than assuming the loan terms are wrong.

Common Spreadsheet Mistakes and Limits

• Mixing time units: multiplying an annual rate by a monthly balance without converting it gives an oversized interest amount. Use APR divided by 1200 for monthly interest.
• Using the wrong payment count: a five-year monthly schedule has 60 periods, not five.
• Rounding too early: round the payment to cents, but avoid rounding intermediate rates and balances in every row unless reproducing a lender’s stated method.
• Assuming every loan is level-payment: interest-only periods, changing rates, irregular payments, fees added to the balance, and extra payments need different calculations.

The formula describes a standard amortizing loan with a positive, fixed APR and regular end-of-month payments. It does not establish a lender’s actual APR, include charges that may affect the payment, or model a changing rate. Enter the figures supplied for the specific loan rather than treating the example as a rate or offer.

Frequently Asked Questions

What formula calculates a loan payment in Excel?
Excel’s PMT function can calculate a level payment: use the periodic rate, number of periods, and principal. For monthly payments, convert APR to a monthly rate and years to months; check the sign convention of the result.

How do I calculate the interest portion of a payment?
Multiply the balance at the start of the period by that period’s interest rate. Subtract that interest from the payment to find the principal portion.

Why does my Excel loan schedule end with a small balance?
Rounding each month, using a different payment amount, or using a different rate convention can leave a small residual. Retain precision in intermediate formulas and check the final payment against the lender’s schedule.

Does a loan payment schedule include fees and insurance?
Not unless they are explicitly added as inputs or separate line items. This calculation covers principal and modeled interest only; it excludes fees, taxes, insurance, PMI, and escrow.

Can I use this schedule for a variable-rate loan?
Not as a complete schedule. The fixed-rate formula assumes the same APR throughout. A changing rate requires recalculating interest and, depending on the loan terms, the payment after each rate change.

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

Related

• Federal Student Loan Payment Calculator
• Estimated House Payment Affordability Calculator
• Estimating Car Payment Affordability: A Practical 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 | $ | round(p * (apr / 1200) / (1 - (1 + apr / 1200) ^ -(yrs * 12)), 2) | 2
out int | Total interest | $ | pmt * yrs * 12 - p | 2