**Excel** is the most widely used tool for dealing with massive datasets. We can perform myriads of tasks of multiple dimensions in **Excel**. In this article, I will show how to create a multiple credit card payoff calculator spreadsheet in **Excel**.

**Table of Contents**hide

## Download Practice Workbook

Download this workbook and practice while going through the article.

## 5 Easy Steps to Create Multiple Credit Card Payoff Calculator in Excel Spreadsheet

This is the dataset for todayâ€™s article. We have 3 debts and we need to create a payoff schedule for these loans.

__Please note that we will follow the debt snowball method__. The minimum payments for each loan are in the dataset. We will additionally pay

**$500**per month to repay the loans.

As per the debt snowball method,

- First of all, we have to pay a minimum amount for each loan.
- Then, we will use the additional payment to pay off the lowest debt.
- After repaying the lowest debt, we will use the additional payment to pay off the 2nd lowest debt and so on.

Now, letâ€™s create the calculator step by step.

### Step 1: Calculate Payment of Each Debt for 1st Month

The first step is to calculate the payment of each debt for the 1st month. We will apply different formulas for each debt. Letâ€™s do it.

#### 1.1 Lowest Debt Calculation

First of all, we will determine the payment of the 1st month for the lowest debt. We will use **the IF function** to do so.

- Go to
**C11**and write down the following formula

`=IF(H5>=C5,C5,H5+C6)`

- Then, press
**ENTER**to get the output.

**Explanation**: Since **$500** < **$2700**, the logical statement is **FALSE**. So the output is **H5+H6** i.e **$550**.

#### 1.2 Second Lowest Debt Calculation

Now, we will create a formula for the payment of the 1st month for the 2nd lowest debt, that is **debt-2**. This time, we are going to use a combination of **the IF** and **AND functions**.

- Go to
**E11**and write down the following formula,

`=IF(H5>=C5+D5,D5,IF(AND(C11=C5,C11<>0),H5-D5+D6,D6))`

**Formula Breakdown**:

**AND(C11=C5,C11<>0)****Output: FALSE****IF(AND(C11=C5,C11<>0),H5-D5+D6,D6)****Output: 130****IF(H5>=C5+D5,D5,IF(AND(C11=C5,C11<>0),H5-D5+D6,D6))****IF(FALSE,5000,130)****Output: 130**

- Now, press
**ENTER**.**Excel**will calculate the payment.

#### 1.3 Last Debt Calculation

Now, itâ€™s time to calculate the payment of the 1st month for the last debt. We will use a similar formula this time.

- Go to
**G11**and write down the formula

`=IF(H5>=D5+E5+C5,E5,IF(AND(E11=D5, E11<>0),H5-D5-C5+E6,E6))`

**Formula Breakdown**:

**AND(E11=D5, E11<>0)****Output: FALSE****IF(AND(E11=D5, E11<>0),H5-D5-C5+E6,E6)****Output: 240****IF(H5>=D5+E5+C5,E5,IF(AND(E11=D5, E11<>0),H5-D5-C5+E6,E6))****IF(FALSE,6000,240)****Output: 240**

- Now press
**ENTER**to get the output.

### Step 2: Determine Remaining Balance of Each Debt After 1st Month

Next, we will determine the remaining balance of each debt after the 1st month. The formulas will be simpler in this case.

#### 2.1 Lowest Debt Calculation

Here, we will use a formula to determine the remaining balance of the lowest debt (that is debt-1) after the 1st month.

- Go to
**D11**and write down the following formula

`=IF(C5-C11<0,0,C5-C11)`

- Then, press
**ENTER**to get the output.

#### 2.2 Second Lowest Debt Calculation

Next comes **Debt-2**. We will simply subtract the 1st monthâ€™s payment from the total amount this time.

- Go to
**F11**and write down the following formula

`=D5-E11`

- Then, press
**ENTER**to get the output.

#### 2.3 Last Debt Calculation

Similar to **Debt-2**, we will calculate the remaining balance for **Debt-3**. The calculation is the same. So, I am showing the output here.

### Step 3: Calculate Payment of Each Debt for Next Months

After that, we will calculate the payment of each debt for subsequent months. This time, the formulas will be different from the ones for the 1st month.

#### 3.1 Lowest Debt Calculation

First, letâ€™s see the formula for the lowest debt i.e. **Debt-1**.

- Go to
**C12**and write down the following formula.

`=IF(D11-$H$5-$C$6<=0,$H$5+D11-$H$5,$H$5+$C$6)`

**Formula Breakdown**:

**D11-$H$5-$C$6<=0****Output: FALSE****IF(D11-$H$5-$C$6<=0,$H$5+D11-$H$5,$H$5+$C$6)****IF(FALSE,2150,550)****Output: 550**

- Now, press
**ENTER**to get the output.

#### 3.2 Second Lowest Debt Calculation

