How to Create Credit Card Payoff Calculator with Snowball in Excel

In this article, we will demonstrate how to create a credit card payoff calculator in Excel using the snowball method.

Debt Snowball Method

When a borrower has more than one debt and repays them by paying off the smallest debts first, we call this technique the “snowball” method. As an example, suppose you have three outstanding debts of $10, $20, and $30, with a minimum payment of $2 in each installment. Using the snowball method, you would pay the total minimum amount of $6, and allocate any additional repayment amounts towards the $10 debt first.

After repaying the the lowest debt, the second-lowest debt will be paid off next, creating a snowball effect. The opposite technique, where the largest debt is repaid first, is called the “avalanche” method.

How to Create a Credit Card Payoff Calculator Using the Snowball Method in Excel

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

Step 1 – Setting up the Essentials

First we’ll input information about the debt, the additional amount to be repaid each month, and a one-time payment. We’ll assume there are 3 debts to be repaid.

• Add a table, containing the following information for each of the 3 debts:
  • Balance → The total outstanding debt.
  • Payment → The minimum amount that needs to be paid off per month.
  • Interest → The annual interest rate.
• Add another table for repayment details with the following information:
  • Per Month → The amount we will pay on top of the minimum payment.
  • One Time → Any additional one-time payment we might make.

Step 2 – Calculating the First Debt Payoff

Now we find the repayment amount for the first debt using the IF function.

• Enter the following formula in cell C11:

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

• Enter the following formula in cell D11 to find the balance:

Next, we will create a formula to find the payment values for the second month.

• Enter the following formula in cell C12:

Next, we will find the Balance value for the second month. We divide the yearly interest rate by 12 to find the monthly interest rate.

• Enter the following formula in cell D12:

• Drag the cell range C12:D12 down to AutoFill the formula into the cells below .

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

Step 3 – Calculating the Second Debt Payment

Next, we will find the credit card payoff for the second debt.

• Enter this formula in cell E11:

• Enter this formula in cell F11:

• Enter this formula in cell E12:

• Enter this formula in cell F12:

• AutoFill the formulas from the range E12:F12 to the cells below to return the values for debt 2.

Step 4 –  Finding the Third Debt Payment

Now we find the credit card payoff for the last debt using the snowball method.

• Enter this formula in cell G11:

• Enter this formula in cell H11 to find the first balance:

• Enter this formula in cell G12:

• Enter this formula in cell H12:

• AutoFill the formula from the cell range G12:H12 to the cells below.

• Fill the formulas down 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.

• From the Developer tab → select Visual Basic (or press ALT+F11).
• Click Insert, then select Module.

• Enter the following code in the Module window that opens:

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

• Save and Close the Module.

Now we’ll insert two buttons to execute the macros.

• From the Developer tab → Insert → select Button (under Form Controls).

• Drag the cursor to create a box.
• Repeat this process to create a second box.

We have two buttons in the dataset.

Now, we will assign macros to the buttons.

• Right-click on “Button 1” and select Assign Macro.

• Select Hide_Rows and click OK.

• For the second button, select Unhide_Rows and click OK.

• Change the button labels to Hide and Unhide respectively.

Now, if we press the Hide button, the extra rows will be hidden.

Click on the Unhide button to unhide the rows.

Our snowball credit card payoff calculator is complete.

Download Practice Workbook

Related Articles

• Create Multiple Credit Card Payoff Calculator in Excel Spreadsheet
• Make Credit Card Payoff Calculator with Amortization in Excel

<< Go Back to Credit Card Payoff Calculator | Finance Template | Excel Templates