calculate-loan

01 Loan payments

Credit Card Monthly Payment Calculator in Excel

Calculator No. 01-18758 · Updated · Formula and assumptions shown below

The calculator on this page is switched off while it is checked. The formula is below, and the worked example shows how to do the sum by hand.

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.

A credit card monthly payment Excel calculator estimates a fixed monthly payment from your balance, APR, and payoff term, then shows the total paid and interest. Enter your own figures below; the estimate assumes a fixed rate and equal monthly payments, which may differ from a card issuer’s minimum-payment calculation.

Formula

The calculator uses the fixed-payment amortization formula, the same standard method used to calculate a level payment on a balance with a constant periodic interest rate:

Monthly payment = round[B × r ÷ (1 − (1 + r)−n), 2]

  • B is the current credit card balance in dollars.
  • r is the monthly interest rate as a decimal: APR ÷ 1,200. For example, 18% APR gives r = 0.015 per month.
  • n is the number of monthly payments: payoff period in years × 12.
  • round(…, 2) rounds the payment to cents.

Total paid is the rounded monthly payment multiplied by the number of payments. Estimated interest is total paid minus the starting balance. This total uses equal payments and does not model a final payment adjusted by a few cents to clear a remaining rounding balance.

Worked Example

Using the calculator’s defaults: balance B = $5,000, APR = 18%, and payoff period = 3 years.

  1. Convert the annual APR to a monthly decimal rate: r = 18 ÷ 1,200 = 0.015.
  2. Convert the term to months: n = 3 × 12 = 36.
  3. Substitute into the formula: payment = round[$5,000 × 0.015 ÷ (1 − (1.015)−36), 2] = $180.72 per month.
  4. Total paid = $180.72 × 36 = $6,505.92.
  5. Estimated interest = $6,505.92 − $5,000 = $1,505.92.

Assumptions

  • The APR stays fixed for the entire payoff period.
  • Payments are made once per month at the end of each monthly period.
  • Interest compounds monthly at APR ÷ 12, with no daily-balance timing adjustment.
  • No new purchases, cash advances, balance transfers, fees, late charges, taxes, or other account activity are included.

Build or Use the Calculator in Excel

You can reproduce the calculation in a spreadsheet by placing the balance in cell B1, APR as a percentage in B2, and payoff years in B3. For example, enter 5000, 18%, and 3. A monthly-payment formula is:

=ROUND(B1*(B2/12)/(1-(1+B2/12)^(-B3*12)),2)

Excel stores 18% as 0.18, so dividing by 12 gives the monthly decimal rate. If you instead type 18 as a plain number, the rate conversion must be B2/1200. Mixing those formats can inflate the calculated rate by a factor of 100.

After the payment result, calculate total paid with the payment cell multiplied by B3*12, and estimated interest by subtracting B1 from that total. Format money cells as currency and APR as a percentage. Keep the payment rounded to cents before calculating totals so the displayed results follow the same convention as this calculator.

What the Result Means—and What It Does Not

The monthly payment is the amount that, under the stated assumptions, would amortize the starting balance over the selected number of months. A shorter term generally produces a larger payment but fewer months of interest; a longer term generally lowers the scheduled payment while extending the time interest accrues. The total-interest figure is an estimate from this model, not a statement from a card issuer.

A credit card’s required minimum payment is not necessarily a fixed payoff payment. Issuers may calculate minimums using an account-specific percentage, a dollar floor, interest and fees, or terms in the card agreement. A minimum payment can also change as the balance changes. This calculator does not derive that required amount and should not be used to determine whether a payment satisfies an account’s terms. Check the current statement or issuer’s disclosed payment calculation for the amount due.

Likewise, a balance may accrue interest daily, and the APR or balance can change. Promotional rates, different APR categories, new transactions, fees, and payment posting dates can all make actual results diverge from a simple monthly model. Obtain the applicable APR and account details from the card issuer; the sample rate here is only an example input.

Common Excel Errors and Limitations

  • Entering 18 instead of 18%: If the spreadsheet cell is a plain number, use APR/1200; if it is formatted as a percentage and stores 0.18, use APR/12.
  • Using the APR directly as a monthly rate: Convert the annual percentage to a monthly decimal before applying the formula.
  • Rounding too early: Round the final payment to cents, not the monthly rate or intermediate formula terms. Small rounding differences can accumulate across many payments.
  • Assuming the result is the minimum due: The formula calculates a planned fixed payoff payment, not the issuer’s required minimum or current statement balance.
  • Leaving out new activity: Purchases, fees, rate changes, or missed payments alter the balance path and are not represented.

If the displayed payment is unaffordable or the APR varies, the fixed-payment result cannot describe the actual account schedule. You can model different balances, APR inputs, or payoff periods for arithmetic comparison, but those scenarios do not predict issuer terms or account outcomes.

Frequently Asked Questions

How do I calculate a credit card monthly payment in Excel?
For a fixed balance, APR, and term, use =ROUND(balance*(APR/12)/(1-(1+APR/12)^(-years*12)),2) when APR is stored as a percentage such as 18%. If APR is entered as the number 18, use APR/1200 instead.

Does this calculate my credit card minimum payment?
No. It calculates an equal payment intended to clear the balance over a selected term. Minimum-payment rules vary by account and can change; consult the issuer’s statement or payment terms.

Why does my Excel answer differ from my statement?
The statement may reflect daily interest, a changing balance or APR, fees, transaction categories, payment dates, or a different payment calculation. This estimate assumes monthly compounding and a fixed balance schedule.

Can I use the calculator if I keep making purchases?
Not for an exact payoff forecast. New purchases and other account activity change the balance, so the model’s fixed-payment and fixed-balance assumptions no longer apply.

How much should I pay to clear the balance in a set number of months?
Enter the balance, APR, and desired payoff period. The result estimates the equal monthly amount under the displayed assumptions; it is not a minimum-payment figure or a guarantee of the issuer’s final balance.

This result is an estimate; the card issuer’s disclosure and account records provide the exact figures.

Related

Formula used
in bal | Credit card balance | $ | 5000 | 0 | 100000000
in apr | Interest rate (APR) | % | 18 | 0.01 | 100
in yrs | Payoff period | years | 3 | 0.0833 | 50
out pmt | Monthly payment | $ | round(bal * (apr / 1200) / (1 - (1 + apr / 1200) ^ -(yrs * 12)), 2) | 2
out tot | Total paid | $ | pmt * yrs * 12 | 2
out int | Total interest | $ | tot - bal | 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 →