Debt Payment Excel Formula: Calculate Your Monthly Cost

Formulas & functions · Updated November 22, 2026

In Excel, use the PMT function to calculate a fixed monthly debt payment: =-PMT(annual_rate/12,number_of_months,balance). For example, a $12,000 balance at 18% annual interest paid over 36 months has a calculated payment of about $433.83 per month, assuming a fixed rate and payments at the end of each month.

The Excel formula for a debt payment

PMT calculates the regular payment needed to pay off a loan or other fixed-rate balance over a chosen number of periods. Excel’s syntax is PMT(rate,nper,pv,[fv],[type]): rate is the interest rate per payment period, nper is the number of payments, and pv is the present value, usually the amount owed today. The last two arguments are optional; for a fully repaid debt, the future value is usually zero, and type 0 means each payment is made at the end of a period.

Excel normally returns a negative payment when the balance is entered as a positive amount, because it treats the payment as money leaving your account. Put a minus sign before PMT when you want to display the payment as a positive number. The minus sign changes only how the result is shown, not the payment calculation.

Worked example with spreadsheet cells

Enter the example inputs in a small table: A2 is the balance ($12,000), B2 is the annual interest rate (18%), and C2 is the term in months (36). Format B2 as a percentage and enter 18%, not 18. In D2, use this formula:

With the balance in A2, annual rate in B2, and number of monthly payments in C2, enter the following in D2:

=-PMT(B2/12,C2,A2,0,0)

The result is approximately $433.83 per month. The formula divides the annual rate by 12 to get a monthly rate, uses 36 monthly periods, and specifies a zero remaining balance and end-of-month payment timing. The displayed value may vary by a cent if the cell is formatted to show two decimal places; Excel retains more precision internally unless you explicitly round the formula.

To estimate total payments and the interest paid over the full term, use two more cells. With the payment in D2 and the number of payments in C2, enter this in D3:

=D2*C2

D3 returns about $15,617.80. With the original balance in A2 and the estimated total in D3, enter this in D4:

=D3-A2

D4 returns about $3,617.80 in interest. These totals assume the same payment and rate each month and do not include fees, insurance, or other charges. They are estimates from the stated inputs, not a lender’s payoff quote.

How PMT works and what the inputs mean

PMT is based on the way interest accrues on a declining balance. Early payments include a larger interest share because the balance is higher; later payments generally apply more to principal. The formula finds a level payment that, under its assumptions, reduces the balance to the specified future value after the stated number of periods.

  • Rate: Enter the rate for one payment period. For a nominal annual rate and monthly payments, dividing by 12 is a common model. For weekly or biweekly payments, the conversion and period count must match that schedule.
  • Nper: Enter the total number of payments, not the number of years. A three-year monthly term is 36 periods.
  • Pv: Enter the amount borrowed or current balance as a positive number when using the positive-payment version shown above.
  • Fv: Use zero if the goal is to repay the balance fully. A different future value represents an amount still owed or retained at the end.
  • Type: Use 0 for payments at the end of each period or 1 for payments at the beginning. Most standard monthly loan schedules use end-of-period payments, but check the agreement.

Keep the timing consistent: if payments are monthly, the rate and period count must describe monthly periods too. Putting an annual rate directly into the rate argument while also using 36 monthly periods would calculate a very different, incorrect payment for this example.

Build an amortization schedule

A single PMT result gives the regular payment, but an amortization schedule shows how each payment splits between interest and principal. It is useful for checking the balance month by month and understanding why the principal falls slowly at first on a high-rate debt.

For a simple schedule, set up columns A through F with month, opening balance, interest, scheduled payment, extra payment, and closing balance. Use row 9 for the first month. Put the annual rate in B2, the regular payment in D2, and an optional recurring extra-payment amount in D5. Enter the opening balance in B9. The following formulas assume monthly periods and payments at month-end.

With the opening balance in B9 and annual interest rate in B2, enter the month’s interest in C9:

=B9*$B$2/12

With the regular payment in D2, opening balance in B9, and current interest in C9, enter the scheduled payment in D9. This caps the final scheduled payment at the amount due:

=MIN($D$2,B9+C9)

With the recurring extra amount in D5, opening balance in B9, interest in C9, and scheduled payment in D9, enter the extra payment in E9. This prevents the extra amount from exceeding the remaining amount due:

=MIN($D$5,MAX(0,B9+C9-D9))

