Prepayment Amortization Schedule Excel Calculator

This prepayment amortization calculator estimates how extra payments change a loan’s balance, payoff time, and total interest. Enter the starting balance, APR, term, and recurring extra monthly amount to see a month-by-month schedule using a fixed-rate, monthly-payment model.

Formula

Important limitation: the calculator block above computes the standard level-payment loan estimate, not an extra-payment amortization schedule. Its required inputs do not include a prepayment amount, and its outputs do not calculate the changing balance or revised payoff date. Use the schedule method below, or a spreadsheet with a separate extra-payment input, to model prepayments accurately.

For the calculator’s regular monthly payment, the formula is M = P × r ÷ [1 − (1 + r)⁻ⁿ], where P is the loan principal in dollars, r = APR ÷ 1,200 is the monthly interest rate as a decimal, and n = 12 × years is the number of monthly payments. The displayed payment is rounded to cents. Total interest in the block is calculated as rounded monthly payment × number of months − principal.

For a spreadsheet schedule that includes extra payments, calculate each month’s interest as beginning balance × monthly rate; principal paid as the regular payment plus extra payment minus interest; and ending balance as beginning balance minus principal paid. In the final month, cap the total payment at the amount needed to clear the balance, so the balance does not go below zero. This is a month-by-month recurrence, not the same formula as the calculator outputs.

Worked Example

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

1. Monthly rate: r = 8 ÷ 1,200 = 0.0066666667.
2. Number of payments: n = 5 × 12 = 60.
3. Unrounded payment: $20,000 × 0.0066666667 ÷ [1 − (1.0066666667)⁻⁶⁰] ≈ $405.5278.
4. Monthly payment rounded to cents: $405.53.
5. Total interest using the block’s formula: $405.53 × 60 − $20,000 = $4,331.80.

These results are the regular-payment estimate only. They do not include any additional principal payments. A schedule that rounds interest and payments each month can differ slightly from a calculation that carries full precision throughout.

Assumptions

• Fixed APR and equal monthly payments are assumed.
• Payments are assumed to occur monthly; the schedule method applies each payment after that month’s interest accrues.
• Interest is modelled monthly at APR divided by 12.
• Fees, taxes, insurance, escrow, and lender-specific prepayment allocation are not included.

How to Build a Prepayment Schedule in Excel

Put the loan inputs in separate cells: original balance, annual APR, term in months, and planned extra payment. Convert APR to a monthly rate by dividing by 12 and then by 100. Calculate the ordinary payment with Excel’s PMT function or the formula above. For instance, with APR entered as a percentage, the spreadsheet function is commonly written as =-PMT(APR/12, TermMonths, Principal); cell references should replace the names.

Create columns for month, beginning balance, interest, scheduled payment, extra principal, total payment, principal reduction, and ending balance. For each row:

• Interest = beginning balance × monthly rate.
• Scheduled payment = the lesser of the regular payment and the amount due to clear the balance after interest.
• Extra principal = the lesser of the planned extra amount and the balance remaining after the scheduled payment.
• Ending balance = beginning balance + interest − scheduled payment − extra principal.

Carry the prior row’s ending balance into the next row as its beginning balance. Stop once the balance reaches zero. In Excel, MAX(0,...) can prevent tiny rounding differences from displaying a negative balance, while MIN(...) can cap the final payment. If a lender applies extra funds to future installments instead of principal, or requires instructions to designate principal-only payments, this simplified schedule will not represent that handling.

How to Read the Results

A useful schedule shows the balance declining over time, with interest calculated on the balance at the start of each period. When an extra payment is applied to principal, later interest is generally lower in this mathematical model because the balance used to calculate it is smaller. The final payoff month may therefore arrive sooner than the original term, and total interest can fall. The exact result depends on payment dates, rounding, and how the loan servicer processes additional money.

For example, if a row starts with a $10,000 balance at 8% APR, one month’s modeled interest is approximately $10,000 × 0.08 ÷ 12 = $66.67. A $405.53 regular payment would reduce principal by about $338.86 before any extra payment. An additional $100 applied to principal would increase that month’s principal reduction to about $438.86, assuming the payment is not capped by the remaining payoff amount.

Common Spreadsheet Errors and Limits

• Using annual APR as a monthly rate: divide the percentage by 1,200, not 12, when the input is entered as 8 rather than 0.08.
• Adding extra money to the payment without tracking principal: interest is based on the outstanding balance, so the schedule must recalculate it each period.
• Allowing a negative final balance: cap the final payment at the amount owed, including that period’s interest.
• Rounding inconsistently: decide whether to round interest each month to cents or retain precision internally. Lender statements may use their own day-count and rounding practices.
• Assuming every extra payment is principal-only: verify the loan’s payment instructions and servicing treatment; this page does not model lender-specific rules.

The block’s total-interest output assumes the rounded regular payment is made for every month in the stated term. In real amortization, a last payment may differ because of rounding. A prepayment model also needs a defined timing convention: an extra amount paid with the regular monthly installment is not necessarily equivalent to one made on a different date.

Frequently Asked Questions

Can Excel calculate a prepayment amortization schedule?
Yes. Use one row per payment period and calculate interest, principal, and remaining balance from the prior row. Add a separate extra-principal input and cap the final payment so the balance does not become negative.

What Excel formula calculates a regular loan payment?
Excel’s PMT function can calculate a level payment: =-PMT(APR/12, months, principal) when APR is entered as a decimal. If APR is entered as a whole percentage such as 8, use APR/1200 as the periodic rate.

Does an extra payment reduce the next monthly payment?
Not necessarily. A principal prepayment may reduce the balance and shorten the modeled payoff time while the contractual scheduled payment stays unchanged. Any payment recalculation depends on the loan terms and servicer.

Why does my Excel payoff date differ from my statement?
Possible causes include payment posting dates, daily rather than monthly interest calculations, rounding, fees, a variable rate, or how additional payments are allocated. Compare the spreadsheet assumptions with the loan statement and ask the servicer how it applies extra funds.

Is this calculator’s output a prepayment schedule?
No. It calculates a regular fixed-payment estimate and total interest. A true prepayment schedule needs an extra-payment amount and period-by-period balance calculations, which the calculator block does not take as inputs.

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

Related

• Amortization Table With Balloon Payment Calculator
• Amortization Schedule With Extra Payments
• Amortization Table Extra Principal Payment Calculator

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