Create Multiple Credit Card Payoff Calculator in Excel Spreadsheet

Get FREE Advanced Excel Exercises with Solutions!

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.


Create Multiple Credit Card Payoff Calculator in Excel Spreadsheet: with Easy Steps

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

multiple credit card payoff calculator spreadsheet excel

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.

multiple credit card payoff calculator spreadsheet excel

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.

multiple credit card payoff calculator spreadsheet excel


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.

multiple credit card payoff calculator spreadsheet excel


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.

multiple credit card payoff calculator spreadsheet excel


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.

multiple credit card payoff calculator spreadsheet excel


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)

multiple credit card payoff calculator spreadsheet excel

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,D$6)))

Second Lowest Debt Calculation

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,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,E$6)))

3rd Lowest Debt Calculation

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

multiple credit card payoff calculator spreadsheet excel


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.

multiple credit card payoff calculator spreadsheet excel

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

multiple credit card payoff calculator spreadsheet excel

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

multiple credit card payoff calculator spreadsheet excel

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

Multiple Credit Card Payoff Calculator

Read More: How to Create a Credit Card Payoff Spreadsheet in Excel


Things to Remember

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

Download Practice Workbook

Download this workbook and practice while going through the article.


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.


Related Articles


<< Go Back to Credit Card Payoff CalculatorFinance TemplateExcel Templates

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Akib Bin Rashid
Akib Bin Rashid

AKIB BIN RASHID, a materials and metallurgical engineer, is passionate about delving into Excel and VBA programming. To him, programming is a valuable time-saving tool for managing data, files, and internet-related tasks. Proficient in MS Office, AutoCAD, Excel, and VBA, he goes beyond the fundamentals. Holding a B.Sc in Materials and Metallurgical Engineering from Bangladesh University of Engineering and Technology, MD AKIB has transitioned into a content development role. Specializing in creating technical content centred around Excel and... Read Full Bio

2 Comments
  1. Reply
    Ketayla Boudreau Oct 5, 2023 at 1:59 AM

    Having difficulty adding Debt 4 and Debt 5 formula is confusing me

    • Reply Avatar photo
      Osman Goni Ridwan Oct 5, 2023 at 5:02 PM

      Hi Ketayla!
      Thanks for the comment. There were some errors in the formula of Lowest Debt Calculation and I have fixed it. Also for your convenience, I have added the debt 4 in the workbook. Following the same method, you can create a formula yourself for debt 5, debt 6, and more. You can download the file from this link.
      Here is the image of the solution:

      Thanks for supporting us! If you face any other problem with any other topic then reply in the comment section.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo