How to Use Excel to Calculate Principal and Interest
PPMT and IPMT: Calculating principal and interest per loan payment
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.
PPMT and IPMT: Calculating principal and interest per loan payment provides you with in-depth training on Business. Taught by Curt Frye as part of the Excel 2010: Financial Functions in Depth
Excel 2010: Managing Multiple Worksheets and Workbooks141,476 Views
Cleaning Up Your Excel 2010 Data281,169 Views
Excel 2010: Advanced Formatting Techniques247,005 Views
Excel 2010: Real-World Projects57,824 Views
Excel 2010: Working with Dates and Times95,251 Views
Illustrator CC 2013 One-on-One: Fundamentals1,678,359 Views
Excel 2010 Essential Training6,010,917 Views
Access 2013 Essential Training2,711,788 Views
Illustrator CC 2013 One-on-One: Mastery302,647 Views
Excel 2013 Essential Training6,279,953 Views