How to Make Credit Card Debt Reduction Calculator for Excel

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.

credit card debt reduction calculator for excel


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)

credit card debt reduction calculator for excel

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)

credit card debt reduction calculator for excel

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


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.

credit card debt reduction calculator for excel

  • 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

credit card debt reduction calculator for excel

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

credit card debt reduction calculator for excel

  • 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

credit card debt reduction calculator for excel

  • 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.

credit card debt reduction calculator for excel

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

credit card debt reduction calculator for excel

Thus you can create a credit card debt reduction calculator for excel.


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.


Related Articles

Nahian

Nahian

Hello, Nahian here! I do enjoy my efforts to help you understand some little basics on Microsoft Excel I've completed my graduation in Electrical & Electronic Engineering from BUET and I want to be a successful engineer in my life through intellect and hard-work, and that is the goal of my career.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo