It’s actually a pretty straightforward process to calculate credit card interest. The following are the steps you can use to calculate the interest for your credit cards using Microsoft Excel.
Step 1: Write down your APRs and balances.
Depending on the credit card agreement you have with your card issuer, you may have a single APR for your total balance or several APRs. It’s not uncommon for banks to charge different APRs to balance transfers, cash advances, and purchases, so you’ll need to grab a recent credit card statement and write down the various APRs you’re being charged and the portion of the balance they apply to. Once you’ve done that, go through Step 2 below for each APR and corresponding balance on your list.
Step 2: Calculate the accrued interest for each APR.
Once you’ve noted down your APRs and the balances applicable to them, run through the following procedure for each APR using Microsoft Excel.
a) Calculate the Daily Periodic Rate (DPR). The first step to calculating the total interest you’re being charged is to determine the Daily Periodic Rate you’re being charged. To do this, we’ll use the following formula in Excel:
APR / 365 days in a year = Daily Periodic Rate
Assuming an APR of 12.99%, you would type the following into a cell in your Excel spreadsheet (make sure to include the equal sign so Excel knows you’re asking it calculate something):
= 12.99% / 365
This calculation yields a Daily Periodic Rate of .0355890%.
b) Calculate the interest charged. Once you have the Daily Periodic Rate, the next step is to calculate the interest charged for the current billing period. We’ll do this using the following formula:
(Daily Periodic Rate * Days in Billing Period) * Balance Subject to APR = Interest Charged
Note that Days in Billing Period is the number of days in the month for which you’re calculating the interest. For example, if you’re working with a December billing statement, the Days in Billing Period would be 31 for the total days in the month.
Let’s continue with the same example as above and assume the same 12.99% APR, a 30-day billing period, and an outstanding credit card balance of $5,000. We already calculated the Daily Periodic Rate, so we’ll plug the result of that calculation, 30 for the Days in Billing Period, and $5,000 for the balance into Excel as follows:
= (.0355890% * 30) * $5,000
The answer should be $53.38.
Remember, if different portions of your credit card balance are subject to different APRs, the credit card company will calculate the accrued interest for each type of balance based on the appropriate APR. Make sure to do the above procedure for each APR and the portion of your balance it is applicable to.
Step 3: Add up the total interest.
Now that you’ve calculated the accrued interest for each APR and its corresponding balance, simply add them up to get the total interest charged.