Join Curt Frye for an in-depth discussion in this video Calculating interest and principal components of a loan payment, part of Excel 2007: Creating Business Budgets.
In the previous movie I showed you how to calculate the amount of a monthly payment on a fully amortized loan. In this movie I'll show you how to calculate the amount of principal and the amount of interest in each loan payment. Calculating the amount of principal you've paid on the loan enables you to determine the amount of equity you have in a purchase, which is your down payment plus any loan principal paid, plus appreciation of the property if any. Also what your new monthly payment would be if you refinance the loan at a specific interest rate for a given length of time. Calculating the interest paid on a loan enables you to find the amount of Interest you paid in a fiscal year, which you can often write off on your taxes.
To calculate the interest component of a payment you use the IPMT function. The IPMT function enables you to determine the amount of interest paid with a specific loan payment. In other words, for the loan payment number 1, on this loan, I can calculate the interest component by typing =IPMT and left parenthesis. The IPMT function has four required arguments and two optional arguments. The first argument is rate and that is the percentage rate on the loan.
In this case it's 4.50% and it is expressed as an annual percentage rate. I'll type that rates' cell reference, cell B5, in this cell and then I will press F4. Pressing F4 turns the cell reference from a relative reference, which can change, to an absolute reference, which cannot change. That is what the dollar sign in front of the column and in front of the row mean. Now because I'm doing monthly payments, I want to turn this into a monthly interest rate.
I divide that number by 12. The next number is the period, in other words which payment within the year or within the term that I'm looking at, which is in this case a year, which payment is that in that sequence. That number appears in cell A12, payment number 1, and then A13 payment number 2 and so on. To indicate that within the formula I type in A12, but I do not make it an absolute reference. If I turned A12 into an absolute reference by pressing F4, then that value wouldn't change and Excel would continue calculating the same value over-and-over because it would look at cell A12 instead of A13 for payment number 2, A14 for payment number 3 and so on.
The next argument is the number of periods. In this case it's monthly so I have the term of 30 years in cell B7. I don't want that to change so I will press F4 and because I have 12 payments a year, I will multiply that value by 12 to get 360. The last required argument is the present value and that's simply the loan principal, the amount you borrowed. That occurs in cell B3, so I'll type B3 and F4 to make it an absolute reference so it doesn't change.
The final two arguments are optional and you usually won't go with them. The first of those is fv . That is Future Value. The future value argument is used to indicate whether you have some money left over at the end, in other words a balloon payment, and the type argument indicates whether you make your payment at the end of a period. That is in arrears or at the beginning of a period, in advance. If you make your payment in advance you enter the number one. If you make your payment in arrears, which is the default, and pretty much every amortized loan allows interest to accumulate for a month before you make your first payment, you would enter 0 or you can leave it blank because it's the default choice.
I'm done here so I can type a right parenthesis, hit Enter, and Excel calculates the interest component. Now for the principal, it's almost exactly the same thing except I use the =PPMT function. So I have =PPMT, which is Principal Payment, and I enter the same arguments. I have the rate, which is in cell B5. I don't want that to change. So I press F4, divided by 12, because it's a monthly rate. I'm making 12 payments per year.
The period is in cell A12. Again don't change it to an absolute reference so that that element can vary as it's copied down the Excel table. The number of periods in the loan is B7, again 30 years. Make it into an absolute reference pressing F4 multiplied by 12, because we have 12 payments per year. Then finally we have the present value in cell B3, $500,000, F4 so it doesn't change, right parenthesis, look it over one last time and hit Enter and there I have both the interest and the principal components of the payment.
So that's how you determine the interest and principal components of individual payments. You can also determine the cumulative interest and principal you've paid on a loan by using the CUMIPMT and CUMPRINC functions. These two functions require you to enter the periods for which you want to calculate interest and principal paid. For example, if you barred on the same terms and wanted to determine how much interest and principal you'll pay over the first year, you can create these formulas. First we'll do the interest. So it's =CUMIPMT, Cumulative Interest Payment between two periods, the rate is in B5, absolute reference divided by 12, the number of periods in the loan is in cell B7, that's 30 years, absolute reference multiplied by 12, because we're making 12 payments per year. I have the present value which is in B3, again absolute reference, the start period, that's the first payment in the loan, so it's period number 1, the end period is 12, the 12th payment on the loan, and then the type.
In this function type is required. We'll assume that you're paying in arrears, in other words that the payment is due at the end of the month. So we'll type 0, right parenthesis, and we can see that at the end of the first year you will pay $22,335 minus one cent in interest. Here's the same thing for principal. =CUMPRINC for Principal, B5 which holds the rate divided by 12 to make it monthly, number of periods in B7, that's the number of years, multiplied by 12 because it's monthly. The present value is in cell B3, the start period is 1, first payment on the loan, 12 is the 12 monthly payment, that's at the end of the first year and the type of the loan, you're paying in arrears so that's 0.
Type right parenthesis, hit Tab, after I press Tab I can format this number using the Accounting format and you see that in the first year I paid a little bit over $8,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 purchases and the amount of money you can write off in your taxes. Be sure to watch your interest expenses closely.
- Tracking income and expenses by category and contract
- Using balance sheets
- Designing worksheets to assist decision making
- Creating income statements
- Calculating loans payments and interest
- Creating cell references to other worksheets
- Summarizing data in a chart
- Building alternative budget scenarios