Debt Payoff Formulas for Excel: A Practical Guide

Formulas & functions · Updated November 22, 2026

Excel can estimate a debt payment with =PMT(B2/12,B3,-B1), estimate how many monthly payments a balance will take with =NPER(B2/12,-B4,B1), and track each month’s interest and remaining balance in an amortization schedule. These formulas are useful planning tools, but the result is only an estimate unless the rate, payment timing, fees, and lender calculation rules match your actual account.

What debt payoff formulas calculate

A payoff worksheet generally answers three related questions: what payment would clear one loan by a target date, how long a planned payment will take, and how much interest accrues as the balance falls. Excel’s financial functions model regular payments at a constant interest rate. A separate schedule makes the month-by-month arithmetic visible and is easier to adapt when you make extra payments.

The examples below use monthly payments, a nominal annual percentage rate (APR), and a monthly rate calculated as APR divided by 12. That is a simplifying assumption, not a universal description of how lenders calculate interest. Credit cards and some other accounts may accrue interest daily, and fees, payment dates, promotional rates, or changing rates can alter the result. Check the account agreement or ask the lender for the applicable calculation method.

Calculate a fixed monthly payment with PMT

Set up a small input area in Excel for Microsoft 365 on Windows: enter the loan balance in B1, annual APR as a percentage in B2, and the number of monthly payments in B3. For an illustrative $10,000 balance at 12% APR over 24 months, B1 is 10000, B2 is 12%, and B3 is 24.

With principal in B1, annual APR in B2, and the term in months in B3, enter this formula in B4: =PMT(B2/12,B3,-B1). It returns a payment of approximately $470.73 per month. The negative sign before B1 makes the payment display as a positive cash outflow; without it, Excel follows its cash-flow sign convention and returns a negative payment. The estimate assumes the same monthly payment and rate throughout and does not include fees.

PMT answers “What regular payment amortizes this loan over this many periods?” It does not determine whether a lender will approve that payment, whether the account accepts extra payments, or the exact final amount after rounding. If the aim is to model a current loan, use the lender’s actual required payment and terms rather than replacing them with a target-term estimate.

Estimate the payoff time with NPER

NPER estimates the number of equal payment periods needed when the rate and payment stay constant. Suppose B1 contains the $10,000 balance, B2 contains the 12% annual APR, and B4 contains a planned payment of 500. With those cells and monthly payments assumed, enter =NPER(B2/12,-B4,B1). The result is about 22.43 months.

A fractional result is a mathematical estimate, not a promise that the lender will accept 22.43 payments. In practice, this example means 22 full payments of $500 followed by a smaller final payment during month 23, assuming monthly interest at 1% and no additional charges. After 22 payments, the balance is about $211; one more month of interest brings the final amount to roughly $213. The exact amount can differ because of payment dates and the lender’s interest method.

If the payment is too small to cover interest, the balance may not decline. NPER can return an error or fail to give a useful finite payoff estimate in that situation. Review the payment and rate inputs rather than treating an error as a spreadsheet malfunction.

Build an amortization schedule

A schedule shows why a payment does not all go toward principal. At the start of a loan, a larger share may pay interest; as principal falls, the interest portion usually falls too. For a clear example, keep B1 as $10,000 principal, B2 as 12% APR, and B4 as the $470.73 payment calculated by PMT. Make headings for period, beginning balance, interest, payment, principal paid, and ending balance in columns A through F. Use row 8 for the first month.

With B8 holding the first month’s beginning balance of 10000 and B2 holding the annual APR, enter this in C8 for interest, rounded to cents: =ROUND(B8*$B$2/12,2). It returns $100.00: $10,000 multiplied by 1% is $100. With B4 holding the planned payment and B8 and C8 holding the opening balance and interest, enter this in D8 to cap the payment at the amount owed: =MIN($B$4,B8+C8). It returns about $470.73 in this first month.

With D8 as the actual payment and C8 as interest, enter this in E8 for principal paid: =D8-C8. It returns about $370.73. With B8 as the beginning balance and E8 as principal paid, enter this in F8 for the ending balance: =B8-E8. It returns about $9,629.27. The arithmetic checks: $100.00 of interest plus $370.73 of principal equals the $470.73 payment, and $10,000 minus $370.73 leaves $9,629.27.

For the next month, with F8 as the prior month’s ending balance, enter this in B9: =F8. It returns $9,629.27. Copy the interest, payment, principal, and ending-balance formulas down the rows for later months; Excel adjusts row references while the dollar signs keep the APR and payment inputs fixed. The MIN function prevents the last payment from exceeding the balance plus that month’s interest. In an actual account, a lender may calculate the final amount differently, so treat this as a model rather than a payoff quote.

Model extra payments and multiple debts

For one loan, include any planned extra payment in the monthly payment input, but do not assume that every lender applies extra money in the way you expect. Some may require a payment instruction or apply funds to fees or accrued interest first. Confirm how extra payments are handled, and whether there is a prepayment charge, before relying on an accelerated schedule.

For several debts, make one row per account with its current balance, APR, minimum payment, and any relevant promotional-rate end date. Set a total monthly debt budget. Continue paying at least the required amount on every account, then assign the remaining budget to one selected target debt. When that debt is paid off, redirect its former payment to the next target. Your total budget can stay constant while the allocation changes.

