Meraz Al Nahian
The article will show you how to make a credit card debt reduction calculator for Excel. In our daily life, we should spend our money in a calculative way. If we don’t keep track of our expenses, we might end up losing all the money before the month ends, and hence, we become dependent on other people’s loans. However, the credit card has become a powerful medium of money transaction in our daily life as we can buy things using it and pay the dues of those products in consecutive months. We can use Excel effectively to keep track of credit card debts by using a debt reduction calculator and understand how we should use our earnings to reduce the debts
Download Practice Workbook
3 Steps to Make Credit Card Debt Reduction Calculator for Excel
Suppose, you have 5 credit cards and you use them to mitigate your expenses. In the following picture, you will see the necessary data to calculate credit card debt reduction.
Step 1: Inserting Credit Card Debt Data
First, we need to insert the data of necessary parameters to calculate credit card debt reduction.
- Put the Credit Card Balance, Interest Rate, and Monthly Payment in the Excel sheet.
- After that, use the following formula to calculate the Total Balance.
=SUM(C6:C10)
The formula uses the SUM function to return the Total Balance of all the Credit Cards.
- Similarly, use the following formula to calculate the Total Monthly Payments.
=SUM(E6:E10)
- Thereafter, insert this formula in cell C13 to determine the Average Interest Rate.
=SUMPRODUCT($C$6:$C$10,$D$6:$D$10)/SUM($C$6:$C$10)
The formula here uses the SUMPRODUCT function to calculate the summation of the product between the Balance and the Interest Rate. Then we divide this resultant value by the Total Balance to calculate the Average Interest Rate.
Read More: Debt Service Coverage Ratio Formula in Excel
Similar Readings
- How to Find Intersection of Two Lists in Excel (3 Easy Methods)
- Debt to Income Ratio Calculator in Excel (Create with Easy Steps)
- Create Debtors Ageing Report in Excel Format
- How to Calculate Before Tax Cost of Debt in Excel (3 Easy Ways)
Step 2: Summarizing Credit Card Debts
In the following steps, you will see the process of rearranging the parameters. Say your Repayment Balance is 900 dollars. We will be doing this process in a new sheet.
- First, we need to insert some data from the previous sheet which was named Debt Data.
- Similarly, we insert the Average Interest Rate and Total Monthly Payments.
- After that, type the following formula to calculate the Average Monthly Interest.
=C6*C7/12
- Next, write down the following formula to get the Extra Repayments
=C11-C10
Read More: How to Calculate Annual Debt Service in Excel (3 Ideal Examples)
Step 3: Calculating the Credit Card Debt Reduction Amount
This is the final step where we are going to show you the reduced value that you need to repay from your corresponding credit card. You can repay the money in these possible ways: you can either pay the Higher Interest Balance or the Lowest Balance first according to your convenience.
- To pay the Higher Interest Balance first, use the following formula. Here I’m doing this calculation in another new sheet named reduction calculator.
=INDEX('Debt Data'!B6:E11, MATCH(MAX('Debt Data'!D6:D11),'Debt Data'!D6:D11, 0), 1)
Here, we used the INDEX and MATCH functions in order to insert the payable debts of the credit card.
- MAX(‘Debt Data’!D6:D11) —-> returns the Maximum Interest Rate from the Debt Data sheet with the help of the MAX function.
- Output: 12 (12%)
- MATCH(MAX(‘Debt Data’!D6:D11),’Debt Data’!D6:D11, 0) —-> then turns into
- MATCH(0.12,’Debt Data’!D6:D11, 0) —-> which provides the position of 12 in the D6:D11 column of the Debt Data sheet.
- Output: 1
- INDEX(‘Debt Data’!B6:E11, MATCH(MAX(‘Debt Data’!D6:D11),’Debt Data’!D6:D11, 0), 1) —-> now becomes
- INDEX(‘Debt Data’!B6:E11, 1, 1) —-> results into
- Output: Card 1
- After that, we use the same formula but change the [column_num] of the INDEX function to 2 to get the Balance
- Similarly, change the [column_num] to 3 and 4 to get the Interest Rate and Monthly Payment.
- After that, type the following formula to determine the New Payment.
=E6+Summary!C13
- If you choose to repay the Lowest Balance first, first determine the credit card that has the Lowest Balance using the following formula.
=INDEX('Debt Data'!B6:E11, MATCH(MIN('Debt Data'!C6:C11),'Debt Data'!C6:C11, 0), 1)
This formula is pretty similar to the one that we use to determine the Creditor of the Highest Interest Rate. It just has the MIN function instead of the MAX function.
The other data can be found by changing the [column_num].
- After that, type the following formula to determine the New Payment.
=E10+Summary!C13
Thus you can create a credit card debt reduction calculator for excel.
Read More: How to Use ISNUMBER & MATCH Function in Excel (2 Ways)
Practice Section
Here, I’m giving you the dataset of this article so that you can practice these methods on your own.
Conclusion
That’s all for the article. I’ve tried to show you the way of creating credit card debt deduction calculation in an as simple way as possible. If you have any better methods or questions or feedback regarding this article, please share them in the comment box. This will help me enrich my upcoming articles. For more queries, kindly visit our website ExcelDemy.
