Debt Snowball VS Debt Avalanche Method 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 the case of repaying multiple loans, one can take the help of Excel. Two standard methods to calculate repayments of multiple loans are the debt snowball method and the debt avalanche method. We can perform both in Excel. In this article, I will give an overview of debt snowball vs avalanche in Excel spreadsheet. After going through this article you will learn the methods and understand the concepts of debt snowball vs avalanche in Excel spreadsheet.


Debt Snowball Method in Excel Spreadsheet

This is the dataset for today’s article. We have two debts and will prepare the schedule for repaying these loans using both the debt snowball and debt avalanche methods. The minimum amount of payment for each loan is $200 per month and we have allotted $1000 per month for loan repayment. That means, after repaying a minimum amount for each loan, we will use the remaining fund to repay one of these loans.

debt snowball vs avalanche excel spreadsheet

First of all, we will look at the debt snowball method. In this method, we will repay a minimum amount to each loan every month. Then we will use the remaining fund to repay the loan that is of the lowest amount. In our case, it will be Debt-2. Let’s make the schedule step by step.

Steps:

  • First of all, repay the minimum amount for debt-1.
  • Then calculate the balance for this debt.

  • Then calculate the interest amount for this loan. To do so, write down the following formula in D7.
=E7*$K$4/12

debt snowball vs avalanche excel spreadsheet

  • You have to divide the yearly interest rate by 12 to get the monthly interest rate.
  • Then, press ENTER. Excel will calculate the interest amount.

  • Now, you have to repay debt-2. You allotted $1000 per month for repaying loans. Now, $200 was for debt-1. So you have $800 remaining for debt-2.
  • Pay $800 to debt-2 and calculate the balance and interest just like debt-1.

debt snowball vs avalanche excel spreadsheet

  • After that, again pay $200 for Feb-21.

  • Now, you have to calculate the remaining balance for debt-1. For this, go to E8 and write down the following formula-
=E7+D7-C8

debt snowball vs avalanche excel spreadsheet

  • The interest for Jan-21 was the month-end interest. You must pay this additional interest too. So this will add to the remaining balance.
  • Now, press ENTER. Excel will return the balance.

  • Then, again calculate the month-end interest for Feb-21.

debt snowball vs avalanche excel spreadsheet

  • After that, repay $800 to debt-2 and calculate the balance and month-end interest in a similar fashion.

  • Then, do the same operation for Jun-21. You can use the AutoFill feature to do the operation easily.

debt snowball vs avalanche excel spreadsheet

  • Now, you see that the remaining balance for debt-2 is $402.78 which is below $800. So this time, you will cancel out this debt. The payment should equal the sum of the Balance and the month-end interest of Jun-21. So, write down the following formula in F13.
=H12+G12

  • Then, press ENTER. Excel will calculate the payment for Jul-21.

debt snowball vs avalanche excel spreadsheet

  • Thus, debt-2 is paid off.

  • Now, for Jul-21, you have already paid $408.82. So you have another $591.18 available for this month. This amount will go as the payment for debt-1.

debt snowball vs avalanche excel spreadsheet

  • Calculate the balance and interest for Jul-21.

  • Now, you have only debt-1. So you can use all $1000 to repay the debt. Make a payment of $1000 up to Nov-21 and calculate the interest and balance.

debt snowball vs avalanche excel spreadsheet

  • On Dec-21, you have a balance of $779.23 and interest of $9.74 for payment. So the payment for Dec-21 will be the sum of these two amounts.
  • To calculate it, go to C18 and write down the following formula
=D17+E17

  • Now, press ENTER. Excel will return the payment.

debt snowball vs avalanche excel spreadsheet

  • Thus, debt-1 will also be paid off.

Read More: Make Credit Card Debt Reduction Calculator for Excel


Debt Avalanche Method in Excel Spreadsheet

Now, we will look at the debt snowball method. In this method, we will repay a minimum amount to each loan every month. Then we will use the remaining fund to repay the loan that has the highest interest rate. In our case, it will be Debt-2 again. Let’s make the schedule step by step.