With the opening balance in B9 and the interest and payments in C9, D9, and E9, enter the closing balance in F9:

=MAX(0,B9+C9-D9-E9)

For the next month, use the previous closing balance as the next opening balance. With the first month’s closing balance in F9, enter this in B10:

=F9

Copy the month formulas down for as many periods as needed. The extra amount is limited in the final period, so the schedule does not show a negative balance. This basic model assumes the lender applies extra payments directly to principal and does not charge a prepayment penalty; confirm how extra payments are handled in the actual agreement.

Choosing the right rate and payment timing

The simple annual-rate-divided-by-12 approach is appropriate for illustrating a fixed monthly rate, but real debt agreements do not all calculate interest the same way. Some use daily accrual, variable rates, irregular first periods, or fees incorporated into the payment. A lender’s payment schedule can therefore differ slightly from PMT even when the balance, stated annual rate, and term appear to match.

Use the interest rate that matches the calculation you are trying to model. If a lender provides a monthly periodic rate, use that rate directly rather than dividing it again. If the debt has a variable rate, one PMT formula assumes a rate that stays fixed; a future payment estimate would need to be recalculated when the rate changes. Also make sure that the balance is the amount actually being amortized, rather than a statement balance that includes charges treated separately.

PMT versus a payoff schedule or minimum payment

PMT answers a specific question: what equal payment would repay a balance over a fixed number of periods at a fixed rate? It does not automatically reproduce the minimum due on a credit card, predict a variable-rate payment, or account for new charges. Credit-card minimums may be set by contract using a percentage, a fixed floor, interest, fees, or a combination, so a PMT result should not be mistaken for the required minimum.

A schedule is a better choice when you want to model extra payments, see the interest portion each month, or estimate when a balance reaches zero. A spreadsheet that handles several debts can also compare payment strategies, such as paying extra toward the highest-rate balance or the smallest balance. Those approaches change where available extra money goes; they do not change the contract’s required payment on each debt. Use the lender’s statements and terms for required amounts and due dates.

Common errors and practical checks

  • Using years instead of months: For a three-year monthly loan, enter 36 periods, not 3.
  • Using an annual rate as a monthly rate: If the input is a nominal annual percentage and payments are monthly, the example divides by 12.
  • Getting a negative result: This is Excel’s cash-flow sign convention. Negate the PMT result for a positive displayed payment, or use consistent opposite signs for money received and paid.
  • Rounding every payment too early: Formatting a cell to two decimals changes its appearance, not its stored value. Rounding inside the calculation can create a small final-balance difference.
  • Expecting lender precision: Fees, daily interest, payment dates, rate changes, and rounding rules can alter the real amount. Compare the model with the lender’s schedule.
  • Using the wrong balance: A payoff amount can include accrued interest or fees not present in the principal balance. Check which figure belongs in the model.

Before relying on a workbook, check that the first period’s interest looks plausible, that the ending balance declines, and that the final balance is near zero rather than materially positive or negative. If the schedule does not amortize, verify the rate units, period count, payment timing, and signs first.

Frequently Asked Questions

What is the Excel formula for a monthly debt payment? Use =-PMT(annual_rate/12,number_of_months,balance) for a positive displayed payment on a fixed-rate monthly schedule. The rate and period count must use the same time unit.

Why does Excel show a negative payment? PMT follows a cash-flow sign convention: money received and money paid have opposite signs. A positive balance entered as present value commonly produces a negative payment, so putting a minus sign before PMT displays the payment as positive.

Can PMT calculate a credit-card minimum payment? Not reliably unless the card’s minimum-payment rule is exactly represented by the inputs. PMT calculates an equal amortizing payment for a chosen term; credit-card minimums may follow a different formula and can change as the balance changes.

How do I include extra monthly payments in Excel? Keep the regular PMT amount, then add an extra-payment column to an amortization schedule. Apply the extra amount to the balance after that period’s interest and limit the final extra payment to the amount still owed. Check the lender’s rules for principal-only payments and any fees.

Why does my Excel payment differ from the lender’s amount? The model may use a different rate convention, payment date, day-count method, fee treatment, or rounding rule. Confirm the exact balance, rate, term, and payment timing with the lender before treating the spreadsheet as a precise payoff figure.

Excel calculations can help organize estimates, but they are not financial, tax, investment, or legal advice. Check the actual agreement and current payoff terms with the lender, and consult a qualified professional if you need advice about your circumstances.

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.