Start learning with our library of video tutorials taught by experts. Get started
Viewers: in countries Watching now:
In this course, author Curt Frye shows how to perform a wide range of financial calculations quickly and easily using the many financial functions found in Excel 2010. The course details dozens of functions for evaluating cash flows; calculating depreciation; determining rates of return, bond coupon dates, and security durations; and more.
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.
Find answers to the most frequently asked questions about Excel 2010: Financial Functions in Depth.
Here are the FAQs that matched your search "":
Sorry, there are no matches for your search ""—to search again, type in another word or phrase and click search.
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.