The annual interest rate of credit cards is notoriously high. Although most consumers prefer to reduce or even eliminate the debt accumulated on them, it still remains a fairly common item in family budgets. Here's how to calculate a credit card interest rate using Excel, so you can estimate how much you would save by lowering or eliminating debt, or by switching to a lower-rate credit card. In this article, tips for saving money on interest are also described.
Steps
Part 1 of 3: Collect Data and Set Up an Excel Sheet
Step 1. Retrieve all credit card account details
Get the latest bank statements; on the top or bottom you can read the current balance, the minimum payout percentage and the annual interest rate for each card.
Step 2. Open the Microsoft Excel program and then a new workbook
Label cells A1 to A5 as follows: "Credit Card Name", "Initial Balance", "Interest Rate", "Minimum Payment" and "Interest Value" and "New Balance".
- The column at the end of the worksheet labeled "New Balance" should be equal to "Initial Balance" minus "Minimum Payment" plus "Interest Value".
- You will also need 12 lines for each credit card so that you can see how much paying the minimum will extend your debt, accumulating a lot of interest over time.
Step 3. Enter information by taking it from your bank statements
Use a column for each credit card; this means that the first card data is in column B listed in rows 1 to 5.
- If you can't find the minimum payout percentage, you can calculate it by dividing the minimum payout value by the final balance on your statement. You have to calculate the percentage, because the absolute value expressed in euros changes every month.
- Suppose your Visa credit card has an initial balance of € 1000, an annual interest rate of 18% and the minimum payment is 3% of the total.
- In this case, the minimum payment value is € 30 which results from the formula "= 1000 * 0.03".
Part 2 of 3: Calculate the Credit Card Interest Rate
Step 1. Calculate the amount of monthly interest
In each cell of row 6, in whose columns there is data, enter the formula: "= [Letter] 2 * [Letter] 3/12" and press the "Enter" key. For example, if you are typing the formula in cell B6, you must enter: "= B2 * B3 / 12" and confirm with "Enter". Copy the formula from cell B6 and paste it into all those that belong to row 6, in whose columns there are credit card data. Edit the formula so that the letter agrees with the column.
- Previously you entered the details of all credit cards in columns C, D, E and so on, based on how many you have. The copied formula automatically performs the calculations for each column.
- The annual interest rate is divided by 12 to get the monthly one. In the example described above, the formula calculates a monthly interest of € 15, starting with a balance of € 1000 and an annual rate of 18%.
Step 2. Compare interest with principal payments
When you have entered the data and obtained the results from the formulas, you can understand how much interest you are paying for each credit card and the amount of the minimum repayment of the principal. It is important to understand how much of your monthly payments is intended to cover interest and how much to repay the principal (and therefore to reduce debt). Your goal is to lower the monthly principal amount as much as possible, which you can do when the interest rate is lower.
To pay off debts as quickly as possible, transfer your bills to cards with lower rates or cards that offer a zero promotional rate. Read on to find out how to proceed
Step 3. Calculate the total of all monthly interest
You can create the formula using the "SUM" function. The syntax for the formula is "= SUM (B6: E6)", where E6 is the last cell of row 6 that contains a number. The total value represents the amount of money you pay out each month to pay interest on all credit cards.
Part 3 of 3: Save on Credit Card Interest
Step 1. Identify credit cards with the lowest rate or which do not charge fees
Search online for the keywords "low interest rate credit cards" or "zero interest promotional credit cards". Once you have been granted a credit card of this type, you can easily transfer your balance to it from the ones you are currently using and which charge higher interest.
- If you keep a high enough credit rating by honoring your debts on time, you get a lower interest rate.
- If you are a good customer, you can inquire at your bank and ask for a credit card with a much lower interest rate.
Step 2. Pay installments higher than the minimum amount
If you add € 10 to the minimum payment each month, you settle your debts faster and improve your credit rating. When you have settled an account, add this additional fee to the installments of another high-interest card, so you can pay off the debt quickly.
Remember to honor the minimum payments of all credit cards not to end up on the black list of bad payers, but at the same time try to add extra shares to accounts with higher interest rates.
Step 3. Take advantage of the "zero rate" offers
If you have a high rating as a good payer, you will probably receive emails with promotional offers for interest-free credit cards for the first year or more. Try to pay off any debts on these cards before the promotional period expires.
