Start learning with our library of video tutorials taught by experts. Get started
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 pay back a loan, each payment has a principal component and an interest component. Payments early in the loan's life consists mostly of paying down the interest. All payments late in the loan's life are almost entirely principal. You can determine the cumulative interest and principal you've paid on the loan by using this CUMIPMT function and CUMPRINC function. In this workbook, I have added the arguments that we need. The first is the annual interest rate and the second is the Number of periods and that's simply the number of payments in the loan.
In this case, I'm assuming a 30 year loan with 12 monthly payments per year, so it's a total of 360 and then we have the Present value of 615,000. Now in previous movies in this course you've seen that the present value is negative, because it's an amount that you owe. However, the way that these functions work we need to have the present value a positive number. So it's a little bit different, but hopefully you'll remember that. The Starting period is the first payment for which we want to start our calculations of cumulative interest or cumulative principal payment.
The Ending period is the last period and then Type is either 0 or 1. And this indicates when you make your payment. If it's 0, which is the default, then Excel assumes that you're making your payment at the end of a period, in this case the last day of the month, or May 30th. If it's 1, then that indicates the beginning of a period, so for example May 1. With all that information in place we can create our formulas. So for the principal, I'll =CUMPRINC( and now I can start typing in the cell references. So the Rate is in cell c3, but that's an annual rate.
And we are making monthly payments so we need to divide that by 12, twelve months in the year, then a comma, and the number of periods is in cell c4. Present value c5, the starting period is c6, and then a comma, the ending period is in c7, a comma, and the type is in cell c8. Type a right parenthesis and looking over the formula, I am dividing the interest rate by 12. Always important.
My intention in creating this formula with these arguments is to find the cumulative principal that I paid off from payments 1 through 12, or in the first year of my loan. So with that in mind I can press Enter and see that I have paid off over $9,279 in principal. And the number is displayed as a negative number in red because that is money that is going out of my account and we can do the same thing to find the total interest. I'll move through the formula more quickly this time because I use all the same arguments.
So it's equal, then cumulative interest payment, so CUMIPMT(, and then the rate is in cell c3, divided by 12 for monthly payments, then a comma, number of periods in c4, comma, present value c5, comma, starting period c6, comma, ending period c7, and then the type is in cell c8 and right parenthesis. Everything looks good. I'll press Tab so I don't make the screen scroll, and in the first year of this loan, I would have paid off almost $30,000 in interest.
Now let's see how those numbers change as we move through the loan's period. So let's say that we start after 10 years so it would be during year 11, so we would have period 121 and then the ending period would be 132. So I press Enter and then we see that the principal is over 15,000 and interest is almost 24. So the ratio is closer. Now let's go to 241 to 252 and press Enter and we see that the principal has outstripped the interest and we're paying almost twice as much principal as we are our interest.
And then in the last few months of the loan if we go from period 349 to period 360 we'll see that we're paying almost exclusively principal. Pay down principal on a loan gives you equity in the asset you're paying off and you might be able to right off the interest. You should check with your tax advisor to see how you can use those factors to your advantage.
Find answers to the most frequently asked questions about Excel 2010: Financial Functions in Depth.
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.