calculate-loan

01 Loan payments

Credit Card Amortization Calculator and Excel Formula

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

Months to repay 24 months
Total paid
$6,000.00
= Monthly payment × months to repay
Total interest
$1,000.00
= Monthly payment × months to repay − starting balance
Starting balance
$5,000
Input
You enter
Starting balance ($), Interest rate (APR) (%), Monthly payment ($)
You get
Months to repay (months), Total paid ($), Total interest ($)
Rounding
0 dp · 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.

A credit card amortization spreadsheet estimates how a balance declines as you make monthly payments, and how much interest those payments add over time. Enter the starting balance, APR, and planned payment to see an estimated payoff period, total paid, and interest; the calculator below uses a fixed monthly payment and a fixed APR.

Formula

The monthly rate is the APR divided by 12 and then by 100: r = APR / 1200. The estimated number of monthly payments is n = ceil(round(-ln(1 – r × B / M) / ln(1 + r), 6)), where B is the starting balance in dollars and M is the fixed monthly payment in dollars. Total paid is T = M × n, and estimated interest is I = T − B. The calculator rounds the number of months up to a whole payment period and calculates totals using that whole number.

This repayment formula assumes the payment is large enough to cover the first month’s interest. It cannot produce a payoff estimate when M ≤ r × B, because the balance would not decline under the stated assumptions. The calculator returns “Cannot compute” for that case.

Worked Example

Using the default inputs—balance B = $5,000, APR = 18%, and monthly payment M = $250:

  1. Convert the APR to a monthly rate: r = 18 / 1200 = 0.015, or 1.5%.
  2. Substitute into the month formula: n = ceil(round(−ln(1 − 0.015 × 5,000 / 250) / ln(1.015), 6)).
  3. Since 0.015 × 5,000 / 250 = 0.3, the expression is ceil(round(−ln(0.7) / ln(1.015), 6)) = ceil(23.98…) = 24 months.
  4. Total paid = $250 × 24 = $6,000.00.
  5. Estimated interest = $6,000 − $5,000 = $1,000.00.

The month count is rounded up because the model treats every payment as $250. In an actual payoff schedule, the final payment may be smaller than the regular payment; a spreadsheet that calculates each month separately can show that adjustment.

Assumptions

  • The APR is fixed for the entire repayment period.
  • Payments are made monthly at the end of each monthly period.
  • Interest compounds monthly at APR / 12, using a monthly rate of APR / 1200.
  • The payment amount stays fixed, and no new purchases or cash advances are added.
  • Fees, penalty charges, taxes, and changes to the account’s terms are not included.

How a Credit Card Amortization Spreadsheet Works

An amortization schedule breaks repayment into monthly rows. Each row starts with a balance, calculates interest for the period, applies a payment, and carries the remaining balance into the next row. This makes the changing split between interest and principal visible. Early in repayment, interest can take a larger share of a payment; as the balance falls, the interest charge generally falls too, provided the APR and other assumptions stay unchanged.

A common spreadsheet layout uses columns for payment number, beginning balance, interest, payment, principal paid, and ending balance. For a simple monthly model, the core calculations are:

  • Interest: beginning balance × monthly rate.
  • Principal paid: payment − interest.
  • Ending balance: beginning balance − principal paid.

For example, at a 1.5% monthly rate on a $5,000 beginning balance, one month’s modeled interest is $75. A $250 payment would reduce principal by $175, leaving an estimated $4,825 balance. A real card may calculate interest using daily balances and transaction timing, so this monthly example is an approximation rather than a statement forecast.

Putting the Calculation in Excel

To build a basic schedule, put the starting balance and APR in separate input cells, and enter the fixed payment in another cell. Convert APR to a monthly decimal rate by dividing the percentage value by 1200. In the first schedule row, calculate interest from the opening balance, subtract that interest from the payment to find principal paid, and subtract principal paid from the opening balance to get the closing balance. Link the next row’s opening balance to the prior row’s closing balance, then fill the formulas down.

Use cell references rather than typing the same inputs into every formula. Format money cells to two decimal places, but keep full precision in underlying formulas unless the intended model explicitly rounds each interest charge. Different rounding choices can make the final total differ by cents. Stop the schedule when the balance reaches zero, and cap the final payment at the amount actually owed plus that period’s interest so the sheet does not show a negative balance.

What the Results Mean—and What They Leave Out

The calculator’s payoff month is a model estimate, not a guaranteed date. It assumes the same payment arrives once per month and that interest is represented by a monthly rate. Card issuers may calculate interest daily, apply payments under account-specific rules, or change the APR. New charges, returned payments, fees, promotional-rate expirations, and variable rates can also change the schedule.

For a closer spreadsheet estimate, use the account’s actual APR and balance, update the balance and rate when they change, and enter planned payments by month. If the statement provides an interest charge, comparing it with the model can reveal differences in timing or assumptions. The exact account calculation should be checked against the card agreement or statement information rather than inferred from this simplified formula.

Common Spreadsheet Problems

  • Entering APR as a decimal: If a cell contains 18 for 18%, use 18 / 1200 for the monthly decimal rate. If it contains 18% as a formatted percentage, use that stored percentage carefully; spreadsheet percentage formats can create a factor-of-100 error.
  • Payment does not cover interest: At $5,000 and 18% APR, modeled first-month interest is $75. A payment of $75 or less will not reduce principal under this model, so the payoff formula has no finite result.
  • Ignoring new charges: Additional purchases increase the balance and can invalidate a schedule based on repayment alone.
  • Overlooking the last payment: The final amount due is often less than the fixed payment. A row-by-row schedule can model it more accurately than multiplying a rounded month count by the full payment.
  • Rounding every step: Rounding monthly interest to cents may differ slightly from keeping full precision and rounding only displayed values. Decide which convention the spreadsheet uses and label it.

Frequently Asked Questions

Can I use Excel’s PMT function for a credit card?
PMT calculates a level payment for a stated rate, number of periods, and present value. It is useful when the payoff term is known and you want an estimated payment, but it does not automatically model new card transactions, daily interest, or account-specific fees.

Why does my spreadsheet show a different payoff time?
Differences can come from daily rather than monthly interest, payment dates, rounding, a variable APR, or the final payment being smaller. Check that the APR conversion, payment frequency, starting balance, and treatment of new charges match between calculations.

What if my monthly payment is less than the interest?
The balance will not decline under the fixed-rate, fixed-payment model. The calculator cannot estimate a payoff period in that situation. Actual balances may also change because of fees, new transactions, or other account activity.

Does this include minimum payments?
No. It models a fixed dollar payment that you enter. A minimum payment may be calculated differently and can change as the balance changes, so a fixed-payment schedule should not be treated as a minimum-payment forecast.

Can I use it if I keep making purchases?
Not as-is. The calculator assumes no new charges. A spreadsheet can include an additional monthly charges column, but the timing and interest treatment of each transaction may require more detailed account data.

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

Related

Formula used
in bal | Starting balance | $ | 5000 | 0.01 | 100000000
in apr | Interest rate (APR) | % | 18 | 0.01 | 100
in pmt | Monthly payment | $ | 250 | 0.01 | 100000000
out months | Months to repay | months | ceil(round(-ln(1 - (apr / 1200) * bal / pmt) / ln(1 + apr / 1200), 6)) | 0
out paid | Total paid | $ | pmt * months | 2
out interest | Total interest | $ | pmt * months - 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 →