How to Create Credit Card Payoff Calculator with Snowball in Excel

If you have any debts, you need to pay them back. When a borrower returns money by putting emphasis on the smallest amount, we call this technique the “snowball” method. This is applicable when we have more than one debt. This technique allows the repayment of the smaller debts faster. We will show you how to create a credit card payoff calculator in Excel using the snowball method.

Download Practice Workbook

Debt Snowball Method

We have already discussed the definition. Let us see an example of this method. If you have three outstanding debts of $10, $20, and $30, the minimum payment is $2 for each. Then, you will pay the total minimum amount of $6 and may choose to include additional money towards the $10 debt.

This additional $10 will be applied to the lowest debt. Whenever we finish paying the lowest debt, then this amount will be applied to the second-lowest debt. Thus, it creates a snowball effect to make the payment faster. Now, there is an opposite technique called the “avalanche” method, where we pay the debt in the largest amount first.

Step-by-Step Procedures to Create Credit Card Payoff Calculator Using Snowball Method in Excel

Here is a quick look at the credit card payoff calculator using the snowball method, which we will create by the end of this tutorial.

Step 1: Setting up the Essentials

For the first method, we will input information about the debt. Then, we will pay an additional amount per month, and there will be a one-time payment for our credit card payoff calculator.

• Firstly, there will be three debts for us and the information related to our debts is as follows:
  • Balance → Amount of outstanding debt.
  • Payment → The minimum amount that needs to be paid off per month.
  • Interest → Annual interest rate.
• Secondly, add another table for the additional payment:
  • Per Month → The amount, we will pay on top of the minimum payment.
  • One Time → If we pay an additional one-time payment we will input it here.
• Then, we will use these values to find the credit card payoff calculator in Excel using the snowball method.

Read More: How to Create a Credit Card Payoff Spreadsheet in Excel (2 Ways)

Step 2: Finding First Debt Payoff

In this step, we will find the credit card payoff from the first debt. We will use the IF function to do so.

• To begin with, type the following formula in cell C11.

• Here, if the total amount of the additional payment is more than that of the balance of the first debt, it will return the balance from debt 1. Else, it will add the values of the additional payment and the minimum payment for debt 1.

• Next, type another formula in cell D11 to find the balance.

• Next, we will create a formula to find the payment values from the second month.
• So, type this formula in cell C12.

• Next, we will type this formula in cell D12 to find the values of balance from the second month. Moreover, we have divided the yearly interest rate by 12 to find the monthly interest rate.

• Lastly, drag the cell range C12:D12 to AutoFill the formula into the rest of the cells.

Step 3: Calculating Second Debt Payoff

In this step, we will find the credit card payoff for the second debt.

• Firstly, type this formula in cell E11.

• Next, type this formula in cell F11.

• Afterward, type this formula in cell E12.

• Then, type another formula in cell F12.

• Finally, AutoFill the formula from the range E12:F12 to the other cells to find the values for debt 2.

Read More: How to Calculate Credit Card Interest in Excel (3 Easy Steps)

Step 4: Finding Third Debt Payoff

We will find the credit card payoff for the last debt using the snowball method in Excel.

• To begin with, type this formula in cell G11.

• Next, type another formula in cell H11 to find the first balance.

• After that, type another formula in cell G12.

• Then, type this formula in cell H12.

• Then, AutoFill the formula from the cell range G12:H12 to the rest of the cells.

• Now, we have filled in the formulas up to month 120 (10 years).

Step 5: Applying VBA to Hide Extra Rows

We can easily hide the extra rows using Excel VBA Macros. After that, we will use another VBA code to unhide the rows.

• Firstly, from the Developer tab → select Visual Basic. Alternatively, you can press ALT+F11 to do so.

• Then, type the following code in the Module.

Option Explicit
Sub Hide_Rows()
    Dim Cell_Range As Range
    Application.ScreenUpdating = False
        For Each Cell_Range In Range("G11:G130")
            If Cell_Range.Value = 0 Then
                Cell_Range.EntireRow.Hidden = True        
            Else
                Cell_Range.EntireRow.Hidden = False
            End If
        Next Cell_Range
    Application.ScreenUpdating = True
End Sub
Sub Unhide_Rows()
    Rows.EntireRow.Hidden = False
End Sub

• Then, Save and Close the Module.
• After that, we will insert two buttons to execute the codes.
• So, from the Developer tab → Insert → select Button (Form Control).

• It will change the cursor, then drag it to create a box. Afterward, repeat this process.

• There will be two buttons in the dataset.

• Now, we will assign macros to the buttons. So, right-click on “Button 1” and select “Assign Macro”.

• Then, select “Hide_Rows” and press OK.

• After that, do so for the second button and select “Unhide_Rows” and press OK.

• Then, we will change the button labels to “Hide” and “Unhide” respectively.
• Now, if we press the “Hide” button, the extra rows will be hidden.

• Moreover, if we click on the “Unhide” button, it will unhide the rows.

Conclusion

We have shown you five quick steps to create a credit card payoff calculator in Excel using the snowball method. If you face any problems regarding these methods or have any feedback for me, feel free to comment below. Moreover, you can visit our site ExcelDemy for more Excel-related articles. Thanks for reading, keep excelling!

Related Articles

• How to Convert Credit Card Statement to Excel (with Easy Steps)
• Create Multiple Credit Card Payoff Calculator in Excel Spreadsheet
• How to Reconcile Credit Card Statements in Excel