How to Create Credit Card Payoff Calculator with Snowball in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.


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.


How to Create Credit Card Payoff Calculator Using Snowball Method in Excel: with Easy Steps

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

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


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


Download Practice Workbook


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.


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.
Rafiul Haq
Rafiul Haq

Rafiul Haq worked as an Excel and VBA Content Developer in Exceldemy for over two years and published almost 200 articles for the website. He is passionate about exploring new aspects of Excel and VBA. He received his Bachelor of Science in Mechanical and Production Engineering (MPE) from the Islamic University of Technology. Rafiul furthered his education by obtaining an MBA in Finance from the Institute of Business Administration (IBA) at the University of Dhaka. Apart from creating... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo