PMT: Calculating a loan payment

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.