Amortization Schedule With Extra Payments XLS

An amortization schedule with extra payments in XLS format shows how each regular payment and additional payment changes a loan balance over time. This calculator estimates the regular monthly payment and total interest for a fixed-rate loan; enter the loan amount, APR, and term, then use the schedule method below to model extra payments in a spreadsheet.

Formula

The calculator uses the standard fixed-payment loan formula. Let the monthly rate be r = APR / 1,200, and let n = years × 12 be the number of monthly payments. The monthly payment is M = P × r / [1 − (1 + r)⁻ⁿ]. Estimated total interest is I = M × n − P.

• P is the starting principal in dollars.
• APR is the annual percentage rate entered as a percentage, such as 8 for 8% per year.
• r is the monthly rate as a decimal.
• n is the number of monthly payments.
• M is the calculated monthly payment in dollars.
• I is total interest in dollars across the scheduled term.

The formula assumes payments are level and made monthly. The displayed payment is rounded to cents, while the formula’s intermediate calculation may retain more precision; actual loan servicing can round each transaction and adjust the final payment.

Worked Example

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

1. Convert the APR to a monthly decimal rate: r = 8 ÷ 1,200 = 0.0066667 (approximately).
2. Convert the term to months: n = 5 × 12 = 60.
3. Substitute into the payment formula: M = 20,000 × 0.0066667 ÷ [1 − (1.0066667)⁻⁶⁰].
4. The monthly payment is approximately $405.53.
5. Estimated total interest is 405.53 × 60 − 20,000 = $4,331.80.

This example is the scheduled-payment baseline, not an extra-payment result. Extra payments require a month-by-month balance calculation because they change the principal on which later interest is charged.

Assumptions

• The interest rate stays fixed for the full term.
• Payments are made monthly at the end of each period, after that period’s interest accrues.
• Interest is modelled monthly at APR divided by 12; the lender may calculate interest differently.
• Fees, taxes, insurance, PMI, escrow, and other charges are not included.
• The schedule does not account for payment holidays, late payments, rate changes, or lender-specific allocation rules.

Build an Extra-Payment Schedule in XLS

In Excel or another spreadsheet that opens XLS/XLSX files, create one row for each payment period. Put the original principal, APR, term, and planned additional payment in clearly labelled input cells. Then add columns for period, opening balance, regular payment, extra payment, interest, principal paid, and closing balance.

For each month, calculate interest on the opening balance, subtract the principal portion of the regular payment and the extra amount, and carry the resulting balance to the next row. A useful structure is:

• Interest: opening balance × monthly rate.
• Regular principal: regular payment − interest.
• Closing balance: opening balance − regular principal − extra payment.

In spreadsheet terms, if the opening balance is in B2, monthly rate in a fixed input cell, regular payment in C2, and extra payment in D2, then interest can be calculated as B2 times the monthly rate; the closing balance is B2 minus (C2 minus interest) minus D2. The next row’s opening balance equals the previous row’s closing balance. Use absolute references for fixed input cells so formulas copy down correctly.

Do not let the final row produce a negative balance. Cap the amount applied to principal at the balance remaining after interest, or reduce the last payment to the amount due. Otherwise, a spreadsheet can show an overpayment that would not match a real payoff amount. Confirm how the lender applies extra funds; some may require instructions for principal-only treatment, and timing can affect interest.

Reading the Schedule and Comparing Scenarios

Each row reveals how the payment is divided. Early in a fully amortizing loan, the balance is larger, so the interest portion tends to be larger. As principal falls, the interest portion generally falls and more of each level payment reduces principal. An extra payment applied to principal reduces the balance sooner, which usually reduces later interest under the same fixed-rate assumptions.

To compare scenarios, keep the loan inputs unchanged and alter only the extra-payment amount or timing. Compare the payoff month and the sum of the interest column. Make sure the schedule uses the same assumptions in every scenario, and do not confuse “extra paid” with “interest saved”: extra payments include principal that would otherwise have been paid later.

Common Spreadsheet Problems and Limits

• Wrong rate units: If APR is entered as 8, divide by 1,200 to get a monthly decimal rate. If entered as 0.08, the formula must be adjusted; mixing conventions causes major errors.
• Rounding every step: Rounding interest or balances too early can create a different final balance. Keep sufficient precision in formulas and display cents separately.
• Extra payment exceeds the balance: Cap the last payment so the schedule ends at zero rather than below zero.
• Payment timing differs: A payment made at the start of a period or on a different date can change the interest calculation.
• Payment is not enough: If the regular payment does not cover accrued interest, the balance may grow rather than amortize. Check the lender’s stated payment and loan terms.

The calculator’s total interest is a simplified baseline using the rounded displayed monthly payment for the total. An XLS schedule can produce a slightly different sum because it calculates interest period by period, handles the final payment separately, and may use daily accrual or lender-specific rounding. For an exact payoff figure, use the lender’s current payoff statement rather than treating a spreadsheet balance as a quote.

Frequently Asked Questions

Can I download an XLS amortization schedule with extra payments?
This page provides the calculation and spreadsheet layout, not a downloadable file. You can reproduce the schedule in Excel or another compatible spreadsheet by entering the loan inputs and copying the monthly formulas down.

Does an extra payment reduce the monthly payment?
Not necessarily. In many fixed-payment schedules, an extra principal payment shortens the time to repay while the required regular payment stays the same. The lender’s terms determine whether a recast or payment change is available.

Should I enter extra payments as principal-only?
Model them as principal reductions only if that matches the lender’s payment-allocation process. Check the loan documents or ask the servicer how to designate an additional amount.

Why does my spreadsheet show a different payoff date?
Differences can come from daily rather than monthly interest, payment dates, rounding, fees, an irregular final payment, or an extra payment being applied in a different period.

Is the calculator’s total interest exact?
No. It is an estimate based on a fixed rate, monthly periods, and level payments. Actual amounts depend on the contract and how the lender posts payments.

The result is an estimate, and the lender’s disclosure gives the exact figures.

Related

• Yield vs. Effective Interest Rate: What’s the Difference?
• Japan Interest Rate Decision: What the BOJ Decides
• US Interest Rate: What It Means and How It Works

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