calculate-loan

01 Loan payments

Credit Card Amortization Schedule Excel: Formula & Example

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

Months to repay 24 months
Interest over schedule
$989.13
From the formula below, to the cent
Balance after schedule
$-10.87
From the formula below, to the cent
Starting balance
$5,000
Input
You enter
Starting balance ($), Interest rate (APR) (%), Monthly payment ($), Schedule length (months)
You get
Months to repay (months), Interest over schedule ($), Balance after schedule ($)
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 schedule in Excel estimates how a balance declines as you make monthly payments, showing each month’s interest, principal paid, and remaining balance. Enter the starting balance, APR, payment amount, and number of months; this calculator returns the projected payoff time and a month-by-month schedule you can build in Excel.

Formula

For a fixed APR and a level payment made monthly, the monthly interest rate is r = APR / 1200. In each month, calculate interest on the opening balance, subtract the portion of the payment that reduces principal, and carry the resulting balance forward:

  • Interest for month t: opening balance × r.
  • Principal paid: monthly payment − interest for the month.
  • Closing balance: opening balance + interest − payment.

After k equal monthly payments, the mathematical balance is Bk = P(1+r)k − M((1+r)k−1)/r. Here, P is the starting balance in dollars, r is the monthly rate as a decimal, M is the payment in dollars per month, and k is the number of monthly periods. The calculator’s schedule-length output is the number of months entered; its interest-over-schedule output is total payments less the original balance plus the balance still remaining. The payoff-month estimate assumes an unchanged payment and no new charges.

The payment must exceed the first month’s interest for the balance to decline. The calculator flags a payment at or below that amount as not computable for payoff. In practice, a final payment may be smaller than the regular payment; the compact estimate treats payments as level, so use the Excel rows below to model a capped final payment more precisely.

Worked Example

Using the calculator’s defaults—$5,000 starting balance, 18% APR, $250 monthly payment, and a 24-month schedule—the monthly rate is 18% ÷ 12 = 1.5%, or 0.015. First-month interest is $5,000 × 0.015 = $75. The payment leaves $250 − $75 = $175 to reduce principal, so the first closing balance is $5,000 + $75 − $250 = $4,825.

The projected payoff time is calculated as ceil(round(−ln(1 − 0.015 × 5,000 / 250) ÷ ln(1.015), 6)) = 25 months. The schedule output covers 24 months. Applying the balance formula gives a balance after 24 payments of approximately $235.45. Therefore, the schedule-period interest is 24 × $250 − $5,000 + $235.45 = $1,235.45. These figures assume the same payment amount each month and no additional transactions; rounded monthly entries in a spreadsheet can differ slightly from a formula using full precision.

Build the Schedule in Excel

Put the starting balance, APR, and monthly payment in separate input cells. For example, use B1 for $5,000, B2 for 18%, and B3 for $250. Format B2 as a percentage and enter 18% (Excel stores it as 0.18). Create columns for month, opening balance, payment, interest, principal, and closing balance.

  1. In the first schedule row, enter month 1 and link opening balance to the starting-balance cell.
  2. Calculate interest with =OpeningBalanceCell*$B$2/12.
  3. Calculate principal as payment minus interest. If modeling a smaller final payment, use a payment no greater than opening balance plus interest.
  4. Calculate closing balance as opening balance plus interest minus payment.
  5. For the next row, link opening balance to the previous row’s closing balance, increase the month number, and copy the formulas down.

Use absolute references such as $B$2 for inputs that should not shift when formulas are copied. You can stop once the closing balance reaches zero, or use a remaining-balance formula such as =MAX(0,OpeningBalance+Interest-Payment) to prevent a negative displayed balance. The cap simplifies the final row; adjust that row’s payment to the actual amount due if you want payment totals to match the modeled payoff exactly.

What the Schedule Shows—and Omits

An amortization schedule separates a payment into interest and principal rather than treating the full payment as debt reduction. Early in a repayment period, interest is calculated on a larger balance; as the balance falls, the interest portion generally falls too, provided the APR and payment pattern stay unchanged. A schedule can help illustrate how payment size affects the path of a balance, but it is a projection, not a statement from a card issuer.

Credit cards often have changing balances because of purchases, fees, credits, and payments that vary from month to month. Some accounts may also have different rates for different balance categories. A single-rate, fixed-payment schedule cannot represent those details unless you add separate transactions or balance categories. Check the account’s statement and terms for the actual balance, rate, and calculation method relevant to your situation.

Common Excel Errors and Limitations

  • Entering APR in the wrong form: If the cell contains 18%, divide by 12; if it contains the number 18, divide by 1,200. Mixing these conventions can greatly distort interest.
  • Rounding every row: Displaying two decimals does not require rounding the underlying formula. Rounding monthly interest and principal can cause small cumulative differences; follow a consistent approach and compare totals with account records.
  • Payment too small: If payment does not exceed monthly interest, the balance will not decline under this model. A formula that calculates months to payoff cannot produce a valid finite payoff in that case.
  • Ignoring new charges: New spending, fees, and rate changes alter the balance and can make the projection inaccurate. Add those amounts in the appropriate rows if you are building a more detailed model.
  • Assuming a fixed payment is required: The entered payment is a modeling input, not a statement of the required minimum. The required amount and its calculation must be taken from the relevant account disclosure or statement.

Assumptions

  • The APR stays fixed for the full modeled period.
  • Payments are made monthly at the end of each period, after that period’s interest is added.
  • Interest is modeled monthly at APR divided by 12; daily balance methods and transaction timing are not represented.
  • No new purchases, fees, credits, taxes, or other account-specific adjustments are included.

Frequently Asked Questions

Can I make a credit card amortization schedule in Excel?
Yes. Set up columns for opening balance, payment, interest, principal, and closing balance, then link each month’s opening balance to the prior month’s closing balance.

What Excel formula calculates monthly credit card interest?
For a simple monthly APR model, multiply the opening balance by APR divided by 12. If the APR cell contains 18%, a formula is =OpeningBalance*APRCell/12.

Does a credit card amortization schedule include new purchases?
Not unless you add them. This calculator assumes no additional charges, fees, or credits, so a real changing balance may differ.

Why does the balance barely go down?
Interest uses part of each payment before principal is reduced. When the payment is close to the month’s interest charge, only a small amount reduces the balance; if it does not exceed interest, the balance will not decline in this model.

Why does my Excel result differ from my statement?
Statements can reflect transaction dates, daily balance calculations, changing rates, fees, and rounding conventions not represented by a fixed monthly model. Use the account’s disclosed figures for exact amounts.

This result is an educational estimate; the lender’s disclosure gives the exact figures.

Related

Formula used
in bal | Starting balance | $ | 5000 | 0 | 100000000
in apr | Interest rate (APR) | % | 18 | 0.01 | 100
in pmt | Monthly payment | $ | 250 | 0 | 100000000
in months | Schedule length | months | 24 | 1 | 600
out payoff | Months to repay | months | ceil(round(-ln(1 - (apr / 1200) * bal / pmt) / ln(1 + apr / 1200), 6)) + 0 * sqrt(pmt - bal * apr / 1200) | 0
out int | Interest over schedule | $ | pmt * months - bal + (bal * (1 + apr / 1200) ^ months - pmt * ((1 + apr / 1200) ^ months - 1) / (apr / 1200)) | 2
out end | Balance after schedule | $ | bal * (1 + apr / 1200) ^ months - pmt * ((1 + apr / 1200) ^ months - 1) / (apr / 1200) | 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 →