calculate-loan

01 Loan payments

Loan Payoff Calculator Extra Payment Excel

Calculator No. 01-26501 · Updated · Formula and assumptions shown below

The calculator on this page is switched off while it is checked. The formula is below, and the worked example shows how to do the sum by hand.

Estimate for education, not financial advice. The result follows from the numbers you enter and the formula shown. It is not a quote or an offer, and it does not include fees, taxes or insurance unless the calculator asks for them. Ask your lender for the exact figures.

This calculator works out how much faster you can pay off a loan when you add extra payments, and how much interest you save. You enter your loan amount, interest rate, term, and an extra monthly amount; you get back your new payoff time, total interest paid, and total savings. The math is the same as what you would build in Excel, so you can use this page to check your own spreadsheet.

Formula

The calculator uses the standard amortization formula for a fixed-rate loan. The regular monthly payment P is:

P = L × (r / (1 − (1 + r)^(−n)))

where:

  • L = loan amount (in $)
  • r = monthly interest rate = APR / 1200 (APR in % per year)
  • n = original number of monthly payments = term in years × 12

When you add an extra amount E each month, your total monthly payment becomes P + E. The number of months m needed to pay off the loan is found by solving the balance equation for m:

m = ⌈ −ln(1 − r × L / (P + E)) / ln(1 + r) ⌉

This gives the smallest whole number of months such that the balance reaches zero. The total interest paid is (P + E) × m − L, and the interest saved is the original total interest minus the new total interest.

Worked Example

Using the defaults: $20,000 loan, 8% APR, 5-year term, and $100 extra per month.

  1. Monthly rate r = 8 / 1200 = 0.0066667
  2. Original payment P = 20000 × (0.0066667 / (1 − (1.0066667)^(−60))) = $405.53 (rounded to cents)
  3. Total monthly payment = 405.53 + 100 = $505.53
  4. Months to payoff m = ⌈ −ln(1 − 0.0066667 × 20000 / 505.53) / ln(1.0066667) ⌉ = ⌈ 47.02 ⌉ = 48 months
  5. Total interest with extra = 505.53 × 48 − 20000 = $4,265.44
  6. Original total interest = 405.53 × 60 − 20000 = $4,331.80
  7. Interest saved = 4,331.80 − 4,265.44 = $66.36

The calculator shows 48 months, $4,265.44 total interest, and $66.36 saved. Note that the interest saved is small here because the extra payment is only $100 on a 5-year loan; the effect grows with longer terms and larger extra payments.

Assumptions

  • Fixed interest rate for the entire loan term (no rate changes).
  • Payments are made at the end of each month (ordinary annuity).
  • Interest compounds monthly at APR / 12.
  • Extra payments are made every month along with the regular payment, not as a lump sum.
  • No fees, prepayment penalties, taxes, or insurance are included.

How to Build This in Excel

If you want to replicate this in a spreadsheet, you can use the built-in financial functions. For a loan with amount in B1, APR in B2 (as a decimal, e.g., 0.08), term in years in B3, and extra payment in B4:

  • Monthly rate: =B2/12 (if B2 is 8%, use =8%/12)
  • Original payment: =PMT(B2/12, B3*12, -B1)
  • Months to payoff with extra: =NPER(B2/12, -(PMT(B2/12, B3*12, -B1)+B4), B1) – this returns a fractional value; round up with =CEILING(NPER(...),1)
  • Total interest with extra: =(PMT(...)+B4)*CEILING(NPER(...),1)-B1

Excel’s NPER function solves the same equation as the calculator. The key is to use the total payment (original + extra) as the payment argument. If you get a #NUM! error, it means the extra payment is not enough to cover the monthly interest – the loan would never be paid off.

Common Mistakes and Misconceptions

  • Using the wrong rate: Always divide the annual APR by 12 for monthly calculations. If you use 8 instead of 0.08, the payment will be wildly wrong.
  • Rounding too early: In Excel, if you round the payment to cents before using NPER, the result may be off by a month. Use full precision in formulas, then round the final month count up.
  • Assuming extra payments reduce the monthly payment: Here, extra payments shorten the term, not lower the payment. If you want to lower the payment, you would recast the loan, which is a different calculation.
  • Thinking interest saved is the extra payment times the remaining months: That ignores the time value of money. The actual saving is the difference in total interest, which is smaller than the simple product.

