Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
When you take out a loan, either you or your accountant might need to know how much interest you paid for a given period. For example if your business borrows $250,000 for some facility upgrades, you might be able to write off some or all of the interest paid for a given month, quarter, or year. You can discover how much interest you paid on a specific loan payment by using the ISPMT function. I've set up the sample workbook with all of the arguments that we need for the ISPMT function. We have the Rate, which is simply the annual interest rate on the loan, then we have the Period, which is the payment for which we're interested in calculating the interest component. So this would be period 14 which is the second payment of the second year.
Then we have the Number of periods and the Number of periods is the number of payments throughout the life of the loan. In this case we have 12 monthly payments a year over two years for the total of 24. And then finally, we have the Present value, which is the amount that we've borrowed. It's 250,000. And with all those arguments in place we can create our formula. So type =ISPMT( and then first we have the rate which is in cell C3.
Now that's an annual rate and we're making monthly payments, so we can type /12 to divide the value in C3 by 12, and then type comma and the period is in C4, comma, number of periods is in C5, and then the present value of the loan is in C6. Type a right parenthesis and then look through all of the arguments. Everything looks to be good. We've divided the interest rate by 12 for monthly payments and then press Enter.
And we see that the interest paid for that payment was about $695. Now if we change it to the last period by typing 24 in cell C4 and press Enter, we see that we paid no interest. It was exclusively principal. You can use the ISPMT function to find out exactly how much of a payment was interest. You should check with your tax professional to find out if you can write off the interests you pay. If so, the ISPMT function will tell you how much that is.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 80536 Viewers
80 Video lessons · 132805 Viewers
52 Video lessons · 66334 Viewers
59 Video lessons · 52099 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.