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.
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
- 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.
- 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
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- Then, do the same operation for Jun-21. You can use the Excel AutoFill tool to perform operations based on another cell.
- 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
- Then, press ENTER. Excel will calculate the payment for Jul-21.
- 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.
- 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.
- 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
- Now, press ENTER. Excel will return the payment.
- Thus, debt-1 will also be paid off.
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.