When to Use This Calculator

Use this tool when you have a fixed-rate loan (auto, personal, student, or mortgage) and you are considering adding a fixed amount to your monthly payment. It helps you see the trade-off: paying extra reduces the term and interest, but it also ties up cash that could be used elsewhere. The calculator is also useful for comparing a lump-sum extra payment versus monthly extras – though this version only handles monthly extras.

For loans with variable rates, the result is only an estimate because future rate changes are unknown. For loans with prepayment penalties, the savings might be offset by fees – check your loan contract. The calculator assumes the extra payment is applied directly to the principal, which is how most lenders handle it unless you specify otherwise.

Limits of the Calculation

This model assumes you make the same extra payment every month without fail. If you make irregular extra payments, the result will differ. It also assumes the interest rate never changes, which is not true for adjustable-rate loans. The formula breaks down if the extra payment is zero – then it just returns the original term. If the extra payment is too small to cover the monthly interest (i.e., P + E is less than the interest for the first month), the logarithm becomes undefined and the calculator shows “Cannot compute” – this is correct because the loan would never be paid off.

Finally, the calculator ignores the effect of rounding each monthly payment to the nearest cent. In reality, your lender will round the payment, which can shift the payoff month by one or two. The estimate is accurate to within a month.

Frequently Asked Questions

How do I add extra payments in Excel?

Use the NPER function with the total payment (regular + extra) as the payment argument. For example, =NPER(rate, -(PMT(rate, nper, -pv)+extra), pv) gives the number of periods. Round up to the next whole month with CEILING.

Is it better to make extra payments monthly or as a lump sum?

Mathematically, a lump sum applied early saves more interest than the same total amount spread over many months, because it reduces the principal sooner. However, monthly extras are easier to budget. This calculator only models monthly extras; for a lump sum, you would need a different calculation.

Does an extra payment reduce my monthly payment or shorten the term?

That depends on how the lender applies it. By default, extra payments shorten the term while keeping the monthly payment the same. Some lenders allow “recasting,” which lowers the monthly payment but keeps the term. This calculator assumes the term shortens.

What if my extra payment is not enough to cover the interest?

If your total monthly payment (regular + extra) is less than the interest that accrues in the first month, the loan balance will grow, not shrink. The calculator will show “Cannot compute” because the loan would never be paid off. In that case, you need to increase the extra payment.

Why does the interest saved seem so small?

Interest savings depend on the loan amount, rate, term, and extra amount. On a short, low-rate loan, the savings are modest. On a 30-year mortgage with a high rate, even $50 extra per month can save thousands. Try changing the term to 30 years in the calculator to see the effect.

Can I use this for a bi-weekly payment plan?

Bi-weekly payments are equivalent to making 13 full payments per year, which is like an extra monthly payment each year. To model it here, divide your annual extra amount by 12 and enter that as the extra payment. The result is close but not exact because of timing differences.

Remember, this calculator gives an estimate based on standard assumptions. Your lender’s amortization schedule and disclosure statement will show the exact figures for your specific loan.

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 payment per month | $ | 100 | 0 | 1000000
out months | New payoff time | months | ceil(round(-ln(1 - apr / 1200 * p / (p * (apr / 1200) / (1 - (1 + apr / 1200) ^ -(yrs * 12)) + extra)) / ln(1 + apr / 1200), 6)) | 0
out int | Total interest with extra | $ | p * (apr / 1200) / (1 - (1 + apr / 1200) ^ -(yrs * 12)) * months + extra * months - p | 2
out save | Interest saved | $ | (p * (apr / 1200) / (1 - (1 + apr / 1200) ^ -(yrs * 12)) * yrs * 12 - p) - (p * (apr / 1200) / (1 - (1 + apr / 1200) ^ -(yrs * 12)) * months + extra * months - 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

The whole Loan payments section →