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**

**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%**)

- Output:
**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**

- Output:
**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**

- Output:

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

## 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

**How to Calculate Current Portion Of Long Term Debt in Excel****How to Use Debt to Equity Ratio Formula in Excel (3 Examples)****Debt Snowball VS Debt Avalanche Method in Excel Spreadsheet****Debt to Income Ratio Calculator in Excel (Create with Easy Steps)****How to Create Debtors Ageing Report in Excel Format****How to Calculate Before Tax Cost of Debt in Excel (3 Easy Ways)**