Loan Amortization Excel With Extra Payments

A loan amortization schedule with extra payments shows how each payment is split between interest and principal, and how additional principal changes the balance and payoff date. Enter the loan amount, APR, term, and planned extra monthly payment to see the scheduled payment, estimated payoff time, total interest, and a month-by-month schedule.

Formula

This calculator uses a standard fixed-payment amortization formula. It does not have an extra-payment input, so it calculates the scheduled monthly payment and interest for the original term; it cannot calculate the effect of extra payments or produce a month-by-month schedule. To model extra payments in Excel, add a separate extra-principal input and calculate each month’s actual payment and remaining balance.

The scheduled payment formula is M = P × r ÷ [1 − (1 + r)⁻ⁿ], where the monthly rate is r = APR ÷ 1,200 and the number of payments is n = years × 12. The calculator rounds the monthly payment to cents. Total interest in this simplified result is I = M × n − P, using that rounded payment.

• P is the starting loan principal, in dollars.
• APR is the annual percentage rate entered as a percentage, not a decimal.
• r is the monthly interest rate as a decimal.
• n is the scheduled number of monthly payments.
• M is the calculated monthly payment in dollars.
• I is the estimated interest paid over the scheduled term, in dollars.

Worked Example

Using the calculator’s default values: a $20,000 loan, 8% APR, and a five-year term.

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

These results describe the scheduled-payment loan without extra payments. The calculator’s first output is the monthly payment, and its second is the corresponding total interest estimate.

Assumptions

• The rate is fixed for the full modeled term.
• Payments are monthly and made at the end of each period.
• Interest accrues monthly using APR divided by 12.
• The calculation excludes extra payments, fees, taxes, insurance, PMI, and escrow.

How to Model Extra Payments in Excel

For a true extra-payment schedule, create columns for the month, beginning balance, scheduled payment, extra principal, interest, principal paid, and ending balance. Use one row for each payment period. For a standard fixed-rate loan, a typical month’s interest is the beginning balance multiplied by the monthly rate. The principal portion is the actual payment minus that interest, and the ending balance is the beginning balance minus principal paid.

1. Enter the original principal, APR, term, and the lender’s required monthly payment. You can use the formula above to estimate the payment, but compare it with the loan disclosure.
2. Set the first row’s beginning balance to the original principal. For later rows, link beginning balance to the prior row’s ending balance.
3. Calculate interest as beginning balance × APR ÷ 12. Enter the planned extra amount separately from the scheduled payment.
4. Calculate principal paid as scheduled payment + extra principal − interest. Subtract that principal from the beginning balance to get the ending balance.
5. Continue until the balance reaches zero, and make the final payment no larger than the remaining principal plus that period’s interest.

For example, if a row starts with a $20,000 balance at 8% APR, its simplified monthly interest is about $133.33. If the scheduled payment is $405.53 and you add $100 toward principal, about $372.20 of that month’s $505.53 payment reduces principal; the remaining balance is about $19,627.80. Subsequent interest is calculated on that smaller balance. This example assumes monthly accrual and does not account for lender-specific payment handling.

Reading the Schedule and Choosing Inputs

Use the original principal actually financed, rather than the purchase price if a down payment or other financing changes the amount borrowed. Enter APR as a percentage, such as 8 for 8%, and the term in years. For an Excel schedule, use the contractual payment and the lender’s method for applying extra payments when available. Keep scheduled and extra amounts in separate cells so it is clear which part is optional in the model.

Compare the balance path and total interest in the baseline schedule with a second schedule that includes the additional principal. In the second schedule, keep the interest calculation tied to the current balance. Applying a fixed interest amount each month would not reflect amortization. If you want to estimate a one-time extra payment, enter it in the month it is expected to be applied, rather than treating it as recurring.

Common Excel Problems and Limits

• Extra payment treated as a regular payment: Adding it to the scheduled payment can work for a simple model, but label it separately and ensure it is applied to principal, not held for a future installment.
• Negative ending balances: The final payment may need to be capped at the amount owed. A schedule that subtracts a full regular payment after payoff can show an unrealistic negative balance.
• Rounding differences: A lender may round interest for each period to cents, while a spreadsheet keeps more precision. Over many payments, small rounding differences can change the last payment.
• Different accrual rules: Some loans calculate interest using actual days, payment dates, or other contract terms. APR divided by 12 is a useful standard estimate, not a universal servicing method.
• Payment allocation: Extra funds may be handled differently depending on the loan’s terms and servicing instructions. Confirm how a payment is credited and whether any prepayment charge or restriction applies before relying on a projection.

Excel functions such as PMT can calculate a level scheduled payment, but a simple PMT result alone does not model early payoff. A row-by-row balance schedule is needed to show the changing interest and the effect of extra principal. Do not infer a lender’s actual payoff amount from a spreadsheet: payoff figures can depend on the date and accrued interest.

Frequently Asked Questions

How do I make a loan amortization schedule in Excel with extra payments?
Set up monthly rows for beginning balance, interest, scheduled payment, extra principal, principal paid, and ending balance. Calculate interest from the current balance, apply the extra amount to principal, and continue until the balance is paid off.

Does an extra payment automatically reduce loan interest?
In a schedule where the extra amount is applied to principal, the balance falls sooner and later interest calculations use a smaller balance. The real effect depends on when and how the loan servicer credits the payment.

Should extra payments go in a separate Excel column?
Yes. A separate column makes it easier to distinguish the required installment from extra principal and to test different additional-payment amounts without changing the scheduled payment formula.

Why does my Excel payoff date differ from the lender’s?
Differences can come from payment dates, daily versus monthly interest calculations, rounding, payment allocation, or a final payoff amount that includes accrued interest. Check the loan documents and current lender figures.

Can I use this calculator to include extra payments?
No. It calculates the scheduled monthly payment and baseline interest only. Use a month-by-month spreadsheet or a calculator with an extra-payment input to estimate an accelerated payoff.

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

Related

• 10-Year Business Loan Amortization Schedule Calculator
• 10 Year Loan Amortization Schedule Calculator
• Union Bank Term Loan Interest Rate: Estimate Payments

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