Next, we will calculate the payment for **Debt-2**. This time, we will use a combination of the AND and IF functions.

- Go to
**E12**and write down the formula

`=IF(AND(((F11-$H$5+C12-D$6-C$6)<=0),D12=0),F11,IF((F11-$D$6-$H$5)<=0,F11,IF(D12=0,$H$5-C12+D$6+C$6,D$6)))`

**Formula Breakdown**:

**IF(D12=0,$H$5-C12+D$6+C$6,D$6)****Output: 130****(F11-$D$6-$H$5)<=0****Output: FALSE****IF((F11-$D$6-$H$5)<=0,F11,IF(D12=0,$H$5-C12+D$6+C$6,D$6))****IF(FALSE,4870,130)****Output: 130****AND(((F11-$H$5+C12-D$6-C$6)<=0),D12=0)****Output: FALSE****IF(AND(((F11-$H$5+C12-D$6-C$6)<=0),D12=0),F11,IF((F11-$D$6-$H$5)<=0,F11,IF( D12=0,$H$5-C12+D$6+C$6,D$6)))****IF(FALSE,4870,130)****Output: 130**

- Now, press
**ENTER**to get the output.

#### 3.3 Last Debt Calculation

Now, I will create another formula that will give us the payment for the subsequent months for **Debt-3**.

- Go to
**G12**and write down the following formula

`=IF(AND(((H11-$H$5+E12-E$6-D$6-C$6)<=0),F12=0),H11, IF((H11-$E$6-$H$5)<=0,H11,IF(F12=0,$H$5-E12+E$6+D$6+C$6,E$6)))`

**Formula Breakdown**:

**IF(F12=0,$H$5-E12+E$6+D$6+C$6,E$6)****Output: 790****(H11-$E$6-$H$5)<=0****Output: FALSE****IF((H11-$E$6-$H$5)<=0,H11,IF(F12=0,$H$5-E12+E$6+D$6+C$6,E$6))****IF(FALSE,5760,790)****Output: 790****AND(((H11-$H$5+E12-E$6-D$6-C$6)<=0),F12=0)****Output: FALSE****=IF(AND(((H11-$H$5+E12-E$6-D$6-C$6)<=0),F12=0),H11, IF((H11-$E$6-$H$5)<=0,H11,IF(F12=0,$H$5-E12+E$6+D$6+C$6,E$6)))****IF(FALSE,5760,790)****Output: 790**

- Then, press
**ENTER**to get the output.

**Note: **This amount is not the correct one. However, ** the formula is correct**. Once we calculate the remaining balances (which are shown in the subsequent steps), we will get the accurate amount.

### Step 4: Determine Remaining Balance of Each Debt for Next Months

Now, we will calculate the remaining balances of these debts for the next few months. This time, we must consider the interest applied to the remaining balances.

#### 4.1 Lowest Debt Calculation

First, we will create a formula for **Debt-1**.

- Go to
**D12**and write down the formula

`=IF(D11-C12<=0,0,(D11-C12)*(1+($C$7/12)))`

**Formula Breakdown**:

**(D11-C12)*(1+($C$7/12)) â†’ ($C$7/12)**is the monthly interest rate.**Output: 1620****D11-C12<=0****Output: FALSE****=IF(D11-C12<=0,0,(D11-C12)*(1+($C$7/12)))****IF(FALSE,0,1620)****Output: 1620**

- Now, press
**ENTER**to get the output.

#### 4.2 Second Lowest Debt Calculation

This time, we will create a formula for **Debt-2**.

- Go to
**F12**and write down the formula

`=IF(F11-E12<0,0,(F11-E12)*(1+($D$7/12)))`

- Now, press
**ENTER**to get the output.

**Note: **As soon as you calculate the balance for **Debt-2**, you will get the accurate payment for **Debt-3**. Notice that __the payment has become $240 now__. Previously, it was

**$790**.

#### 4.3 Last Debt Calculation

Next, we will create a formula for **Debt-2**.

- Go to
**H12**and write down the formula

`=IF(H11-G12<0,0,(H11-G12)*(1+($E$7/12)))`

- Now, press
**ENTER**to get the output.

### Step 5: Use AutoFill to Create a Calculator

Finally, you must use the Fill Handle and **AutoFill feature** to complete the calculator.

- First of all, I am using the
**AutoFill feature**for**Payment**of**Debt-1**.

- The values are not correct. Once you
**AutoFill**all the columns, you will get the exact values.

## Things to Remember

- Do not forget to repay a minimum amount in all loans.
- Use
**the absolute reference**to lock a cell.

## Conclusion

In this article, I have shown you how to create a multiple credit card payoff calculator spreadsheet in **Excel**. I hope it helps everyone. If you have any suggestions, ideas, or feedback, please feel free to comment below. Please visit **Exceldemy** for more valuable articles like this.