Loan amortization schedules. Formulas. Interest and principal portions. Feel overwhelmed yet? Many Americans do when they’re discussing possible loans with bank officials. And why shouldn’t we? After all, finance is not everyone’s strong suit. Unless you have some academic grounding in finance and accounting, you really don’t get the whole picture. Unless someone were to lay it out simply. Let’s discuss loan amortization with a simple example. Imagine if you were in between paychecks or low on funds. You have Xfinity triple play, car payments, mortgage payments, and kids in college. You have a business, wages, expenses and utility bills that can’t wait until the end of the month. So you decide to take out a loan of $100,000 to help with your cash flows. Let’s take this situation further to see how loan amortization works.
The loan amortization formula looks fairly confusing at first glance:
This is the standard formula to calculate monthly payments. In the above equation:
The first thing you need to know is that your monthly payments on your loan are actually two parts. One portion goes back to paying back the principal amount of the loan. The other portion goes towards paying the interest on the loan. As the principal payments portion increases, the interest portion will go lower. You can find lots of amortization calculators on the internet. But it always pays off to know exactly how your loan works. Here’s how to calculate your loan amortization:
Let’s dive right into it and look at each step one by one.
There are 3 main things you need when calculating amortization. These are the principal amount of the loan, the interest rate, and the loan term. You also need the amount of the monthly payment amount. For the purpose of our example, the loan details are as follows:
When you put these values into the formula, you get the repayment amount
A = 100,000[0.005(1.005^360)/(1.005^360)-1] = $599.55
The actual amount of your payment will stay the same for the duration of the loan. But the principal and interest portions will change. The interest portion is high in the beginning, with a lesser percentage going back toward the principal amount. Now you have everything you need to calculate amortization for the duration of your loan. Let’s move on to the next step.
When calculating amortization, you need to understand that there are a number of variables. So, to avoid errors and make life easier, it’s best to do this on a spreadsheet. Make the 7 following columns:
There will be a total of 360 rows in this spreadsheet, one for each month. Sounds frightening? Don’t worry. You’ll only have to do the grunt work for the first 2 months’ calculations. Once you have entered the right equations, all you have to do is drag them down. There is an equation built into Microsoft Excel that can really help you with calculating amortization. Its called the PMT formula and it works when you input:
=PMT(r,n,p) or in our case =PMT(0.005,360,100000). This will give you the value of your amortization payments, which you can drag down the rows to autofill.
You will get a complete schedule of amortization for your loan. Let’s move on to the calculations.
Interest is equal to the principal times rate times loan period. Or I = P*r*t. In our case:
I = 100,000 * 0.005 * 360
The first step is to convert the yearly interest rate into a monthly rate. 6%/12 = 0.005% per month. The next thing to do is to multiply your principal amount with the monthly interest rate. $100,000 x 0.005% = $500. For the first month, $500 out of $599.55 will go toward interest.
Calculating the principal portion of the payment for the first month is simple.
The payment amount A comprises of the principal portion P and the interest portion I.
You need to subtract the interest portion from the loan repayment. In this case, A= I + P which means P = A – I =$599.55 – $500 = $99.55.
As you can see, the major part of your first repayment is towards interest. There is a smaller payment towards the principal outstanding, which will reduce interest in the next month.
Now its time to repeat the process, but with the second month. At the beginning of the second month, we have a lower amount of outstanding principal. $100,000 – $99.55 = $99,900.45. This is the opening principal amount for month 2. Now we calculate the interest portion of the payment in month 2 which is $99,900.45 x 0.005 = $499.50. As you can see the interest portion is lower in month 2 than in month 1.
Calculate the principal portion of the loan repayment in the same way as in month 1. Subtract the interest portion from the last step from the payment amount. $599.55 – $499.50 = $100.05. This amount is larger than the principal amount for the first month ($99.55).
This is where the spreadsheet will come in handy. Otherwise, you’d have to repeat the process manually for all 360 months. All you have to do is drag down the equations you used for the calculation in the first 2 months. The opening principal and the closing principal gets reduced with each payment period. By the end of the loan term, the interest portion declines to zero. The principal amount outstanding at the end of the 360th month should be zero.
Knowing how amortization works can come in handy with most financial decisions you take in your life. If you manage your finances well, you won’t have to haggle or argue with your bank. Just keep an eye on the elements of the loan we discussed above. Hopefully, you’re now in a better position to calculate your loan amortization for the entire term. Let us know if you found this article to be helpful.