Description: This article is intended to show you greatlakes borrowing login. From this article, you can get information about how to use the Solver program for Microsoft Excel to determine the optimal repayment schedule for multiple and unconsolidated loans.
I’m Matthew Nelson. Welcome to my demonstration on how to optimize a repayment schedule for multiple loans with different interest rates, different principles and different times to maturity. We’ll be using Microsoft Excel 2011.
There is a very common situation. Many loans are unsolved and you want to have a gold date to pay them off at the same time. This is very difficult to do using an amortization schedule or any normal techniques that don’t utilize the power of a modern computer. What we’re going to do is to set up a solver program that will do all the work for us.
Let’s get started. Here we have seven different loans from A to G. We have principal at column B and we have the annual interest rate at C. I’m going to assume 365 basis. We have the payment column which is a calculated field and next to it are our inputs. This could be any loans. The table is extensible. You could add rows to add more loans or delete them.
The payment will be determined by the solver, so it is a constant that the iterative program will determine. We do not need to concern ourselves with that. That is the hard part, we let the computer do the work for us. The next column we have the time to maturity in days and that will be determined on a calculation based on your inputs below.
The maturity will be equal for all of these, because we want to have them all paid off at the same time. We can see the change in days in column G. That is the difference between when the loan will be repaid based on the payment in column D and the desired maturity date. The way the solver works is that if the change in days is zero, that means our goal has been accomplished. We’ve determined the exact payment schedule that will make all the loans at the same time.
Let’s look at some of the inputs. First of all, we have today and we use the Excel function today. That’s easy and you could change it if you’d like. Let’s choose an easy data as our goal date. Let’s do December 31st of 2019. The change in days has already updated to be relative to the previous maturity from the last time. I ran the solver program. Here’s an interesting component to this model.
I have student loans in mind. If you have a monthly budget and you put in a number, that could be part of your planning for how you’re going to facilitate repayment these loans given your life circumstances. Before we get into the economic optimization and marginal revenue marginal cost, whatever your budget is and the loan repayment schedule you desire, that’s going to translate into a minimum salary after graduate school.
You can put in your effective tax rate below which is typically around 23% for professional salaries before the executive level. We’re going to calculate the salary needed based on a simple present value of the above inputs amplified by the tax rate. If that’s in the denominator, the salary will become larger to show you the gross salary before tax.
I’d like to show you how I got the optimal solution by looking at the change between different payment periods to see the benefits and cost of how long it’s going to take you to repay. There’ll be more interest, but there’ll also be more cash flow available in the present. I set up a profit maximization equation for marginal revenue equals marginal cost. That is usually the definition of profit maximization and turns out that.
That’s going to be pre-painting about one and a half years for the situation. That’s a more complex calculation. Let’s look at what this model can do. We put in a new goal and let’s change our monthly expenses of something different. Maybe it is forty to seventy eight.
Let’s keep the tax rate near there, but let’s change it to twenty two percent. It has not changed a lot yet. The change in days is still the same. The payment has been determined from the last time we were in the solver. Before we run the solver, let me discuss a little bit about this loan table below.
We see the seven loans with the starting principal and in each cell, we see the end of your balance. We see how much interest is calculated and they have a very complex conditional statement above.
It has some internal logic that it finds where the payment stops and everything’s paid off by using a very powerful combination of Excel functions, the match and offset function. If you don’t know how to use those, I highly recommend looking them up. They can greatly empower your models to work within the same column. If you have logic there, there’s no need to have another table somewhere else or to have it more cumbersome.
At the bottom, we can see the total amount we paid. That uses quite a long equation up there. Don’t be too scared of it. It’s looking up the details based on the name of the loan. Data gets pulled down there. Then the calculation is made.
We know the total amount of payments at the month previous to final repayment. We calculate the exact payment including the last month’s interest to get a perfectly exact figure rather than multiplying the total number of months.
That payment is in process by the monthly payment. That would not be accurate. By simply subtracting the total paid from the principal, we can determine how much interest was paid over the repayment period. Let’s get started and show you the solver. Java applet is unfortunately not native to the Mac version of Microsoft Excel in Windows because the windows architecture is very uniform. It is embedded in Excel. You can enable it by turning on the data analysis pack and other options.
Let me make that smaller and let’s bring up the solver program. It usually shows up under tools and then at the bottom solver. This isn’t going to come with normal prepackaged Microsoft Excel. You have to go to the internet, download this program and install it. It’s completely free. I think it’s an open source. It is very powerful. I apologize that it’s small here. Something about the Excel is rendering this Java program. That’s not native to itself. It comes out a little small.
What we want to do is to make all that change in days. All these days should end the same. We’ll know that’s true if this box which is the sum of this column G is zero . If all the numbers are positive and this is zero, that means that our admission has been accomplished and all the loans will finalize on the same day.
We put in some constraints. The first constraint is column D, the payments. They have to be positive. We’re paying the bank. The bank’s not paying us, so we want to make sure those are non-negative values. I set that column greater than or equal to zero and here’s another important thing. If you have covenants in your loan where there is a minimum payment stipulated and it can’t float around, this is the place to do that.
For example, on D5, this loan has a minimum contractual payment of ninety five dollars and seventy seven cents. Make sure that the solver program doesn’t change that to be below. The tip and the logic and the rest of the model will simply let that loan expire early, if the ninety five dollars will be too fast for it to end with the rest of the loans. Finally as I mentioned before, column G, the change in days needs to be equal to zero.
That’s the point of the whole model. We want to keep changing that payment so that when we do the time value of money calculations, the maturity date changes until it equals our goal repayment date. That’s the most important constraint in this model. You need to use nonlinear, because these are basically geometric sequences so use nonlinear. They’re the other ones about our simplex and evolutionary.
Let’s give it a whirl and see if it can figure out what we should pay the banks. Depending on the speed of your computer, this could take quite a while. Don’t be afraid of that. It gives you a little beep and it seems that we’re successful. We’ll go ahead and keep that solution. Press OK and remember our goal is December 31st of 2019. All these have changed to 2019. The change in days is the number of periods calculation. It’s one of the built in time value of money equations in Excel.
That’s how many days we’ll be repaying. Into payments, you will see long decimals coming up on your display. These are exact calculations. The computer has 14 decimal points of resolution. It’s accurate and powerful. These are the payments that we would make on each one of these loans.
This whole set of unconsolidated loans with different interest rate such as 7.9, 6.8, 2.8 and 7.5 is different. We need to adjust the payment periods to get all this zeroed and be successful and be debt-free in 2020.
Let’s look at what happened down at the loan table. These are the ending principal down at each year during the repayment period. Because that’s the end of the year and we’re paying it off in a little over seven years, there will be payments in your eight. The balance will be zero at the end. That requires a little bit of an additional calculation to figure out how much interest we paid and how much total money we paid to the bank for year eight which was a partial year.
That’s where this equation on the right came in. There’s built in logic so that the formula at each one of these sells. Through I27 has built in logic using the match and offset combination, that tells which year have final balance of principle. That shows in the year it will be paid off. They can use a conditional statement to perform the different calculation which will get us the exact amount of interest for that year.
If all works correctly, we have the total paid which is simply a sum of all the interest and that should equal the sum horizontally of the total interest paid by simply subtracting the total payment from the original principle. These two are equal. We have an exact model and we have a solid control about the quality of the data here. I’m very confident that that is working properly.
The next component that I’d like to show you is based on this repayment scenario with a monthly budget of forty two hundred dollars and seventy eight dollars and an effective payroll income tax rate of twenty two percent. We can calculate the salary we need to pay for this repayment and all of our monthly living expenses. Below similar calculations into getting the gross salary above is the optimal solution.
This is a theoretical repayment plan that is a constant. It is another solver solution. I’m not going to demonstrate all of that, because it’s a fairly tedious. Below what I did is that I did theoretical point estimates time to maturity of ten years, nine years, eight years and so forth and figured out from the computer. Every time I ran solver, it would tell you the total monthly payment that is for all seven loans and the interest, the total interest paid.
We have all these point estimates here and that starting to shape up into a kind of graph that we can extrapolate information from the marginal cost. In this scenario, I determined that marginal cost would be the difference between the total interest. That makes sense, because there’s a time value to money and that is known as interest. The difference between paying off in ten years and nine years is an additional interest payment to the banks of almost three thousand dollars.
That’s our point estimate for the marginal cost curve which is merely the change where the slope of the total costs. We take an average of average month and average year interest payment merely by dividing one into the other. The average yearly payment is obvious.
That’s important in economic decisions for production decisions. I don’t use it too much for demonstrate the size of the interest payment. It’s important on production to be aware of the average variable cost. That’s the row 37 and marginal revenue.
This was a little bit tricky, but basically I determined that for the change in value. That is the value to me, the loan holder would be C34. We see the payment minus B34. The payment changes. The interest is the cost or the time value of money but our revenue in this situation.
It’s a little bit stretched from some traditional models of marginal revenue, but our revenue is the change in cash flow. If our monthly payment shrinks, because we have a longer debt repayment period, we get a benefit of an additional in this case 51 dollars per month of money. That was not mailed to the bank, so that’s savings, that’s economic profit.
The way we figure out the solution is figuring out where marginal cost and marginal revenue equal one another. That will give us the time to maturity. That should be our goal to repay our loans scrolling over. That happens interesting Lane up soon. This was in a marginal cost and marginal revenue. They equal each other somewhere about a third of the way through in the second year. We can set up a simple high-low type of equation. We have program to save us a lot of head-scratching.
That is the intercept function. You can set an intercept and put in the arrays. We see the x-axis are the years and the marginal cost the interest payment being is on the y-axis. It will tell us the y-intercept is when we start and when time is zero and we do the same thing for row 38 with marginal revenue.
Second part to any linear equation is the intercept. That’s constant above. The other one is the slope or the rate of change. Excel makes things wonderful for us by including a slope function and we do it the same way by including two arrays inside the slope function. We get an equation. We have this slope times the year. That’s the rate of increase of the interest payments over time.
It will do millions of calculations per second down to resolution again of 14 decimal points and great news for us. We don’t have to set the things equal to each other or divide or anything. We run solver. We set the target objective cell to be 0 and it will tell us that the optimal number of periods is 1.3267. That’s a fraction of a year and that can be translated into months easily by using round functions and separating the remainder and changing into base 12.
When I did that earlier, the optimal monthly payment on the debt was 43 1/3 and that worked out to a salary needed of 115,000. This is very interesting. I think students will find this useful. That was the reason why I made this viewers. I hope that you can enjoy this passage of how to optimize your multiple loans.