Paying additional principal per month can save significant interest payments over the life of the loan. In this video, learn how to calculate those savings.
- [Instructor] In the previous movie, I created formulas to calculate the principle and interest components of a loan. Many loans let you pay additional principle per month, which reduces the total amount of interest you owe. In this movie, I'll show you how to calculate your interest savings from paying at extra principle. My sample file is the ExtraPrinciple workbook and you can find it in the Chapter01 folder of the Exercise Files collection. In this worksheet, I have all of the information that I need for my calculations. At the top, I have the rate, the annual percentage rate of this loan.
In this case, we're assuming a 30 year standard home loan with a rate of 4 3/4%. We have 12 payments per year, 360 total payments, and a loan amount of $570,000. To determine the effect of extra principle, I also have the base payment and any extra principle. At the end of the movie, I will calculate the total interest for the base loan, and also total interest paid for the loan where we pay additional principle per month.
I have recreated the same calculations that I did in the previous movie for this loan. So, I see that I have interest payments and the principle payments for the standard 360 payment course. And if I scroll down to the bottom of the list on the left, you'll see that at payment 360, we're paying $11.72 in interest and 2,961.67 in principle. And here on the right are the row headers for the bottom rows of the table where we track additional principle.
So, I'll go back up to the top. With all that information in place, I can create formulas that will calculate the interest and the principle components for payments, allowing for the fact that I might choose to pay extra principle per month. So, in cell F8, I will create a formula to calculate the interest component of my next payment. So, I'll type an equal sign and the remaining principle. I want to multiply that by the interest rate, which is the cell B1.
I want that reference to the interest rate to remain static, I don't want it to change, so I'll press F4 to make it an absolute reference. But I also don't want to apply a full year's worth of interest to every single payment. I need to divide by the number of payments per year, which is in cell B2, and also make that an absolute reference. So, I have my remaining principle multiplied by the annual interest rate divided by the number of periods per year. Looks good, so I'll press Tab and I see my interest payment.
I'm not paying any extra principle yet, so, as a sanity check, I can make sure that my result here in F8 is exactly the same as the corresponding payment for period one in cell B7. I see the same result, so I know I got my calculation right. To calculate the principle, I need to do two things. I need to subtract the interest from the base payment and also add any extra principle that I might pay. So, in cell G8, I'll type an equal sign.
And the principle is the base payment, which is in H1. And I don't want that reference to change, so F4 to make it absolute. Minus the interest component in F8 plus any extra principle that I pay, and that is in H2. The H2 reference will stay static, so I'll press F4 to make it an absolute reference, and Tab. So, I have my principle component of $717.14, which matches the principle for period one for the loan where we're not paying any extra principle.
And the remaining principle on the loan is just a matter of subtracting the principle we paid from the previous period's remaining principle. So, that's equal, remaining principle in H7 minus the principle payment in G8, and Enter, there we go. We see that we have a bit over $569,000 left. Now I can copy all of these payments down by copying the formulas from period one, which is row eight of the worksheet.
So, I will select cells F8 through H8 and double click the fill handle at the bottom right corner. And I know I'm in the right place when my mouse pointer changes to a black crosshairs. I'll double click, looks like everything filled in, but I will scroll down to the bottom of the table and, if I'm lucky, the last row will match the last row from my other table. So, yep, I have my interest of 11.72, principle of 2,961.67, and my remaining principle is zero.
Good, that means the base calculations are correct. Now I can go back up to the top and see how extra principle affects the payments. So, let's say that I have a little bit of extra money per month and I can pay $200 in extra principle. So, in H2, I will type the number 200, but, before I press Enter, I want you to remember that in cell G8 we have a principle payment of 717.14. When I press Enter, that amount goes up by $200, and you see that the remaining principle balance has gone down and everything else appears to be correct.
There is one aspect of paying additional principle that you need to watch out for. If we scroll down, we'll see that remaining principle goes negative after a while. So, that means when we're calculating the amount of interest that we've actually paid, we need to ignore any rows where the remaining principle is below zero. So, I will go back up to the top and we can now calculate the total interest paid for each of the loans. If I click in cell B4, I can calculate the sum of all of the interest paid for the loan where we don't pay any additional principle by typing the equal sign, then sum, followed by a left parentheses, then I'll click cell B7, which is my first interest payment, and, then, to select the rest of the cells in this column that contain data, I will press Control Shift down arrow.
So, I'm holding down Control and Shift and pressing the down arrow key. And I've selected everything to the bottom of the data range. I will type a right parentheses up in the formula bar to close out the formulas argument list, and Enter, and scroll up, and I see that the total interest paid on this loan is over $500,000, so quite a lot. Now let's take a look at the total interest paid for the loan where we're paying $200 of extra principle.
So, I'll click in cell H4, type an equal sign. But one thing to remember is that we have some negative remaining principle values. So, we want to exclude the interest cell from any row where remaining principle is negative, because, in effect, we've already paid off the loan. So, the formula that I use is sumif. So, I've already typed an equal sign in H4, sumif followed by a left parentheses, and then the interest column, which starts at F7, and then Control Shift down arrow.
Then back in the formula bar, I need to do one more thing. So, I'll click to the right of the range, then a comma, and the criteria is double quotes greater than zero, double quotes, and right parentheses, and Enter. And if I scroll back up to the top, I can see that by paying additional principle of $200 per month, I have saved about $73,000 in interest payments. And you can experiment to see how you can save the most interest.
For example, in H2, if I change the extra principle to $500 per month, then the interest bill goes down substantially, about $150,000. Calculating the amount of principle and interest you have or will pay off on your loans reflects both the equity you have in your purchases and the amount of money you can write off on your taxes. Also remember that many loans let you pay additional principle, which can greatly reduce the total interest paid on a loan.
- Calculating the effect of interest rates and inflation
- Finding the arithmetic and geometric means of growth rates
- Calculating the future and present value of an investment
- Calculating loan payments for a fully amortized loan
- Calculating the effect of paying extra principal with each payment
- Finding the number of periods required to meet an investment goal
- Calculating net present value and internal rate of return
- Building a cash tracking worksheet
- Visualizing cash flows using a waterfall chart