Optimizing with Excel Solver: Student Loan Payments (2024)

If you’re like me — along with 44 million Americans in 2019— you probably have student loans. You have a different number of federal loans of different rates and principal amounts. You’re probably wondering, “what’s the best way to pay these?” Should you pay the smaller amounts first? Should you tackle the one with the highest interest rates first? A mix?

What we have here is a simple optimization problem of the following form.

• Goal: Minimize the total debt we owe by the end of year 2020 (to keep it simple)
• Constraints: The max payment we can make in each given month.
• Decision Variables: The amounts we pay towards each loan each month

By using Excel’s Solver Add-In we will find out that, indeed, focusing on paying off the remaining loan with the highest interest rate first is the best (meaning you pay the least in interests) strategy to pay off debt.

Before we start, these are some of the assumptions I made when building my spreadsheet for simplicity:

• Interests are calculated monthly: (Principal) * (Loan Interest Rate) / 12
• Interests are added/calculated before subtracting that month’s payment

Now let’s say that initially you have 3 loans that you have to pay with the following principal and interest rate:

• Loan 1: $1,000; 7.00%
• Loan 2: $10,000; 9.00%
• Loan 3: $20,000; 8.00%

Then let’s say that you’re constrained by your monthly budget, so you can only pay $1000 total each month towards loans.

Finally, remember our goal, is that by the end of 2020 we owe the least amount of money.

Using this starting information, I built the following spreadsheet:

Please note that for all cells, except those in green, their values were calculated as such:

• Month’s Amount == Previous Month’s Total-Payment
• Month’s Interests == Month’s Amount * Loan Rate / 12
• Month’s Total == Amount + Interests
• Payment == What you/Solver will choose to pay each month

The following columns were calculated as such:

• Total Payments = Sum of Payments in each month
• Max Total Payments = What’s your monthly max you can pay each month?
• Total Remaining = Sum of Totals minus Payments in the same month for all loans

Now that your spreadsheet is set up, we can add the information necessary for solver to work its magic. Let’s first bring up Solver…

For our problem, the Solver Parameters are the following:

• Set Objective: In our case, this is the last cell of Total Remaining since this is what we’re trying to minimize by the end of 2020.
• To: Since we’re minimizing what we owe by the end of the year, we choose min
• By Changing Variable Cells: Here we select the cells in blue. These are the cells Solver will fill in for us with the optimal payment values. You can select multiple cells by holding the Ctrl key as you select.
• Subject to Constraints: Our main constraints are that 1) Totals of each loan are ≥ 0, since we don’t want to overpay more than what we owe for each loan, and 2) The sum of payments each month must be less than our Max Total Payment value for that month.
• Make sure that Make Unconstrained Variable Non-Negative is selected so no decision variables are made negative.
• Select the Solving Method: Select Simplex LP, since this problem can be linearly solved (i.e. decision variables aren’t being multiplied with each other).

The set up may feel heavy, but once you click on Solve, the program saves you the hassle of having to solve this problem by hand.

Solver found what other debt advisers recommend: pay off the loan with the highest interest rate first. By doing so you save money by getting rid of the loan that costs you more per dollar. You may want to pay off Loan 1 first to feel like you’re making progress, but that’s just a mental trap.

However please note that the only reason Solver chose to pay Loan 1 on December 2020 is because our model calculates interests before taking into account that month’s payment. As a result, paying $1,000 to either Loan 1 or 3 would yield the same total remaining amount. If you were to run this model into year 2021, you will see that Solver, after paying off Loan 2, will focus on paying off Loan 3 first before moving on to Loan 1.

If you want to find out more about strategies for paying off debt, PBS’s Two Cents did a great video with much more detail and production cost.