Steps:

  • First of all, repay the minimum amount for debt-1.
  • Then calculate the balance for this debt.

debt snowball vs avalanche excel spreadsheet

  • Then calculate the interest amount for this loan. To do so, write down the following formula in D7.
=E7*$K$4/12

  • You have to divide the yearly interest rate by 12 to get the monthly interest rate.
  • Then, press ENTER. Excel will calculate the interest amount.

debt snowball vs avalanche excel spreadsheet

  • Now, you have to repay debt-2. You allotted $1000 per month for repaying loans. Now, $200 was for debt-1. So you have $800 remaining for debt-2.
  • Pay $800 to debt-2 and calculate the balance and interest just like debt-1.

  • After that, again pay $200 for Feb-21.

debt snowball vs avalanche excel spreadsheet

  • Now, you have to calculate the remaining balance for debt-1. For this, go to E8 and write down the following formula
=E7+D7-C8

  • The interest for Jan-21 was the month-end interest. You must pay this additional interest too. So this will add to the remaining balance.
  • Now, press ENTER. Excel will return the balance.

debt snowball vs avalanche excel spreadsheet

  • Then, again calculate the month-end interest for Feb-21.

  • After that, repay $800 to debt-2 and calculate the balance and month-end interest in a similar fashion.

debt snowball vs avalanche excel spreadsheet

  • Now, you see that the remaining balance for debt-2 is $402.78 which is below $800. So this time, you will cancel out this debt. The payment should equal the sum of the Balance and the month-end Interest of Jun-21. So, write down the following formula in F13.
=G12+H12

debt snowball vs avalanche excel spreadsheet

  • Then, press ENTER. Excel will calculate the payment for Jul-21.

  • Thus, debt-2 is paid off.

debt snowball vs avalanche excel spreadsheet

  • Now, for Jul-21, you have already paid $408.82. So you have another $591.18 available for this month. This amount will go as the payment for debt-1.

  • Calculate the balance and interest for Jul-21.

debt snowball vs avalanche excel spreadsheet

  • Now, you have only debt-1. So you can use all $1000 to repay the debt. Make a payment of $1000 up to Nov-21 and calculate the interest and balance.

  • On Dec-21, you have a balance of $779.23 and an interest of $9.74 for payment. So the payment for Dec-21 will be the sum of these two amounts.
  • To calculate it, go to C18 and write down the following formula.
=D17+E17

debt snowball vs avalanche excel spreadsheet

  • Now, press ENTER. Excel will return the payment.

  • Thus, debt-1 will also be paid off.

debt snowball vs avalanche excel spreadsheet

Read More: Create Pay off Credit Card Debt Calculator in Excel


Key Differences Between Debt Snowball and Debt Avalanche Method

  • In the debt snowball method, you pay a minimum amount to all the loans and use the remaining fund to repay the loan that has the lowest amount.
  • On the other hand, after paying a minimum amount for all the loans, you use the remaining fund to repay the highest interest loan.
  • The loan amount is important in the debt snowball method, irrespective of the interest rate whereas, the opposite scenario is true for the debt avalanche method.

Things to Remember

  • Do not forget to repay a minimum amount in all loans.
  • Decide the suitable method after analyzing the results from these methods.
  • Use the absolute reference to lock a cell using formulas in Excel.

Download Practice Workbook

Download this workbook and practice while going through the article.


Conclusion

In this article, I have given an overview of debt snowball vs avalanche in Excel spreadsheet. I hope it helps everyone. If you have any suggestions, ideas, or feedback, please feel free to comment below.


Related Articles

What is ExcelDemy?

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

Hi, this is MD Akib Bin Rashid. I completed my BSc in Engineering back in 2019. After that, I did an MBA. Then, I joined as an Excel and VBA Content Developer at SOFTEKO Digital. Being passionate about data analytics and finance, I will try to assist you with Excel

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo