Credit Card Loan Interest Calculator: EMI in Excel

Quick Answer: A credit card loan or EMI conversion is priced like a small personal loan: a fixed monthly instalment on a reducing balance, plus any processing fee and, on Indian cards, any GST your statement shows on those charges. In Excel the instalment is =PMT(rate/12, months, -principal). Converting ₹60,000 over 12 months at 15% a year gives an EMI of ₹5,415.50 and about ₹4,986 of interest before fees and tax.

A loan on a credit card is not charged like the revolving balance. Whether it is an EMI conversion of a purchase, a pre-approved loan against your card limit, or a fixed-payment plan, the issuer quotes a rate and a term, and you repay equal instalments that include interest on the reducing principal. You can rebuild that schedule in a spreadsheet and check every line on the statement.

What does a credit card loan actually cost?

Before any formula, list the charges. The offer screen or the loan's terms and conditions give each one:

• Interest rate, quoted per year (sometimes per month). Check whether it is on a reducing balance or a flat rate; the difference is large, as shown below.
• Tenure, in months.
• Processing fee, either a fixed amount or a percentage of the principal, usually billed once in the first statement after the conversion.
• GST, on Indian cards. Your statement shows whether GST is applied to the interest and fee lines, and the rate it uses; copy both from there rather than assuming.
• Foreclosure or pre-closure charge if you repay early, again from the terms.
• Blocked limit. The principal is usually deducted from your available credit and released as each instalment's principal is repaid.

The Reserve Bank of India's credit card directions require issuers to show the principal, interest and any upfront discount before an EMI conversion, and to show those details separately on the bill. They also bar presenting an EMI that carries interest as "no-cost" or zero-interest (Master Direction on credit and debit cards, clause 6). That disclosure is what lets you check the numbers.

How is the EMI calculated?

The standard reducing-balance EMI formula is:

EMI = P × r × (1 + r)^n ÷ ((1 + r)^n − 1)

where P is the principal, r the monthly rate (annual rate ÷ 12) and n the number of months. Excel's PMT function computes exactly this; Microsoft's PMT reference explains that the rate and the number of periods must be in the same unit, so a monthly instalment uses the annual rate divided by 12.

Put the inputs in a small block:

The rate, fee and tenure here are an illustration, not any issuer's current pricing; rates on card loans vary by issuer and by customer, and they change.

Worked example: ₹60,000 over 12 months

Below the inputs, build one row per instalment. Interest for the month is =ROUND(Opening*$B$2/12,2), principal is =$B$6-Interest, and the closing balance is =Opening-Principal. If your statement shows GST on the interest line, add a GST column, =ROUND(Interest*$B$5,2), so the total billed each month is EMI plus GST.

Total interest over the year: ₹4,985.98. Each EMI is the same ₹5,415.50, but the interest inside it falls from ₹750 to ₹67, so any tax calculated on the interest falls with it. A processing fee normally appears once, on the first statement after the conversion.

Excel's IPMT gives any single month's interest without the table: =IPMT(B2/12, 4, B3, -B1) returns the fourth month's ₹572.85.

Flat rate or reducing balance: why does it matter?

Some offers quote a flat rate, where interest is charged on the original principal for the whole tenure. The same "15%" then means something else:

To convert a flat-rate EMI into its true annual rate, use Excel's RATE function: =RATE(12,-5750,60000)*12 returns about 26.6%. If an offer does not say which basis it uses, ask before converting.

Is a no-cost EMI really free?

A no-cost EMI usually works by giving you an upfront discount equal to the interest, so the instalments add up to the original price. The interest is still charged on the statement, with the discount shown against it. Two things can still cost money: the processing fee, and any GST line on the interest, which the discount may not cover. Check the statement after converting to see which lines were charged. Under the RBI direction quoted above, all three figures (principal, interest, discount) must be shown before you convert and itemised on the bill, so a spreadsheet with the same three columns tells you the true cost.

Does closing the loan early save money?

The schedule answers this directly. If you foreclose after the sixth instalment, the outstanding principal is the month 6 closing figure, ₹31,117.51, and the interest you avoid is the sum of months 7 to 12, about ₹1,375 in this example. Add one more column to the sheet for the foreclosure charge in your terms (a fixed amount or a percentage of the outstanding principal), plus any tax your statement applies to it, and compare:

• Saving: remaining interest, plus any tax that would have been charged on it.
• Cost: foreclosure charge, plus any tax on that charge.

Late in the tenure the saving is small because little interest remains, so a percentage-based charge can outweigh it. Early on the reverse is usually true. The same logic applies to a loan against the card limit, which is simply a longer schedule with a larger principal.

How do the EMI lines show up on a card statement?

Each month the statement typically carries several separate lines for the loan, which is what makes it hard to read at a glance:

1. The principal portion of the instalment.
2. The interest portion.
3. Tax lines such as GST, where your issuer applies them.
4. In the first month, the processing fee.
5. A summary box listing active EMIs with remaining tenure and principal outstanding.

Check each month's interest line against your schedule. A mismatch of a rupee is rounding; anything larger is worth raising with the issuer. Reading the full statement layout is covered for two Indian issuers in the guides to the ICICI credit card statement download and the SBI card statement.

Lining up twelve months of these lines by hand is slow. QuickBankConvert converts a credit card statement PDF into rows of date, description and signed amount; password-protected statement PDFs are supported, and you enter the password when uploading. Filter the description column for the loan reference and paste the lines next to your schedule. The credit card statement to Excel page describes the columns.

Fixed-payment plans on US cards

Some US issuers offer plans that split a purchase or part of the balance into equal monthly payments. Some charge a fixed monthly fee instead of interest. The comparison method is the same: list the payments and fees, then use RATE to find the equivalent annual rate and compare it with your purchase APR. =RATE(months, -monthly_payment_including_fee, amount)*12 does it. The plan's own terms in your online account state the fee; do not assume it is lower than the card's interest until you have run the numbers.

Revolving interest on the rest of the card is a separate calculation, described in how to calculate credit card interest.

Related guides

• Credit card payoff calculator spreadsheet
• How to read a credit card statement
• Find your credit card interest rate