Loan Amortisation Schedule With Extra Payments

Building an amortisation schedule that actually handles extra payments

Most templates you find online calculate standard monthly payments and call it a day. They show principal, interest, and balance column by column until the loan ends. Fine if you never pay extra. The moment you throw a lump sum at the thing, the spreadsheet either breaks or gives you the wrong numbers. I've spent years fixing this for clients and my own loan tracking, so here's how it works in practice. The core mechanic is straightforward. Each month, your payment first covers accrued interest on the outstanding balance. Whatever's left reduces the principal. When you add an extra payment, that entire amount goes toward principal in the same period. The next month's interest calculation runs on a lower balance, which is where the compounding effect comes from. Simple in theory. The problem most people hit is timing. Lenders often apply extra payments in one of two ways: toward principal reduction or toward shortening the loan term. Some even require you to notify them separately. A schedule template doesn't know which method your lender uses, so it can easily show you one outcome when the bank is calculating another.

I ran into this exact issue last year with a commercial real estate loan. The borrower sent me a schedule showing they'd be debt-free three years early based on their extra payments. The lender, however, was applying those prepayments as additional regular payments rather than compressing the term. The gap between the projected payoff date and the actual one was about fourteen months. The workaround was to request a written confirmation of their prepayment application method, then build a second schedule variant into the model where extra payments reduced the total number of periods instead of just the principal balance. Having both versions let the borrower see the full range of possible outcomes.

The structure I use

Start with your loan inputs: original principal, annual interest rate, and total number of months. Calculate the standard monthly payment using the PMT function. That's your baseline. Then build a running balance column that starts at the full principal and subtracts each month's principal portion. For the extra payment section, create a separate column or section where you can input irregular amounts. The key is making sure those extra payments don't just add to the regular payment amount in your formula. They need to sit alongside it so the interest calculation only applies to what's actually remaining. Here's the part most tutorials skip. When you enter an extra payment mid-term, you have to decide whether that period's regular payment stays the same or whether the schedule recalculates going forward. If the borrower is paying a fixed amount monthly and just adds to it whenever possible, keep the base payment constant and let the extra column sit independently. If the goal is to shorten the term, recalculate the monthly payment after each significant prepayment based on the new remaining balance and remaining periods.

Both approaches produce different results. The fixed payment method usually saves more over the life of the loan because the principal drops faster earlier on, which means less interest accrues in subsequent months. Recalculating the payment gives you a lower monthly outflow but may not shave as much total interest. This is counter-intuitive for most people who assume paying less each month is always better.

Pitfalls that will cost you time and money

One common error is double-counting the extra payment. People set up the formula so the extra payment reduces the principal AND reduces the number of remaining periods, which effectively counts the money twice. Check your formulas by verifying that the final balance reaches zero and the total payments made match your inputs. If the balance goes negative, you've counted something twice. Another issue is leap year handling and varying month lengths. Some loans use 360-day year conventions while others use actual days. If your extra payments happen at irregular intervals, the interest accrued between payments changes based on the day count convention. This matters more on larger loans. A $500,000 commercial loan can accrue hundreds of dollars in interest differences depending on whether you use 30/360 or actual/365. Make sure the model you're using matches your loan agreement. There's also the prepayment penalty factor. Some loans, especially in commercial real estate and certain personal loan products, charge a fee if you pay down principal faster than a specified threshold. These penalties can be structured as a percentage of the prepaid amount or as a yield maintenance calculation that essentially guarantees the lender their expected interest over the remaining term. A Loan Amortisation Schedule With Extra Payments that ignores prepayment penalties will show optimistic savings that never materialise. Always check your loan documents for these clauses before trusting the output.

Where this approach falls apart

Straightforward models work fine for fixed-rate loans with predictable payment schedules. They struggle with variable-rate loans where the rate changes periodically. Each time the rate adjusts, you need to recalculate the entire remaining schedule, and the extra payment history can cause reconciliation issues if not tracked carefully. For adjustable-rate mortgages and similar products, consider switching to a period-by-period model that recalculates the payment at each adjustment date rather than trying to force a static formula to handle dynamic rates. The other limitation is loans with biweekly payment structures. Some lenders automatically convert your monthly payment into half-payments every two weeks, which effectively adds one extra payment per year. Your schedule needs to account for this if it's part of your repayment strategy, or the numbers won't align with what the lender actually reports.

A practical setup

I typically build these in a single sheet with the loan parameters locked at the top, the month-by-month breakdown below, and a separate section for extra payment inputs. The extra payments column sits alongside the regular payment columns so the interest calculation always references the correct remaining balance. I add conditional formatting to flag any month where the extra payment would bring the balance below zero, since that indicates either an overpayment or an error in the term calculation. For clients who want projections across multiple scenarios, I duplicate the base schedule and adjust the extra payment columns for each version. This lets them compare a no-extra-payment baseline against a conservative extra payment plan and an aggressive one. The difference in total interest paid between scenarios is usually the eye-opening part of the exercise. On a typical 30-year mortgage, adding just two extra payments per year can cut roughly eight to twelve years off the term depending on the interest rate and remaining balance at the time of the first prepayment. The most useful thing about these schedules isn't the payoff date projection. It's the visibility into how much of each payment goes to principal versus interest over time. Most borrowers don't realise that in the early years of a standard loan, the interest portion dominates by such a large margin that extra payments have minimal impact until enough principal has been reduced to shift the balance. Understanding that trajectory helps people make informed decisions about when to accelerate and when to hold cash reserves instead.