A prepayment schedule in Excel estimates how extra payments change a loan’s balance, payoff time, and total interest. Enter the loan amount, APR, term, and monthly extra payment to see the scheduled payment, revised payoff period, and estimated interest savings; the calculation assumes the extra amount is paid with each monthly installment.
Formula
This calculator estimates a regular level-payment loan first, then treats the extra monthly amount as part of each payment until the balance is repaid.
- Scheduled payment: PMT = round[P × r ÷ (1 − (1 + r)−n), 2].
- Payoff months with extra: N = ceil{round[−ln(1 − rP ÷ M) ÷ ln(1 + r), 6]}, where M = PMT + extra.
- Interest saved: interest under the original schedule minus estimated interest with extra payments.
P is the starting principal in dollars; APR is the annual percentage rate entered as a percent; r is the monthly rate, APR ÷ 1200; n is the original term in months, years × 12; PMT is the scheduled monthly payment rounded to cents; extra is the additional monthly payment; and M is the combined monthly payment. The payoff formula assumes the combined payment covers monthly interest. The total-interest estimate uses the rounded monthly payment multiplied by the estimated number of payments, so its final payment is treated as a full payment; real loan schedules usually adjust the final payment to the remaining amount due.
Worked Example
Using the defaults above: principal $20,000, APR 8%, term 5 years, and extra payment $100 per month.
- Monthly rate: r = 8 ÷ 1200 = 0.0066667. Original term: n = 5 × 12 = 60 months.
- Scheduled payment: round[$20,000 × 0.0066667 ÷ (1 − (1.0066667)−60), 2] = $405.53.
- Combined payment: M = $405.53 + $100 = $505.53.
- Estimated payoff time: ceil{round[−ln(1 − 0.0066667 × 20,000 ÷ 505.53) ÷ ln(1.0066667), 6]} = 47 months.
- Original scheduled interest: $405.53 × 60 − $20,000 = $4,331.80. Estimated interest with extra: $505.53 × 47 − $20,000 = $3,760.91. Estimated difference: $570.89.
Assumptions
- The interest rate stays fixed for the full calculation.
- Payments are made monthly, at the end of each monthly period; the extra amount is included in that payment.
- Interest compounds monthly at APR ÷ 12.
- Fees, taxes, insurance, PMI, escrow, late charges, and lender-specific payment allocation are not included.
How a Prepayment Schedule Works in Excel
A detailed spreadsheet models every payment period in its own row. It starts with the opening balance, calculates interest for the period, applies the scheduled payment and extra principal, and carries the resulting balance into the next row. This is more informative than a single payoff estimate because it shows the balance declining over time and makes timing assumptions visible.
A typical monthly schedule has columns for period number, opening balance, scheduled payment, extra payment, interest, principal paid, and closing balance. In a conventional monthly model:
- Interest = opening balance × APR ÷ 12.
- Principal paid = payment applied to the loan − interest.
- Closing balance = opening balance − principal paid.
- Next row’s opening balance = previous row’s closing balance.
Excel formulas can reference the previous row’s closing balance, the fixed APR, and payment inputs. If an extra payment is intended to reduce principal, calculate interest first and subtract the extra from the balance after the scheduled payment’s interest is covered. A schedule should stop or cap the final payment when the remaining balance is smaller than the planned payment; otherwise it can show a negative balance or overstate the final amount.
Building and Checking the Workbook
Put the loan assumptions in clearly labelled cells and use absolute references for them in the schedule. For example, a monthly rate cell can contain APR divided by 12, with APR entered as a decimal percentage or converted from a whole-number percentage consistently. Excel’s PMT function can calculate the scheduled payment, but check its sign convention: Excel commonly returns a negative cash flow when the principal is entered as positive.
For a transparent schedule, calculate the first month manually and compare it with the spreadsheet. With a $20,000 balance and 8% APR, first-month interest is about $133.33. From the example’s $405.53 scheduled payment, about $272.20 goes to principal before any extra payment. Adding $100 directly to principal makes the next balance about $19,627.80, subject to rounding and the lender’s actual payment processing.
Useful spreadsheet checks include confirming that the balance declines under a payment that exceeds interest, total principal paid matches the starting principal at payoff, and total payments less principal equal total interest. Keep unrounded amounts within formulas if practical, but round displayed currency to cents. Rounding every intermediate cell can cause small accumulated differences.
What the Estimate Does—and Does Not—Show
“Prepayment” can mean different things. Here it means voluntarily paying extra toward principal on a monthly basis. A one-time lump sum, irregular extra payments, or paying an installment earlier in the month needs a different schedule or additional inputs. The calculator’s monthly-extra assumption should not be read as a lender’s exact payoff quote.
The formula assumes a fixed rate and level scheduled payment. It does not model variable rates, daily interest accrual, payment holidays, skipped payments, fees, or a lender’s rules for how excess funds are allocated. Some servicers may require instructions for applying excess amounts to principal. Check the loan documents or contact the servicer to confirm how a particular payment will be handled and whether any charge applies.
One common misconception is that an extra payment reduces the scheduled installment automatically. In many amortizing-loan arrangements, extra principal reduces the balance and can shorten the payoff period, while the regular required payment remains unchanged. This estimate represents that pattern; it does not calculate a recast or re-amortization, which changes the required payment based on a new balance and lender-specific terms.
Frequently Asked Questions
How do I make a prepayment schedule in Excel?
Set up inputs for balance, APR, term, and extra payment, then create one row per month. Calculate interest from the opening balance, subtract the amount applied to principal, and carry the closing balance into the next row. Limit the last payment to the remaining amount due.
Does an extra payment go directly to principal?
Not necessarily. The schedule assumes the extra amount reduces principal after interest due for the period is covered. Actual allocation depends on the loan terms and payment instructions, so confirm the process with the servicer.
How much interest will I save by paying extra?
The result depends on principal, rate, payment timing, extra amount, and the loan’s calculation rules. This calculator gives an estimate under fixed-rate monthly assumptions; a row-by-row schedule can account for irregular payments if those amounts and dates are entered.
Why does my Excel balance become negative?
A formula may keep subtracting the full planned payment after the remaining balance is smaller, or it may use inconsistent signs or rounding. Cap the final payment at the amount needed to bring the balance to zero, and check that interest is calculated before principal is applied.
Can this calculate a one-time lump-sum prepayment?
Not directly. This calculator assumes the same extra amount is paid every month. To model a lump sum, add it in the specific month’s row in a schedule and recalculate each later balance.
This result is an estimate for education, and the lender’s disclosure gives the exact figures.
Related
Formula used
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 monthly payment | $ | 100 | 0 | 100000000 out pmt | Scheduled monthly payment | $ | round(p * (apr / 1200) / (1 - (1 + apr / 1200) ^ -(yrs * 12)), 2) | 2 out total | Estimated payoff months with extra | months | ceil(round(-ln(1 - (apr / 1200) * p / (pmt + extra)) / ln(1 + apr / 1200), 6)) | 0 out saved | Estimated interest saved | $ | (pmt * yrs * 12 - p) - ((pmt + extra) * total - p) | 2
Each line is one input (in, sel, list) or one result (out: label, unit, formula, decimals). How the calculators work
Method: the standard loan formula for this calculation, fixed rate and monthly compounding unless stated, rounded as each result states. Not financial advice; no lender, rate or product is recommended. See formulas and assumptions, the disclaimer or report an error.
More in this section
- June Balance Date: What It Means for Loan Balances$405.53
- L&T Finance Gold Loan: How It Works and What to Check
- Journal Entry to Correct a Beginning Balance
- JS Bank Gold Loan: Eligibility, Process and Costs
- Journal Entry for a Bank Loan: Debit and Credit$20,000.00
- Journal Entry to Adjust Beginning Balance