Credit Card Interest Rate Calculator in Excel (Formulas)

Quick Answer: A credit card interest calculator in Excel needs four input cells (balance, APR, days in the billing cycle, monthly payment) and one formula: =Balance*((1+APR/365)^Days-1), which compounds the daily periodic rate over the cycle. Copy that row down twelve times, subtracting each payment, and you have a year's projection. On $5,000 paid down by $200 every month, 18% APR costs about $775 in interest over twelve months and 20% about $871.

You can build a reusable credit card interest calculator in a spreadsheet with one input block and one projection table. The input block holds your balance, APR and payment; the table runs month by month, compounding interest daily the way most issuers do. Change the APR cell and every month recalculates, which makes comparing two rates, such as 18% and 20%, a one-second job.

What goes into the input block?

Put the inputs on their own sheet named Inputs, one per row, with the label in column A and the value in column B. Give each value cell a name; both Excel and Google Sheets support named ranges, and a formula that says APR is easier to audit than one that says Inputs!$B$3.

Enter the APR as a decimal (0.18) or as a percentage-formatted cell (18%). Excel treats both the same. If you are unsure of your APR or which balance type it applies to, the guide to finding your credit card interest rate shows where issuers print it.

Which formulas does the calculator use?

Three formulas do all the work. Each one answers one of the questions people type into a calculator search box.

Daily periodic rate (the "credit card daily interest calculator" number):

=APR/YearDays

At 18% that is 0.049315% a day; at 20% it is 0.054795%.

One cycle's interest charge, compounded daily:

=Balance*((1+APR/YearDays)^Days-1)

For $5,000 at 18% over 30 days this returns $74.50. The simple version, =Balance*APR/YearDays*Days, returns $73.97. The 53-cent gap is daily compounding: each day's interest joins the balance that accrues interest the next day. Whether your card compounds is stated in the agreement, so pick the formula that matches it.

Effective annual rate (what the APR really costs if nothing is paid for a year):

=(1+APR/YearDays)^YearDays-1

An 18% APR compounds to 19.72% a year; a 20% APR to 22.13%. That is the figure behind "credit card compound interest calculator" searches, and it explains why a year of carrying a balance costs more than the headline rate suggests.

How do you build the 12-month projection?

On a second sheet named Projection, give each billing cycle a row. The columns:

1. A, Cycle start. A2 is =StartDate; A3 is =EDATE(A2,1), filled down to row 13.
2. B, Days. =EDATE(A2,1)-A2, which returns 31, 28, 31, 30 and so on for a calendar-month cycle. If your cycles close on a different day, set StartDate to the day after your closing date.
3. C, Opening balance. C2 is =Balance; C3 is =F2.
4. D, Interest. =ROUND(C2*((1+APR/YearDays)^B2-1),2).
5. E, Payment. =MIN(Payment,C2+D2), so the last payment never overshoots.
6. F, Closing balance. =C2+D2-E2.

Below the table, =SUM(D2:D13) gives the year's total interest and =F13 the balance left. The payment here lands at the end of each cycle, which is a slightly pessimistic assumption; paying earlier in the cycle lowers the average balance and the interest.

EDATE returns the date a given number of months after a start date, and Google Sheets has a function of the same name with the same arguments, so the workbook can live in either.

18% vs 20% APR: what does the projection show?

Run the sheet twice with a $5,000 starting balance and $200 paid each month for calendar-year 2027 cycles:

A two-point APR difference costs about $96 over the year on this balance and leaves you $96 further from zero. Notice February: three fewer days, so noticeably less interest, which is why dividing the APR by 12 never quite matches a statement.

To see both rates side by side without duplicating the sheet, add a second APR input (APR2) and repeat columns C to F to the right, pointing their formulas at APR2. A third column pair handles a promotional or cash advance rate.

If you stop paying altogether, the same sheet with Payment set to 0 shows the balance compounding: $5,000 grows to about $5,986 at 18% and $6,107 at 20% after twelve cycles.

Monthly interest rate: when is APR divided by 12 good enough?

Plenty of calculators show a "monthly interest rate" of APR ÷ 12, which is 1.5% at 18% and 1.667% at 20%. It is fine for rough planning and for loan-style functions such as PMT and NPER, which assume equal monthly periods. It is not what your issuer charges. A 31-day cycle at 18% charges 31 × 0.049315% = 1.529% before compounding, and a 28-day February charges 1.381%.

Use APR ÷ 12 when you want a quick payoff estimate; use the daily formula when you want a number you can hold against a statement. The credit card payoff spreadsheet guide uses the monthly version deliberately, because PMT needs a fixed rate per period.

How do you check the calculator against your statement?

A projection is only as good as its first row. Test it against a cycle that has already closed:

• Set Balance to last cycle's balance subject to interest rate (printed in the interest charge calculation section), Days to the cycle length shown on the statement, and compare the result with the interest charge line.
• If yours comes out higher than the statement, the card probably does not compound daily; switch to the simple formula.
• If it is a few dollars off, the real average daily balance differed from the opening balance because of purchases and payments during the cycle. The step-by-step method in how to calculate credit card interest rebuilds that average from the transaction list.
• Cash advances and balance transfers carry their own APRs. Calculate each balance type separately; credit card cash advance interest covers the one with no grace period.

Getting the transaction list into the workbook is the tedious part if your issuer only gives you PDFs. QuickBankConvert reads a credit card statement PDF and returns one row per transaction with date, description and signed amount, so you can paste a cycle in rather than retype it; the credit card statement to Excel page describes the output. The file is uploaded to our servers for parsing. A free account downloads CSV or Excel; see pricing for the limits.

What does the calculator leave out?

A spreadsheet model is a planning tool, not a bill. It ignores:

• New purchases. Add a Purchases column between D and E if you keep spending on the card.
• Fees. Late fees and annual fees can be added to the balance that accrues interest, depending on the agreement.
• Variable APRs. A variable APR moves with an index such as the prime rate, so the rate in row 12 may not be the rate in row 1. The CFPB explains the difference between fixed and variable APRs.
• Minimum interest charges that some cards apply when any interest is due.
• The grace period. If you pay the full statement balance by the due date, purchases usually accrue no interest at all, and the whole projection is moot. How credit card interest works explains when the grace period is lost and regained.

Related guides

• Credit card payoff calculator spreadsheet
• Credit card minimum payment calculator
• 0% APR credit card payoff calculator
• How to read a credit card statement