Amortization Table With Extra Payments in Excel

An amortization table with extra payments in Excel shows how each scheduled payment is divided between interest and principal, and how additional principal payments change the balance and payoff date. Enter the loan amount, APR, term, and extra amount per month to estimate the monthly payment, total interest, and payoff time.

Formula

The scheduled monthly payment is calculated using the standard fixed-payment loan formula:

PMT = round(P × r ÷ [1 − (1 + r)⁻ⁿ], 2)

• P is the original loan principal, in dollars.
• APR is the annual percentage rate entered as a percentage.
• r = APR ÷ 1200 is the monthly interest rate as a decimal.
• n = years × 12 is the scheduled number of monthly payments.
• PMT is the scheduled payment rounded to the nearest cent.

This calculator assumes the extra amount is paid every month and applied to principal. For an estimate of payoff time, it uses k = ceil(round[−ln(1 − rP/M) ÷ ln(1 + r), 6]), where M = PMT + extra is the total monthly amount and k is the estimated number of payments. Estimated interest is M × k − P. Because the final payment is usually smaller than the regular amount, the interest estimate can differ slightly from a row-by-row schedule that adjusts the last payment precisely.

Worked Example

Using the calculator’s defaults: principal $20,000, APR 8%, term 5 years, and $100 extra per month:

1. Monthly rate: r = 8 ÷ 1200 = 0.0066667.
2. Scheduled payment count: n = 5 × 12 = 60.
3. Scheduled payment: round[20,000 × 0.0066667 ÷ (1 − 1.0066667⁻⁶⁰), 2] = $405.53.
4. Amount paid monthly toward the estimate: M = $405.53 + $100 = $505.53.
5. Payoff estimate: ceil[round(−ln(1 − 0.0066667 × 20,000 ÷ 505.53) ÷ ln(1.0066667), 6)] = 46 months.
6. Estimated interest: $505.53 × 46 − $20,000 = $5,254.38.

The headline result is the scheduled payment, $405.53. The extra-payment estimate suggests payoff in 46 months, with interest of about $5,254.38 under the stated assumptions.

Assumptions

• The interest rate stays fixed throughout the estimate.
• Payments are monthly and made at the end of each monthly period.
• Interest is modelled monthly at APR divided by 12.
• The extra amount is paid each month and reduces principal; the calculator does not model fees, taxes, insurance, PMI, or escrow.

Build an Amortization Table in Excel

Excel can display the period-by-period detail behind the estimate. Start with input cells for principal, APR, term in months, and extra payment. Calculate the scheduled payment with Excel’s PMT function, for example =-PMT(APR/12,TermMonths,Principal). The negative sign converts Excel’s cash-flow convention into a positive payment amount. Keep the underlying precision in the formula; format displayed currency as dollars and cents.

Create columns for payment number, beginning balance, scheduled payment, extra principal, total payment, interest, principal paid, and ending balance. For a standard end-of-month schedule:

• Beginning balance: the original principal on the first row, then the prior row’s ending balance.
• Interest: beginning balance × APR ÷ 12.
• Principal paid: scheduled payment + extra payment − interest.
• Ending balance: beginning balance − principal paid.

For the final row, cap the total payment at the balance plus that period’s interest so the schedule does not show an overpayment or negative balance. In Excel, a formula such as =MIN(ScheduledPayment+Extra,BeginningBalance+Interest) can cap the payment; then calculate principal as total payment minus interest, and ending balance as beginning balance minus principal. Apply the extra amount only while a balance remains. Copy formulas down far enough to cover the longest possible payoff period, and check that the ending balance reaches zero rather than becoming materially negative.

How to Read the Schedule

At the start of a fully amortizing loan, a larger share of each payment generally goes to interest because interest is calculated on the outstanding balance. As principal falls, the interest portion usually declines and the principal portion grows. An extra payment applied to principal reduces the balance sooner, which can reduce later interest charges and shorten the payoff period.

Compare schedules only when their assumptions match. The loan amount, APR, payment frequency, payment dates, and extra-payment timing all matter. A one-time extra payment is not equivalent to a recurring monthly extra amount. If you want to model a one-time payment in Excel, put that amount in the extra-payment cell for only the intended row.

Excel Pitfalls and Limits

• Applying extra money incorrectly: an extra amount should reduce principal, not be treated as a separate fee or as interest.
• Rounding every row: rounding intermediate calculations can create small discrepancies. Keep full precision in formulas and round only displayed values unless your loan’s actual payment handling requires otherwise.
• Forgetting the final payment adjustment: an extra-payment schedule often pays off before the original term. Cap the final payment at the remaining balance plus interest.
• Assuming lender treatment: the actual servicer may have instructions for how to apply additional funds, and payment dates or posting delays can affect results. Check the loan documents or ask the servicer how to designate principal-only payments.
• Using APR as the exact periodic rate: this estimate divides APR by 12. A loan with fees, variable rates, unusual compounding, or other payment rules may not follow that simplified model exactly.

This calculator is useful for understanding the arithmetic, not for determining what payment is suitable for a household or predicting a lender’s figures. For an exact schedule, use the loan’s actual terms and payment rules.

Frequently Asked Questions

How do I add extra payments to an amortization schedule in Excel?
Enter the extra amount in its own column, add it to the scheduled payment, subtract interest to find principal paid, and subtract principal paid from the beginning balance. Cap the final payment at the amount needed to clear the balance and interest.

Does an extra payment reduce interest or the loan term?
When applied to principal, an extra payment lowers the balance used to calculate future interest. Keeping the scheduled payment unchanged generally shortens the time to payoff; the exact effect depends on the loan terms and payment timing.

Can I use Excel’s PMT function?
Yes. For a fixed-rate monthly loan, use the monthly rate, total number of monthly payments, and principal, such as =-PMT(APR/12,Years*12,Principal). PMT calculates the scheduled payment, not a complete extra-payment schedule by itself.

Why does my Excel balance become negative?
The extra payment may continue after payoff, or a full payment may be applied in the last period when only a smaller amount is due. Stop extra payments once the balance is cleared and cap the final payment at the balance plus that period’s interest.

Is this result the same as my lender’s payoff amount?
No. The estimate does not include lender-specific posting dates, fees, daily interest calculations, or other contract terms. A lender’s payoff statement or disclosure provides the figures applicable to the account.

The result is an estimate for educational purposes; the lender’s disclosure gives the exact figures.

Related

• Social Security Qualifying Income: What Counts?
• SC Qualified Retirement Income Deduction: Rules and Limits
• Section 8 Qualifying Income Level Explained

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
in extra | Extra payment per month | $ | 100 | 0 | 100000000
out pmt | Monthly payment | $ | round(p * (apr / 1200) / (1 - (1 + apr / 1200) ^ -(yrs * 12)), 2) | 2
out int | Estimated interest with extra payments | $ | (pmt + extra) * ceil(round(-ln(1 - (apr / 1200) * p / (pmt + extra)) / ln(1 + apr / 1200), 6)) - p | 2
out months | Estimated payoff time | months | ceil(round(-ln(1 - (apr / 1200) * p / (pmt + extra)) / ln(1 + apr / 1200), 6)) | 0