In this chapter, you will build a loan amortization schedule. You’re building this to gain a better understanding of what’s called the time value of money. The basic principle of the time value of money is that a dollar today is worth more than a dollar tomorrow, and that interest can work for or against you.
Use this workbook for the chapter.
There is a chance you may need to take out a loan at some point, so we will do a simple example of an amortization schedule for a car loan. In this case, you will be buying a 1990 Honda Accord in North America. Fill out the input information as found in Figure 34.1.
Let’s start by understanding what the Scheduled payment for this type of loan would be. There’s a formula called PMT. The PMT function calculates the payment for a loan based on constant payments and a constant interest rate.
This formula results in a scheduled payment of $68.49 in order to start paying off this amount.
The Scheduled number of payments box provides an opportunity to understand how many payments it will actually end up being.
The Actual number of payments might vary based on the table below. The number of early payments will also be determined based on the table below. The last box would calculate the total interest that we would pay if we don’t pay anything extra or early.
To calculate the Total interest, you will use the Cumulative Interest formula (=CUMIPMT).
Now, navigate to enter in the first row of the schedule.
Now that the second row is built, you can copy the row down the number of periods that you have. In this case, there are 24 periods, so you will need 24 rows. You’ll notice that the beginning balance actually goes negative. The reason for that is we started paying extra here because the formula started adding for us. If you delete all the values from the beginning balance column, you’ll see that you end up paying off the loan and have an ending balance exactly at the end of period 24, which is what we had set in our loan calculation.
These are the basics of how to make a loan amortization schedule.
Let’s say you paid $100 extra in the first period. By doing so, you’ll notice that it actually goes negative in the 24th period. You finished paying off the loan in the 23rd period. And so, when you navigate to the actual number of payments, you can use the =COUNTIFS formula.
The number of early payments, also sometimes called extra payments, would use the =COUNT formula of the extra payments column. Because there only is one extra payment, you’ll see that cut off an extra period. If you were to pay another extra $100 in period two, you’ll notice that it cut off three total periods. At the end of this, at the bottom of the page, you’ll be able to see that, in fact, you finished paying it off in period 21.
Loans can be fairly straightforward in terms of how these calculations work. The important function that you need to know is the payment function (=PMT), where you can calculate how much you’ll owe given very simple inputs. You also need to understand the total amount of interest that you end up paying over the life of a loan.
In fact, if you look at what was done by paying $100 extra for those first two periods, you can see that the cumulative interest paid was $38.74 since you would not calculate anything after you’ve paid off the loan. If you calculate the difference between the total interest that you were supposed to pay over the life of the loan compared to the interest that you actually ended up paying down, it’s actually a 15% savings in interest. Now, it may not seem like that much when the dollars are relatively small like this, but over the course of a large loan, like a home loan, where the dollar amounts would potentially be in the hundreds of thousands, the amount of interest that you could save would be in the tens of thousands.
So, knowing how to make these calculations work and how to create one of these schedules is a vital skill to have for anyone that will borrow money at any point in their lives. That could be on a credit card, student loans, home loans, car loans, or any type of money borrowing. You should understand how financial institutions charge you money to use their funds.
Let’s learn a little bit about potential home loan valuation. Given just a very little bit of information, you can use the payment calculation to calculate what you might owe for a given home that you’re considering purchasing. (See Figure 34.4)
To find the payment, using the payment function, take the interest rate divided by the number of periods per year, the number of periods total, the present value of the loan, and the future value, which will be 0. You’ll see that the payment for this loan would be $2,395.63 per month, just to cover the loan.
You can also quickly calculate the amount of interest that you’ll pay over the life of this loan. Using the =CUMIPMT function, divide the rate by the number of periods per year, select the number of total periods and the present value of the loan, enter start period 1, ending period of 360, and select that you’ll pay at the beginning of the period. Over the course of this loan, you will pay more than the value of it originally if you keep the interest rate of 5.99% and keep your paying minimum amount of $2,395.63.
Just to show you how valuable this might be, make a copy of these numbers and change the interest rate from 5.99% to 4.99%. The difference in what you pay in interest is $90,000 savings. If you copy it again and bring it down to 3.99%, you save an additional $85,000 against the previous 4.99% interest rate. If you copy it one last time and change the interest to 2.99%, this saves us an additional $80,000 against the 3.99% interest. Against the original 5.99%, you’ve saved $256,000.
However, in this example, that still means that you’ve paid that amount for your home plus the interest that you paid over the life of the loan. So, you’ve paid for this home more than it’s worth. If we divide what you paid by the value of the home, you’ve paid for it 152%. You have quite the ownership in your property at that point.
Hopefully, this example serves to illustrate how important the time value of money is and how much you can end up paying or saving, because of course, these numbers could be applied to a savings account that you’re earning interest on over time and the opposite effect would be the case.
This content is provided to you freely by BYU-I Books.
Access it online or download it at https://books.byui.edu/bus_115_business_app/loan_amortization_sc.