
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.
Most loans, whether between businesses or made by businesses to individual borrowers, are fully amortized. Fully amortized means that the monthly payments made over the term of the loan pay off the principal and all of the accrued interest. In Excel, you can calculate the monthly payments required to pay off a fully amortized loan by using the PMT function. I've set up this workbook so that it has all the arguments that we need for the PMT function to calculate our monthly payment. First is the Rate and this is exactly what you would expect, the annual interest rate.
Next is the Number of periods and that's just the number of payments that you need to make. In this case, it's 12 payments a year over 30 years for 360 payments. Then we have the Present value and that is the amount that you owe and you'll notice that it is represented as a negative number because, as I said, it's the amount that you are owing. Next is the Future value of the loan and this is the amount that you want to end up with after you make your payments. And because this is a fully amortized loan that value is 0. And then finally, you have the Type of payment you're going to be making and that can either be 0 or 1.
If it's 0, you make your payment at the end of a period, for example the last day of the month. If the Type is 1 then you're making your payment at the beginning of a period. That is the first day of the month. So 1 would be May 1st and 0 would be May 30th. So with that information in hand, we can create our PMT function and to do that, just type an equal sign and then pmt and left parenthesis and then we can start identifying the cells that contain the values.
The rate is in cell c3, but because it's an annual rate and we're going to be making monthly payments, we need to divide it by 12. So /12 and then a comma, then we have a number of periods which is in c4, comma, present value is in cell c5, comma, and then if you look at the tooltip underneath the formula that I'm entering, you'll see that the future value and type arguments are enclosed in square brackets. The square brackets indicate that those arguments are optional.
You don't have to provide a value for them. In this case, Excel would assume that the future value is 0 and that the type is also 0, but because this is the first time through I'll go ahead and add those arguments just for completeness. So future value is in c6, comma, and type is in c7. Type a right parenthesis and check over the formula. Everything looks good. Press Enter and we see that our monthly payment will be a little bit over $3100.
Now let's see how that would change if we made our payment at the beginning of a period instead of at the end. To do that, we change the Type from 0 to 1. So our payment in cell C10 is a little bit over $3100. Change the Type to 1 to pay at the beginning of the month and it goes down by about $14. Now that doesn't seem like a lot, but over 360 payments it comes out to a bit over $3750. The PMT function only calculates a monthly payment to cover principal and interest.
Many loans have other charges included such as property taxes and perhaps private mortgage insurance. So you should be sure to include those other fees and charges when you calculate your loan payments.
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, fullscreen, 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.