Amortization Schedule With Additional Payments Excel

Building an Amortization Schedule That Handles Extra Payments

Most people build a basic amortization table in Excel and then try to tack on additional payments as an afterthought. That doesn't work because the standard PMT function calculates a fixed monthly payment based on a set term. When you throw extra principal at the loan, the payment amount doesn't automatically recalculate. You have to change the structure of the spreadsheet so each row reflects the new remaining balance. Here is what a functional Amortization Schedule With Additional Payments Excel workbook looks like. Set up columns for Period, Beginning Balance, Regular Payment, Additional Payment, Total Payment, Principal Portion, Interest Portion, and Ending Balance. The interest for each period is simply the Beginning Balance multiplied by the monthly interest rate. The principal portion of the regular payment equals the total payment minus the interest. Then you subtract the total principal paid—regular plus additional—from the beginning balance to get the ending balance. The key cell formulas are straightforward:

Interest: =Beginning_Balance * Annual_Rate / 12 Regular Payment (fixed): =PMT(Annual_Rate/12, Total_Months, -Loan_Amount) Principal Portion: =Total_Payment - Interest

Ending Balance: =Beginning_Balance - (Principal_Portions + Additional_Payment) The trick is that the Beginning Balance of each row references the Ending Balance of the previous row. That linkage is what makes the whole thing recalculate dynamically when you change the additional payment amounts.

Handling Extra Payments Correctly

Set up the additional payment column so it can stay blank or contain a value. If a row has no extra payment, the additional payment is zero. The total principal reduction for that period becomes the regular principal portion alone. If there is an extra payment, it goes straight to principal in that period. Only interest accrues on whatever balance remains after that principal reduction. I built one of these for a client who wanted to see the impact of paying an extra $500 every month. The spreadsheet was set up correctly, but the loan had an annual percentage rate that was quoted differently than the compounding frequency. The lender used a 365-day year while the amortization assumed a 360-day basis. That discrepancy showed up as a consistent two-dollar-per-month gap between the scheduled payment and what the bank actually charged. I resolved it by entering the exact daily periodic rate from the loan documents instead of dividing the annual rate by 12. It took about ten minutes once I found the right rate in the closing papers.

Common Pitfalls

One issue people run into is that the PMT function returns a negative number by convention. If you do not account for the sign consistently across your formulas, the principal calculations will flip direction and your ending balance will grow instead of shrink. Always make sure the loan amount is negative in the PMT formula or multiply the result by negative one. Keep the sign consistent everywhere. Another problem is prepayment penalties. Some loans charge a fee if you pay down principal above a certain threshold in any given year. A bare amortization schedule does not model that. You need a separate column or a summary section that tracks cumulative extra payments per year and applies a penalty formula when the threshold is crossed. I encountered a loan with a 2 percent prepayment penalty on any extra principal above $1,000 in a rolling 12-month window. The schedule itself worked fine, but the actual savings were less than the output suggested until I added that penalty calculation.

When This Approach Breaks Down

Excel-based amortization schedules with additional payments work well for fixed-rate mortgages and standard installment loans. They become unreliable for adjustable-rate mortgages where the interest rate changes at specific dates. You would need to manually adjust the rate cell at each adjustment period and rebuild the amortization from that point forward. It is possible to automate with INDEX-MATCH lookups against a rate schedule, but the formula complexity increases significantly and errors creep in easily. Another limitation is rounding. Excel stores numbers to 15 decimal places but banks round each payment's principal and interest to the cent. Over 30 years, those rounding differences can shift the final payment by several dollars. If you need bank-accurate results, you should round each principal and interest figure to two decimal places at every row. The payoff date may end up one or two periods different from what the unrounded calculation shows.

Practical Setup Steps

Start by listing your loan inputs at the top: loan amount, annual interest rate, loan term in months, and the start date. Use named cells for these values so your table formulas reference them cleanly. Build the amortization table starting in row 2. Row 1 should contain the column headers. Copy the formula down for as many periods as the loan term. Leave the additional payment column empty except for the periods where you plan to make extra payments. Add conditional formatting to highlight rows where the additional payment is nonzero. This makes it easy to spot which periods you changed. At the bottom of the schedule, add a summary section showing total interest paid, total principal paid, total additional payments made, and the actual payoff date. Use SUMIF formulas to aggregate the additional payments if they are spread across multiple years. This gives you the real picture of how much time and money the extra payments actually saved. There is a simple but effective way to model the extra payment impact without rebuilding the schedule from scratch. Create a scenario analysis by duplicating the entire table and changing only the additional payment values in the copy. Use data tables or a simple lookup to compare outcomes side by side. This takes maybe five minutes and gives you a clear before-and-after view without manual recalculation.