Join Curt Frye for an in-depth discussion in this video PPMT and IPMT: Calculating principal and interest per loan payment, part of Excel 2010: Financial Functions.
Calculating the amount of principal you've paid on a loan lets you determine the amount of equity you have in your purchase. Your equity is simply your down payment plus any loan principal paid, plus appreciation of the asset. Calculating the interest paid on a loan lets you find the amount of interest you pay in the year, which you can often right off in your tax return. I have set this workbook up so that we can calculate the principal component and interest component of an individual payment. The PPMT function, as you probably guessed, lets you calculate the principal and IPMT the interest.
So let's go through the arguments. The Rate is exactly what you would expect, the annual interest rate, and Period Identifies the period for which you want to identify the principal and interest components. Number of periods is the number of payments for the loan. In this case it's 360 which is 30 years of 12 monthly payments. Then there is Present value of $750,000. Now it's a negative number because it's an amount that you owe, then Future value is the amount that you want to end up either owing or having in surplus and in this case it's 0.
And then Type can be either 0 or 1 and that indicates whether you pay at the end of a period, which is 0 which is the default value, or at the beginning of a period, which is 1. With those arguments in place, let's go ahead and create the formulas. So we have the Principal component, which is =ppmt(, and then we have the rate. That is in cell c3, but because we're making monthly payments we need to divide that by 12, and then the period for which we want to perform our calculations is in cell c4.
Then the number of periods is in cell c5, present value is c6, and then you see that the future value and type arguments are in square brackets and the square brackets means that those arguments are optional. Excel will fill in default values if you don't put them in, but in this case our worksheet contains the values we need. So I'll go ahead and type c7, comma, and then c8, indicating that the payment comes at the end of the period. Type a right parenthesis. Just make sure everything is okay. It's especially important that we divide the interest rate by 12 which we are.
So I press Enter and we see that our principal component of the first payment is about $729. Now I'll move down using my keyboard to cell c13 and I create the IPMT function. And I'll go through this more quickly, because it uses exactly the same arguments. So we have IPMT and then the rates' in c3, again dividing by 12. Period is in c4. And then we have the number of periods. That's in c5. Present value c6, future value c7, and the type in c8.
Everything looks good. I'll press Tab so I don't scroll down and we see that the interest component is over $3800. Over the life of a loan, the principal component and interest component will change. You pay more interest at the beginning of the loan and more principal toward the end, even though individual payment amounts never vary. So for example, let's change the Period from 1 to 120 and that would be after 10 years of the 30 year loan.
So I type 120 into cell c4 and press Enter and we see that the principal component has gone up and the interest component has gone down fairly substantially. Now I'll move back up to cell C4 and type in 180 which is exactly the midpoint of the loan. See the principal is catching up, but it's not quite there to the interest. Then I'll change that period in c4 to 240 and we see that we're now paying off more principal interest than interest. And then for the last loan period, which is 360, we see that we are paying almost exclusively principal and practically no interest at all.
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. Always be sure to watch your interest expenses closely.
- Analyzing loans, payments, and interest
- Discovering the interest rate of an annuity
- Determining depreciation using the straight line, declining balance, double-declining balance, and other methods
- Calculating the future value of an investment with variable returns
- Finding the discount rate of a security
- Converting between fractional prices and decimal prices
- Determining the yield of securities that pay interest periodically
Skill Level Intermediate
Q: Where can I learn more about Excel formulas?
A: Discover more on this topic by visiting Excel formulas on lynda.com.