Calculate Loan Payoff Amount in Excel

To calculate a loan payoff amount in Excel, use the remaining balance formula with the loan’s original amount, monthly interest rate, payment, and number of payments already made. The calculator below estimates the balance after scheduled monthly payments; it does not include a lender’s payoff fees, accrued daily interest, or other account adjustments.

Formula

The estimated balance after k regular payments is B = P(1 + r)^k − M[((1 + r)^k − 1) / r]. Here, B is the remaining principal in dollars; P is the original loan principal in dollars; r is the monthly interest rate as a decimal; k is the number of completed monthly payments; and M is the monthly payment in dollars. The rate conversion is r = APR / 1,200 when APR is entered as a percentage per year.

The payment used in the balance calculation is M = P × r / [1 − (1 + r)⁻ⁿ], where n is the total scheduled number of monthly payments. It is rounded to cents, as a fixed monthly payment normally is. The calculator uses the unrounded mathematical balance formula with that rounded payment, then displays the balance to cents.

Worked Example

Using the calculator’s defaults: P = $20,000, APR = 8%, term = 5 years, and k = 12 payments. The term is 5 × 12 = 60 months, and the monthly rate is 8 / 1,200 = 0.0066667.

1. Calculate the monthly payment: $20,000 × 0.0066667 / [1 − (1.0066667)⁻⁶⁰] = approximately $405.53 after rounding to cents.
2. Calculate the growth factor after 12 payments: (1.0066667)¹² ≈ 1.082999.
3. Apply the balance formula: $20,000 × 1.082999 − $405.53 × [(1.082999 − 1) / 0.0066667] ≈ $16,489.10.
4. Payments made total $405.53 × 12 = $4,866.36. This is not the same as principal repaid: part of each payment covers interest.

Because the display rounds the balance to two decimal places, small differences may occur if you carry fewer digits in intermediate Excel calculations. The shown result for these defaults is an estimated balance of $16,489.10.

Assumptions

• The interest rate stays fixed for the full term.
• Payments are level, made monthly, and applied at the end of each monthly period.
• Interest compounds monthly at APR divided by 12.
• No fees, taxes, insurance, PMI, escrow, extra payments, missed payments, or lender-specific adjustments are included.

How to Calculate It in Excel

For a spreadsheet, enter the original principal in B1, APR as a percentage in B2, term in years in B3, and completed payments in B4. A formula for the estimated balance is:

=B1*(1+B2/1200)^B4-(B1*(B2/1200)/(1-(1+B2/1200)^(-B3*12)))*(((1+B2/1200)^B4-1)/(B2/1200))

This version expects APR as a number such as 8 for 8%, not as an Excel percentage value of 8%. If B2 is formatted as a percentage and contains 8%, use B2/12 as the monthly rate instead of B2/1200. Applying the wrong conversion can produce a dramatically incorrect balance.

You can calculate the scheduled payment separately with =-PMT(B2/1200,B3*12,B1), provided B2 contains 8 for 8%. The minus sign makes the payment display as a positive amount. For the balance formula, the payment should be the actual regular payment rounded to cents; use the payment shown on your statement if it differs from the formula’s result.

What the Payoff Estimate Means

The balance formula estimates principal remaining immediately after the specified number of payments. It is useful for checking amortization progress or making a rough projection, but it is not necessarily the amount required to close the loan on a particular date. A lender’s payoff quote may include interest accrued since the last payment, charges, or credits that are not represented here.

Payment timing matters. The formula assumes each payment is made as scheduled and that a full monthly period elapses between payments. It does not calculate daily interest for an off-cycle payoff date. If payments were late, skipped, increased, or applied differently, the actual balance can diverge from this estimate.

Common Excel Errors and Limits

• Confusing percent formats: Decide whether APR is entered as 8 or 8%. Use the matching conversion consistently.
• Using the wrong payment count: The input is the number of completed payments, not the number remaining. For a 60-month loan after 12 payments, enter 12.
• Rounding too early: Round the scheduled payment to cents, but retain precision in intermediate calculations. Rounding the monthly rate or growth factor prematurely can shift the result.
• Including extra payments as ordinary payments: Extra principal payments change the amortization path. This calculator assumes only the level scheduled payment.
• Entering more payments than the term: Use a payment count from zero through the scheduled term. The estimate is not designed for payments made after the loan has been fully repaid.

For a true date-specific payoff amount, request a payoff quote from the loan servicer and confirm the date through which it is valid. Use the calculator as an arithmetic estimate, not as a substitute for the account’s posted figures.

Frequently Asked Questions

What is the Excel formula for a loan payoff balance?
Use =P*(1+r)^k-M*((1+r)^k-1)/r, where P is principal, r is the monthly rate, k is completed payments, and M is the monthly payment. Convert APR to a monthly decimal rate first.

Can I use Excel’s FV function?
Yes. With a fixed rate and equal end-of-month payments, =-FV(monthly_rate, payments_made, monthly_payment, original_principal) can return the estimated remaining balance, subject to consistent cash-flow signs and rate conventions. Check the result against the amortization formula.

Is the remaining balance the payoff amount?
Not always. A payoff quote can account for interest accrued through the requested date and account-specific charges or credits. The calculator estimates the scheduled balance only.

How do I account for extra payments?
A single standard balance formula cannot represent irregular extra principal payments accurately. Build an amortization table with one row per payment, calculate that period’s interest, subtract the actual payment, and carry the resulting balance forward.

Why does my Excel result differ from my loan statement?
Check the APR entry format, payment count, payment rounding, timing, and whether the loan has variable rates, fees, or extra or missed payments. The lender’s posted balance reflects the account’s actual transactions.

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

Related

• Calculate Loan Repayment: Monthly Payment and Interest
• EMI Calculator Loan Planner: Estimate Monthly Payments
• Loan Calculator Based on Payment Amount: How Long to Pay It Off

in p | Original loan amount | $ | 20000 | 1 | 100000000
in apr | Interest rate (APR) | % | 8 | 0.01 | 100
in yrs | Loan term | years | 5 | 0.0833 | 50
in k | Payments already made | - | 12 | 0 | 600
out bal | Estimated balance | $ | p * (1 + apr / 1200) ^ k - (p * (apr / 1200) / (1 - (1 + apr / 1200) ^ -(yrs * 12))) * ((1 + apr / 1200) ^ k - 1) / (apr / 1200) | 2
out pmt | Monthly payment | $ | p * (apr / 1200) / (1 - (1 + apr / 1200) ^ -(yrs * 12)) | 2
out paid | Payments made | $ | pmt * k | 2