Credit Card Payment Calculator Spreadsheet (PMT, NPER)

Quick Answer: To calculate a credit card payment that clears the balance by a target date, use Excel's PMT function: =PMT(APR/12, months, -balance). A $6,000 balance at 22.99% APR needs $314.21 every month to reach zero in 24 months. To go the other way, =NPER(APR/12, payment, -balance) tells you how many months a fixed payment takes: $250 clears the same balance in about 33 months.

A credit card payment calculator answers one of two questions: how much must I pay each month to be done by a certain date, or how long will the amount I can afford take? Excel and Google Sheets answer both with built-in finance functions, PMT and NPER. Add an amortization table and you can see every month's split between interest and principal, then extend it to several cards.

How much do I need to pay each month to clear the card by a date?

PMT treats your card like a loan you have decided to pay off on a schedule. Microsoft's PMT function reference gives the syntax as PMT(rate, nper, pv, [fv], [type]) and stresses that rate and nper must use the same unit: for monthly payments, divide the annual rate by 12 and count periods in months.

Set up three cells:

• B1: balance, 6000
• B2: APR, 22.99%
• B3: months to payoff, 24

Then in B4:

=PMT(B2/12,B3,-B1)

The minus sign in front of the balance makes the result positive. Without it, Excel returns -314.21, because it shows payments as money leaving you.

Change B3 and watch the trade-off:

Stretching from 12 to 36 months cuts the payment by $332 but triples the interest. Google Sheets has the same function, documented on its PMT help page, with identical arguments.

One assumption to keep in mind: PMT uses APR ÷ 12 as a fixed monthly rate, while your issuer charges a daily rate over cycles of different lengths. The result will be within a few dollars of reality, which is close enough for planning. For exact cycle-by-cycle interest, the credit card interest calculator in Excel compounds daily instead.

How long will a fixed payment take?

If your budget sets the payment, solve for time. NPER takes NPER(rate, pmt, pv, [fv], [type]):

=NPER(B2/12,250,-B1)

returns 32.45, meaning 32 full payments of $250 and a smaller 33rd. Raise the payment to $300 and NPER drops to 25.46, so the extra $50 shaves seven months off.

NPER returns #NUM! when the payment does not cover a month's interest. At 22.99%, a $6,000 balance accrues about $115 in its first month, so any payment below that never finishes. That error is a useful warning in itself.

Two optional arguments are worth knowing. The fv argument lets you aim for a balance other than zero: =NPER(B2/12,250,-B1,1000) counts the months until $1,000 is left, handy if a bonus or tax refund will clear the rest. The type argument switches payments from the end of each period (0, the default) to the start (1). Paying right after the statement closes is closer to type 1, and it trims the month count slightly because each payment reduces the balance before that month's interest is charged. Wrap the result in ROUNDUP(…,0) when you want a whole number of payments to put in a calendar.

Building the amortization table

PMT and NPER give one number each. A table shows the path. Start it a few rows below the inputs, with headers in row 7:

Fill down until column F reaches zero. The first three months of the 24-month plan look like this:

Interest falls and principal rises every month even though the payment stays put. That shift is the whole case for paying a fixed amount rather than the shrinking minimum; the minimum payment calculator builds the opposite schedule so you can compare.

If you prefer functions over the running table, Excel's IPMT returns the interest portion for any single month: =IPMT(B2/12, 3, B3, -B1) gives the third month's $107.24.

Should you pay several cards by avalanche or snowball?

With more than one card, the question becomes where the extra money goes after every minimum is paid. Two common orders:

• Avalanche: extra money goes to the highest APR first. It minimizes total interest.
• Snowball: extra money goes to the smallest balance first. Cards disappear sooner, which some people find easier to stick with.

Take three cards and a total budget of $600 each month, with each card's minimum held fixed for simplicity:

Both orders attack card A first, since it is the smallest and the most expensive. After that they split: avalanche goes to C (24.24%), snowball to B (smaller balance).

The avalanche saves $130 here; snowball clears a second card three months sooner. Neither is wrong. The gap grows when APRs are further apart.

To model this in a sheet, give each card its own block of the amortization columns and add a column per card for "extra payment". The extra for the target card is =Budget - SUM(all minimums) - extra already assigned, and it flows to the next card in your chosen order once a balance hits zero. A simple IF(closing_balance_of_target=0, …) switch handles the rollover.

Where do the inputs come from?

Every number in the planner is printed on your statements:

1. Balance: the new balance on the latest statement, not the current balance in the app, unless you want to include charges since the closing date. Statement balance vs current balance explains the difference.
2. APR: the purchase APR in the interest charge calculation section. If part of the balance is a transfer or cash advance at a different rate, treat it as a separate line in the planner.
3. Minimum payment: the payment box near the top. The guide to reading a credit card statement marks where each item sits.

For a one-off plan, typing three numbers per card is quicker than any tool. If you want to watch progress over months, the transaction history helps: payments, interest charges and new purchases all show up in it. When your issuer only offers PDF statements, QuickBankConvert can turn each one into spreadsheet rows (date, description, signed amount) so you can filter for interest and payment lines; the upload is processed on our servers, and pricing lists what a free account covers.

What the planner does not tell you

A payoff plan assumes you stop adding to the balance. Every new purchase pushes the end date back, so either freeze the card or add a purchases column. It also assumes a fixed APR; a variable rate that rises will lengthen the plan slightly. And it says nothing about whether a balance transfer or consolidation loan would be cheaper. If you are weighing those, comparing credit card debt consolidation options puts them on equal terms using the same inputs.

Related guides

• How to calculate credit card interest
• Average credit card minimum payment percentage
• 0% APR credit card payoff calculator