Two common payoff orders are:

  • Avalanche: Direct extra money to the debt with the highest current interest rate first. If balances, rates, and payment timing are accurately modeled and the same total amount is paid, this generally reduces interest compared with targeting a lower-rate debt first.
  • Snowball: Direct extra money to the smallest balance first, regardless of rate. Paying an account off sooner can simplify the list and provide a visible milestone, although it can cost more interest than the avalanche order when a larger debt has a much higher rate.

For example, if three balances are $1,200 at 24%, $4,000 at 18%, and $8,500 at 9%, the avalanche targets the $1,200 balance first because it has the highest APR. The snowball also starts with the $1,200 balance in this particular example, but that will not always happen. With a $4,000 balance at 24% and a $1,200 balance at 9%, the two methods choose different targets. The choice changes where extra money goes; it does not remove the need to make each required minimum payment.

Choose assumptions that match the account

Before using a formula to compare plans, identify what the inputs mean and how often they change. A rate entered as 18% should be stored as a percentage, not the number 18, if the formula divides the annual rate by 12. A term of 36 means 36 monthly periods, not three years. Check that the balance is current and that the planned payment is affordable and above the interest accruing each period.

Then check the terms that a basic PMT or NPER calculation leaves out:

  • Whether the APR is fixed, variable, promotional, or subject to a scheduled change.
  • Whether interest accrues monthly or daily, and how the lender determines the balance used for interest.
  • Payment due dates, posting delays, fees, and whether missed or late payments affect the rate.
  • How payments above the minimum are allocated and whether the account has a prepayment penalty.
  • Whether the displayed balance includes pending transactions or interest not yet posted.

Excel for Microsoft 365 on Windows includes these functions, and the formulas shown also work in current Excel for Mac. Excel for the web can calculate them as well, though some ribbon and navigation details differ. The examples use ordinary cell formulas, so they do not depend on a newer function such as XLOOKUP or LET.

Common spreadsheet problems and checks

A common mistake is mixing annual and monthly rates. If B2 contains 12%, a monthly-rate assumption is B2/12, or 1%. Entering 12 instead of 12% and dividing by 12 would produce a rate of 100% per month, an entirely different calculation. Format the input as Percentage and confirm that Excel recognizes it as 12%.

Another frequent source of confusion is the sign convention. Financial functions treat money paid out and money received as opposite cash flows. If PMT returns a negative number, that may simply mean the present value and payment were entered with the same sign. Correct the signs consistently rather than deleting the minus sign without understanding the result.

Rounding also matters. Lenders typically post amounts in cents, while an unrounded formula can carry extra decimals from one period to the next. The example rounds monthly interest to cents for a more readable schedule, but this still may not reproduce a lender’s daily-interest calculation. Compare the first modeled interest charge with an actual statement. If they differ, investigate the rate, dates, balance, and calculation rules.

Finally, do not interpret a decreasing scheduled balance as proof that an account is current or that a payoff amount is exact. The schedule may omit new purchases, cash advances, fees, late charges, or a rate change. A payoff quote from the creditor is the appropriate figure to verify before closing an account or arranging a final payment.

A practical workflow for using the workbook

  1. Enter each balance, APR, minimum payment, and due date from current statements. Keep source information and the date you checked it nearby.
  2. Use PMT when you want a payment for a chosen term, or NPER when you want an estimated duration for a chosen payment. Check the sign and monthly-rate assumptions.
  3. Create a separate schedule for each debt if rates, payment rules, or promotional periods differ. Include extra payments only in the month you expect to make them.
  4. Choose snowball or avalanche, preserve the required minimums, and direct only the available additional budget to the selected target.
  5. Update balances and rates from statements regularly, then compare the spreadsheet with posted interest and actual payments. Revise assumptions when they do not match.

Spreadsheet estimates are not financial, tax, investment, or legal advice. For a decision involving your circumstances, verify the account terms with the lender and consult a qualified professional where appropriate.

Frequently Asked Questions

What is the Excel formula to pay off a loan? Use PMT to estimate a fixed monthly payment for a set term, or NPER to estimate the number of periods for a planned payment. The examples above show both, with the balance, rate, term, and payment inputs identified. Neither formula accounts automatically for every lender fee or interest method.

How do I calculate debt payoff with extra payments in Excel? Add the extra amount to the payment applied to the target debt in the months you plan to pay it. In an amortization schedule, calculate interest first, subtract it from the payment to find principal paid, and reduce the balance by that principal. Continue minimum payments on other accounts and verify the lender’s payment-allocation rules.

Is the debt snowball or avalanche better? The avalanche generally minimizes interest under comparable assumptions by targeting the highest-rate balance first. The snowball targets the smallest balance first and may be easier for some people to follow. Which is more suitable depends on your priorities and ability to maintain the plan; compare both using the same total monthly budget.

Why does Excel show a negative PMT result? Excel’s financial functions use positive and negative signs to represent cash flowing in opposite directions. If the loan amount is entered as money received, a payment may appear negative. Entering the principal as a negative value in PMT, as in the example, makes the payment result positive.

Can Excel tell me the exact payoff amount? Not from a basic PMT or NPER formula. Those functions estimate regular payments under assumed terms; they do not know the account’s live balance, daily interest through a specific date, pending charges, or lender fees. Request a payoff quote from the lender and confirm its valid-through date.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.