calculate-loan

01 Loan payments

Loan Payoff Formula in Excel: How to Use It

Calculator No. 01-26545 · 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.

You can calculate a loan payoff amount in Excel using the formula =FV(rate, nper, pmt, pv), where the result is the remaining balance after a certain number of payments. This article explains the formula, shows a worked example, and provides a free calculator to compute your payoff amount directly.

Formula

The Excel formula for the payoff balance after k payments is:

Balance = P × (1 + r)^k − PMT × [((1 + r)^k − 1) / r]

Where:

  • P = original loan amount (present value), in dollars
  • r = monthly interest rate = annual rate (APR) divided by 12, as a decimal (e.g., 8% APR → 0.08/12 = 0.0066667)
  • k = number of payments already made (or the number of periods after which you want the balance)
  • PMT = level monthly payment, in dollars

In Excel, the built-in FV function does this calculation for you: =FV(r, k, PMT, -P). The negative sign on P reflects that the loan is money you received (cash inflow).

Worked Example

Using the calculator defaults: loan amount $20,000, APR 8%, term 5 years (60 months).

  1. Monthly rate: r = 8 / 1200 = 0.0066667
  2. Number of payments: n = 5 × 12 = 60
  3. Monthly payment: PMT = 20000 × 0.0066667 / (1 − (1 + 0.0066667)^−60) = $405.53 (rounded)
  4. Total interest: $405.53 × 60 − $20,000 = $4,331.80

So the monthly payment is $405.53 and the total interest over the life of the loan is $4,331.80.

Assumptions

  • Fixed annual interest rate (APR) that does not change.
  • Payments are made at the end of each month (ordinary annuity).
  • Interest compounds monthly at APR/12.
  • No fees, taxes, insurance, or other charges are included.

How to Use the Payoff Formula in Excel

To find the payoff amount after a specific number of payments, you can use the FV function directly in a spreadsheet. For example, to find the balance after 12 payments on the loan above:

  1. Enter the monthly rate in a cell (e.g., A1 = 0.08/12).
  2. Enter the number of payments made (e.g., A2 = 12).
  3. Enter the monthly payment (e.g., A3 = 405.53).
  4. Enter the original loan amount as a negative number (e.g., A4 = -20000).
  5. Type =FV(A1, A2, A3, A4) and press Enter.

The result is the remaining balance, which is the payoff amount if you pay it immediately after that payment. Note that the payoff amount may be slightly different if your lender charges a prepayment penalty or calculates interest daily; the formula gives the exact remaining principal plus accrued interest under monthly compounding.

Common Mistakes and Misconceptions

  • Using the annual rate instead of the monthly rate. Always divide the APR by 12 before using it in the formula.
  • Forgetting to round the payment. In real loans, payments are rounded to the cent, which can cause a tiny difference in the final balance.
  • Assuming the payoff amount equals the remaining principal. The payoff includes accrued interest since the last payment, so it is usually a bit higher than the principal balance shown on a statement.
  • Using the formula for variable-rate loans. The formula assumes a fixed rate; if your rate changes, you need to recalculate with the new rate for the remaining periods.

Why Use a Payoff Calculator Instead of Excel?

While Excel is powerful, a dedicated payoff calculator simplifies the process: you enter the loan amount, rate, and term, and it instantly shows the monthly payment and total interest. It also lets you see how extra payments affect the payoff date without building complex spreadsheets. The calculator above uses the same formula as Excel, so you can trust the numbers.

Frequently Asked Questions

How do I calculate the payoff amount in Excel with extra payments?

If you make extra payments, you need to adjust the payment amount in the FV function. For example, if your regular payment is $405.53 and you pay an extra $100 each month, use $505.53 as the PMT argument. The FV function will then return the balance after k payments with that higher payment, which will be lower than the original schedule.

What is the difference between payoff balance and principal balance?

The principal balance is the remaining amount you borrowed, without interest. The payoff balance includes the interest that has accrued since your last payment. For example, if your last payment was on the 1st and you request a payoff on the 15th, the payoff includes 15 days of interest. The formula above gives the balance immediately after a payment, so it equals the principal balance plus that month’s interest already paid.

Why does my lender’s payoff quote differ from my Excel calculation?

Lenders often calculate interest daily (simple interest) rather than monthly compounding. They may also add fees for processing the payoff or a prepayment penalty. Always ask for a written payoff statement from your lender to get the exact amount.

Can I use the FV function for a loan with a balloon payment?

Yes, but you need to adjust the formula. A balloon loan has a large final payment. You can use the FV function to find the balance at the end of the term if you know the payment amount. Alternatively, use the PV function to find the loan amount given a balloon payment.

Does the formula work for biweekly payments?

Yes, but you must adjust the rate and number of periods. For biweekly payments, divide the annual rate by 26 (or 26.071 for exact biweekly), and multiply the number of years by 26. The payment amount would be half of a monthly payment if you pay every two weeks. The formula remains the same.

The result from this calculator is an estimate based on the inputs and assumptions. Your lender’s disclosure will provide the exact figures for your 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
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 →