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.

Create Credit Card Payoff Calculator Excel Snowball


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.

Create Credit Card Payoff Calculator Excel Snowball 2


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.

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

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

Create Credit Card Payoff Calculator Excel Snowball 3

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

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

Create Credit Card Payoff Calculator Excel Snowball 4

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

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

Create Credit Card Payoff Calculator Excel Snowball 5

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

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

Create Credit Card Payoff Calculator Excel Snowball 6

Create Credit Card Payoff Calculator Excel Snowball 7


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.

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

Create Credit Card Payoff Calculator Excel Snowball 8

  • Next, type this formula in cell F11.

=D5-E11

Create Credit Card Payoff Calculator Excel Snowball 9

  • Afterward, type this formula in cell E12.

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

Create Credit Card Payoff Calculator Excel Snowball 10

Formula Breakdown

  • AND(((F11-$H$5+C12-D$6-C$6)<=0),D12=0)
    • Output: False.
  • (F11-$D$6-$H$5)<=0
    • Output: False.
  • IF(D12=0,$H$5-C12+D$6+C$6,D$6)
    • Output: 160.
  • Formula reduces to → IF(FALSE,F11,IF((F11-$D$6-$H$5)<=0,F11,160))
    • Output: 160.
  • Then, type another formula in cell F12.

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

Create Credit Card Payoff Calculator Excel Snowball 11

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

Create Credit Card Payoff Calculator Excel Snowball 12


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.

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

Create Credit Card Payoff Calculator Excel Snowball 13

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

=E5-G11

Create Credit Card Payoff Calculator Excel Snowball 14

  • After that, type another formula in cell G12.

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

Create Credit Card Payoff Calculator Excel Snowball 15

Formula Breakdown

  • AND(((H11-$H$5+E12-E$6-D$6-C$6)<=0),F12=0)
    • Output: False.
  • (H11-$E$6-$H$5)<=0
    • Output: False.
  • IF(F12=0,$H$5-E12+E$6+D$6+C$6,E$6)
    • Output: 250.
  • Formula reduces to → IF(FALSE,H11, IF(FALSE,H11,250))
    • Output: 250.
  • Then, type this formula in cell H12.

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

Create Credit Card Payoff Calculator Excel Snowball 16

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

Create Credit Card Payoff Calculator Excel Snowball 17

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

Payoff Calculator Excel Snowball 18


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.

Payoff Calculator Excel Snowball 19

  • 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

VBA Macro

VBA Code Breakdown

  • We have two Sub procedures in this VBA code. The first one is for hiding the rows that have 0 in the range G11:G130. We are using a For Each Next loop to go through all the cell ranges.
  • The last one just unhides all the rows in the active sheet.
  • 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).

Insert Button

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

Drag

  • There will be two buttons in the dataset.

Buttons 2

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

Assign Macro

  • Then, select “Hide_Rows” and press OK.

Hide Rows Macro

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

Assign Unhide Macro

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

Row Hidden

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

Row Unhide


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!

Rafiul Haq

Rafiul Haq

Hello! This is Rafiul. I am passionate about all things related to data and MS Excel is my favorite application. I want to make people's life easier by writing easy-to-follow and in-depth guides here at Exceldemy.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo