Calculating the amount of principal you’ve paid on a loan lets you determine the amount of equity you have in a purchase. You can also calculate interest paid.
- [Instructor] Calculating the amount of principal you've paid on a loan lets you determine the amount of equity you have in your purchase. That would be your down payment, plus loan principal and depreciation if any. You can also determine what your new monthly payment would be if you refinanced the loan at a specific interest rate for a given length of time. In this movie I will show you how to calculate the interest and principal components of loan payments. My sample file is Principal and Interest and that is an Excel file that you can find in the chapter one folder of the exercise files collection.
To calculate the interest and principal components of loan payments you need to know several pieces of information. The first is the loan rate. In this case we're assuming something like a short term construction loan, which has A typically high rate. In this case 10.25%. The loan will be paid off over 24 periods, those periods represent months, so we will need to divide the rate of 10.25% by 12. The value of the loan is $350,000. We want to pay it all the way down so the future value of the loan is zero.
And type refers to the whether the interest accumulates at the end of a period, which is typical and is zero, or if the interest accumulates at the beginning of a period which is much more rare and is indicated using the number one. With that information in place, we can calculate the interest and principal components of each payment. We'll start out in cell E4 and calculate the interest for the first payment. So in cell E4 I will type in equal sign and we're calculating interest so I will use the IPMT function.
This takes the arguments that I mention earlier, the first is the rate, that's in cell B3. I'm going to be copying this formula down so I want to make this an absolute reference, and unchanging reference. So I'll press the F4 key. Also, 10.25% is an annual rate, or at least I hope it's an annual rate. So I want to divide it by 12 to get the monthly rate for the loan. So I'll divide by 12 then a comma. The period is the period that we're calculating interest for and as you pay off principal, the interest that is owed on the next payment goes down.
So period is in D4. Period number one, then we'll copy it down and we'll do it for period number two and so on. Because we want that value to change, we'll leave it as a relative reference. Then I'll type a comma. The total number of periods is in cell B4, that's 24. And again, we want that cell reference to remain static and unchanging so I'll press F4 to make it an absolute reference. Comma, the present value of the loan is how much we're borrowing, that's $350,000 in B5 and again don't want that reference to change so I'll press F4.
Then a comma, the future value is zero, that's in B6, F4 to make it absolute, comma, and then we want the type to be zero for the interest to be accumulated at the end of the period so I'll click B7 and press F4 to make it an absolute reference. Type a right parenthesis to close out the argument list and enter and there I have the payment. Now one thing you'll notice is that the payment came out as a negative number. The reason is because this payment is an outflow from our account.
So it's seen as a negative in terms of our net worth. I prefer to see the numbers as positive numbers, so I'll make one edit. So I'll click cell E4, then go up to the formula bar and between the equal sign and the I in IPMT, I'll type a minus sign. And what that does is negates the output of the function. So I'll press enter and I get a positive number. Now we can do the same thing for principal and I'll move a bit more quickly because the arguments are exactly the same.
So I'll type equal, and then we're using the PPMT function and everything else is exactly the same. The rate is in B3 absolute reference followed by 12, comma. The period is in D4, comma. The total number of periods is in B4, we don't want that to change, so F4 for absolute reference, comma. Present value, same as before, B5 absolute, comma.
Future value, B6 absolute. And then when the payment is timed we have the type, F4, right parenthesis to close and oh, I forgot to use the minus sign to negate the output so we get a positive number, enter, and there we go. So we see that the interest component of the first payment is just about $3,000 and the principal is a little bit over $13,200. Now I can copy these formulas down to the remainder of my payment table.
I'll use my mouse wheel to scroll down and you can see that I have interest and principal components all the way down to period 24. So I'll scroll back up and select cells E4 and F4 and double click the fill handle, the bottom right corner of the selected range. Doing so copies the formulas down and if I scroll down, I can see that for the last period I'm paying $137 in interest and a little bit over $16,000 in principal.
Calculating the amount of principal and interest you have or will pay off on your loans reflects both the equity you have in your purchase and the amount of money you can write off on your taxes.
- 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