A debt repayment schedule in Excel is a month-by-month table showing each payment, the amount that goes to interest and principal, and the balance left. Enter the opening balance, annual interest rate, payment amount, and any extra payments to calculate the schedule and estimated payoff date; the example below uses a standard fixed-rate loan formula.
Formula
This calculator estimates the number of equal monthly payments needed to repay a balance, then multiplies that count by the monthly payment including any regular extra payment. The payoff-month formula is ceil(round(-ln(1 - r × B / M) / ln(1 + r), 6)), where r = APR / 1200 and M = payment + extra payment.
- B is the starting balance in dollars.
- APR is the annual percentage rate entered as a percentage, such as 8 for 8%.
- r is the monthly interest rate as a decimal.
- M is the total amount paid each month in dollars.
- Months is rounded up to a whole payment period.
- Total paid is monthly payment multiplied by estimated months; estimated interest is total paid minus starting balance.
The expression applies the standard amortization relationship for a fixed balance, fixed periodic rate, and level payment. If the payment does not cover the month’s interest, the logarithm has no valid real result, so the calculator cannot produce a payoff estimate. A real final payment is often smaller than the regular amount; this simplified total therefore may overstate the amount actually paid in the last month.
Worked Example
Using the calculator’s default values: balance $20,000, APR 8%, monthly payment $405.53, and extra payment $0.
- Convert the APR to a monthly decimal rate:
r = 8 / 1200 = 0.0066667, or about 0.66667% per month. - Use the total monthly payment,
M = $405.53 + $0 = $405.53. - Substitute into the payoff formula:
ceil(round(-ln(1 - 0.0066667 × 20000 / 405.53) / ln(1.0066667), 6)), which gives 60 months. - Total paid estimate:
$405.53 × 60 = $24,331.80. - Estimated interest:
$24,331.80 − $20,000 = $4,331.80.
These outputs are internally consistent with the entered payment: the calculator assumes that $405.53 is paid for all 60 months. In a month-by-month lender schedule, the last payment may be adjusted to clear the remaining balance rather than collecting a full scheduled payment.
Assumptions
- The interest rate stays fixed for the estimate.
- Payments are made monthly at the end of each period, after that period’s interest accrues.
- Interest compounds monthly at APR divided by 12.
- No fees, taxes, insurance, PMI, escrow, penalties, or changes in payment are included.
- The extra payment is the same each month and is applied to the balance. Actual payment allocation depends on the loan terms and servicing.
How to Build a Schedule in Excel
A spreadsheet schedule makes the balance changes visible instead of showing only a payoff estimate. Create columns for payment number, beginning balance, scheduled payment, extra payment, interest, principal paid, and ending balance. Put the original balance in the first beginning-balance cell, and use one row per payment period.
- Enter the starting balance, APR, and regular monthly payment in clearly labelled cells. Keep rates and amounts in separate cells so formulas can reference them.
- Calculate interest for each row as the beginning balance multiplied by the monthly rate. For example, if APR is in cell B2 and beginning balance is in B6, a formula is
=B6*$B$2/12when B2 is stored as a percentage (8%). - Calculate principal paid as the scheduled payment plus extra payment minus that row’s interest. The ending balance is beginning balance minus principal paid.
- For the next row, set beginning balance equal to the prior row’s ending balance. Copy formulas down until the balance reaches zero, and check that the final payment does not exceed the remaining balance plus interest.
Excel’s cell formatting matters: if an APR is entered as 8 rather than 8%, divide it by 1200 for a monthly decimal rate; if entered as 8%, divide by 12. Mixing those approaches can produce a result off by a factor of 100. Round displayed currency to cents, but avoid rounding the monthly interest formula prematurely because repeated rounding can create small differences from a servicer’s figures.
Reading the Schedule and Handling Extra Payments
In a typical amortizing schedule, interest is calculated on the outstanding balance. Early payments therefore devote a larger share to interest; as the balance declines, the interest portion generally falls and more of a level payment goes to principal. This does not mean that interest is charged only at the beginning: it is calculated for each period on the balance then outstanding.
An extra payment can reduce later interest if it is applied to principal, but the schedule needs to model when it is paid and how the loan applies it. An occasional one-time extra payment is not represented accurately by a constant monthly extra-payment input. Add a separate extra-payment amount on the specific row in Excel instead. Check the agreement or ask the loan servicer how additional amounts are credited; do not assume an extra amount automatically changes the required scheduled payment.
Common Excel Errors and Limits
- Wrong rate unit: APR entered as 8% is 0.08 in a spreadsheet, while 8 means eight whole units. Use a consistent convention.
- Payment below interest: if the monthly payment is no more than the period’s interest, the balance will not amortize as assumed.
- Balance below zero: a fixed payment copied into the last row can overpay. Cap the final payment at beginning balance plus that month’s interest.
- Unmodelled changes: variable rates, skipped payments, fees, payment timing differences, and irregular extra payments require additional rows or assumptions.
- Rounded inputs: a displayed payment rounded to cents may differ from a lender’s unrounded internal calculation or its exact daily-interest method.
This calculator is a fixed-rate estimate, not a replacement for a lender’s official amortization table. Its total-paid figure assumes the same payment throughout, including the final period, and therefore should be read as an estimate rather than an exact settlement amount.
Frequently Asked Questions
How do I make a debt repayment schedule in Excel?
Set up one row per month with beginning balance, payment, interest, principal, and ending balance. Calculate interest from the beginning balance and monthly rate, then carry the ending balance into the next row.
What formula calculates monthly loan interest in Excel?
Multiply the period’s beginning balance by the monthly rate. For an APR entered as a percentage, a common formula is =beginning_balance*APR/12; for APR entered as a whole number such as 8, use =beginning_balance*APR/1200.
Does an extra payment reduce interest?
It can reduce later interest when the lender applies it to principal, because subsequent interest is calculated on a lower balance. The exact effect depends on payment timing and the loan’s terms.
Why does my Excel payoff date differ from the lender’s?
Differences can result from daily rather than monthly interest, payment posting dates, rounding, fees, rate changes, or how extra amounts are applied. Compare the assumptions and use the lender’s schedule for its contractual figures.
What if my payment is less than the interest due?
The balance will not decline under the fixed-payment assumptions and may grow if unpaid interest is added. This calculator cannot estimate a payoff period when the payment does not cover accrued interest.
The result is an estimate for educational arithmetic; the lender’s disclosure gives the exact figures.
Related
Formula used
in bal | Starting balance | $ | 20000 | 0 | 100000000 in apr | Interest rate (APR) | % | 8 | 0.01 | 100 in pmt | Monthly payment | $ | 405.53 | 0.01 | 100000000 in extra | Extra payment each month | $ | 0 | 0 | 100000000 out months | Estimated payoff months | months | ceil(round(-ln(1 - (apr / 1200) * bal / (pmt + extra)) / ln(1 + apr / 1200), 6)) | 0 out total | Total paid | $ | (pmt + extra) * months | 2 out interest | Estimated interest paid | $ | total - bal | 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
- Avalanche Game Schedule: Dates, Times and Where to Check
- Avalanche Hockey Schedule: Games, Dates and TV
- Avalanche Debt Payoff Tracker Calculator
- Avalanche Debt Repayment Calculator and Payoff Plan
- Avalanche Debt Payoff Calculator and Spreadsheet
- Avalanche Debt Payoff Calculator: Free Estimate