calculate-loan

01 Loan payments

Loan Payoff Spreadsheet Calculator and Formula

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

Monthly payment $405.53
Total interest
$4,331.67
= Monthly payment × loan term × 12 − loan amount
Loan amount
$20,000
Input
Interest rate (APR)
8 %
Input
You enter
Loan amount ($), Interest rate (APR) (%), Loan term (years)
You get
Monthly payment ($), Total interest ($)
Rounding
2 dp

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.

A loan payoff spreadsheet estimates the monthly payment, total interest, and payoff schedule for a fixed-rate loan when you enter its balance, annual percentage rate (APR), and remaining term. This calculator returns the level monthly payment and total interest; a spreadsheet can extend the same arithmetic into a month-by-month balance table.

Formula

The calculator uses the standard fixed-payment amortization formula. It first converts annual APR to a monthly rate and years to a number of monthly payments:

  • r = APR / 1200, the monthly interest rate as a decimal.
  • n = years × 12, the number of monthly payments.
  • Payment = P × r / [1 − (1 + r)−n], rounded to the nearest cent.
  • Total interest = Payment × n − P.

Here, P is the starting loan principal in dollars, APR is the annual percentage rate entered as a percentage, r is the monthly rate, and n is the term in months. The payment formula assumes the same payment is made once per month and the rate does not change. Total interest is calculated from the rounded monthly payment, so actual lender totals can differ slightly because of payment dates, rounding, and contract details.

Worked Example

Using the calculator’s defaults—$20,000 principal, 8% APR, and a five-year term—the monthly rate is 8 ÷ 1,200 = 0.0066667, and the term is 5 × 12 = 60 payments.

Payment = 20,000 × 0.0066667 ÷ [1 − (1 + 0.0066667)−60] = $405.53 per month when rounded to cents.

Total interest = $405.53 × 60 − $20,000 = $4,331.80. Total scheduled payments are $24,331.80. The calculation is an estimate based on the stated inputs and assumptions, not a lender payoff quote.

Assumptions

  • The interest rate is fixed for the full term.
  • Payments are equal, made monthly, and applied at regular intervals.
  • Interest is modelled monthly at APR divided by 12.
  • Fees, taxes, insurance, PMI, escrow, late charges, and other account adjustments are not included.

How a Loan Payoff Spreadsheet Works

A payoff spreadsheet repeats the monthly calculation down a table. For each period, it applies interest to the outstanding balance, subtracts the payment, and carries the resulting balance into the next row. A basic layout might have columns for payment number, opening balance, interest, payment, principal repaid, and closing balance.

  • Interest for the month = opening balance × monthly rate.
  • Principal repaid = payment − interest for the month.
  • Closing balance = opening balance − principal repaid.

For example, at the start of the default loan, monthly interest is about $133.33 ($20,000 × 0.0066667). From the $405.53 payment, about $272.20 goes toward principal. As the balance declines, the interest portion generally falls and the principal portion rises. Spreadsheet formulas should reference the prior row’s closing balance so each month builds on the one before it.

Using the Estimate for a Payoff Plan

To model an existing loan, use the current principal balance and remaining term—not necessarily the amount originally borrowed and original term. Check the loan statement or ask the servicer for the current balance, APR, required payment, and remaining schedule. If the rate varies, a single fixed-rate calculation cannot represent future changes precisely.

A spreadsheet can also show the effect of extra principal payments, but those payments need to be entered in the month they would actually be made. Apply an extra amount to principal only if that matches how the loan is serviced. An extra-payment model should stop at a zero balance and reduce the final payment as needed; otherwise it may show an impossible negative balance or overstate total payments. Confirm any payment-allocation or payoff details with the servicer.

Common Spreadsheet Problems and Limits

  • Using the wrong balance: Original principal is not a substitute for today’s outstanding principal when estimating the remaining payoff.
  • Confusing APR and monthly rate: An 8% annual rate is entered as 8, then divided by 1,200 to get the monthly decimal rate. Entering 0.08 as though the input were a percentage produces a different result.
  • Rounding every intermediate value: Rounding interest and balances to cents every row can create small differences from a lender’s system. Keep extra precision in formulas and format displayed values as currency.
  • Assuming the final payment is identical: A real final payment may be adjusted for accrued interest, timing, or rounding.
  • Leaving out contract details: Variable rates, fees, skipped or late payments, and irregular payment dates are outside this simple model.

A payoff quote is a dated amount calculated for a particular loan account. It may include interest accrued since the last payment and other contract-specific adjustments, so a spreadsheet balance should not be treated as the amount needed to close the account on a chosen date.

Frequently Asked Questions

What formula should I use in a loan payoff spreadsheet?
For a fixed-rate loan with equal monthly payments, use the amortization payment formula shown above. For a schedule, calculate each month’s interest from its opening balance, subtract the payment, and carry the new balance forward.

Can a spreadsheet show when my loan will be paid off?
Yes, if it includes the current balance, rate, payment, and any extra principal payments by date. The result is a model; verify the payoff date and amount with the servicer.

Why does my spreadsheet differ from my loan statement?
Common causes include a different balance or APR, payment timing, daily rather than simplified monthly accrual, rounding, fees, or an adjusted final payment. Compare the assumptions and use the account’s actual figures.

Does the calculator include extra payments?
No. It estimates a regular level payment and total interest for the entered term. An extra-payment spreadsheet requires additional entries and a month-by-month recalculation of the remaining balance.

Is the spreadsheet’s balance the payoff amount?
Not necessarily. The amount due on a specific date can differ because interest accrues between payments and the account may include other adjustments. Request a dated payoff figure from the servicer.

This result is an estimate for educational purposes, 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
out pmt | Monthly payment | $ | p * (apr / 1200) / (1 - (1 + apr / 1200) ^ -(yrs * 12)) | 2
out int | Total interest | $ | pmt * yrs * 12 - 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 →