Calculate monthly payment with PMT

- [Voiceover] In this workbook, 07-FinancialFunctions, the first worksheet is PMT. That's the name of a function in Excel that allows us to figure out the monthly payment, possibly quarterly payment on a loan. I've got two different situations in cell A2 and A3. We can essentially perform these together. I wanna know what the monthly payment is if I borrow $35,000 at 3.75% interest. And I want to pay this off over a four year period. That's 48 months.

The second example, I want to borrow $300,000 for a house. I want to pay it off over 30 years, 360 months, and the interest rate is four percent. How do we calculate the monthly payment? If you've ever done this manually, you know that it takes some time, you'll have to be raising a number to the minus 48th power or the minus 360th power. We don't have to worry about that, =pmt. The rate, the interest rate, now nearly always you will want to divided this by 12. In the case, it's in cell B6, divided by 12.

If this were a quarterly payment, it wouldn't be very likely in this situation but it could be, divide by four. That's the yearly interest rate that we're seeing in cell B6. Comma, the term in months. If somehow or other this meant four years, and if you saw a four there, you'd multiply it by 12. Here it's in months, that's good enough. Comma, and the amount that we're borrowing, here it is right here. Notice by the way, in the tip below it PV the phrase present value is what this is refereed to. I'll press Return.

There's the answer. $786 a month to pay off this loan. Principle and interest included. Now, I think a lot of times when you're using this you might want to use this elsewhere. If you plug this into the family budget or something like that, it's going to be easier to work with most of the time if this is not negative. And, that's what it's being displayed as right now. So we can either put a minus in front of the pmt or in front of the B8. Makes no difference. There it is. Now, as I press Return, now. Thats' the answer that we would want.

Now, since that's dealing with those three cells, we want a similar calculation over here. What's the monthly payment, principal and interest, on a $300,000 loan? Paying it off over 30 years month by month, interest rates four percent. Ans, we see the total over there. Functions same general idea. And of course, you can experiment with this pretty readily too. You might want to consider a 15 year loan, that would be 180. And, a lot of people are surprised when they see this, they initially think it's going to be about twice the cost here. But instead of $1,432 it's not going to be $2,800 or even close, it's $2,200.

That might give you some pause if you've never looked at that before. So, there it is for 15 years as we see this here. Now once you get familiar with this function, you might want to build a table. I've go one setup over on the right with some varying interest rates. It could be one of those times when varying interest rates are available. Perhaps at different credit unions or banks. So, let's highlight all these cells and with a single formula we can take care of all these at the same time. =pmt, left parentheses. What's the interest rate? For this cell, only it would be cell F5.

But, if we want to be able to copy this across here, and ultimately down, we always want our percentages to come out of row five. So lets press Command+T a few times. So, that we see the dollar sign only in front of the five, that's the row number. Divide that by 12, comma. And now the term, let's say it's consistent through all of these. This is for a house, it's going to be 360 months, that's in cell E5 right now. Just click there. Here we want this to be an absolute address, not change. At all.

In these cells, Command+T, comma. Now, the amount we are borrowing. I'm going to put in a leading minus. Because we want our numbers to appear as positive entities. And the amount we are borrowing for this particular cell is E6, but we want to be able to make that flexible enough. So, we want to change this using Command+T, change this into a mixed address, so that is always refers to data in Column E. So once again, pressing Command+T a few times. We want the dollar sign in front of the E, but not in front of the six.

If I were doing this for one cell, I'd press Return. But we've got all these cells highlighted, I'll press Control+Return, and there's our list there. For each one of the entries here, we're seeing what the monthly payment is going to be. Now remember, this is just principal and interest. Monthly payment, principal and interest only, based on these various rates. So, this number right here for example, is if we borrowed $225,000, four percent interest, paying it off over 30 years. That's the monthly payment. You could easily manipulate these numbers. This is a pretty broad price range, if you were more in the $300,000 range, you might start, for example, make this $300,000 and make this one be $305,000.

And, by highlighting two of these and then dragging downward, we'll have an interval here. A tighter interval then we saw before. And perhaps more inline with what you were thinking. It all depends. And we could clean up the dollar signs there, not too big of an issue. But, we see different prices here, and we can gauge, for example, the difference in interest rates and the differences in the amount that we're borrowing with regard to monthly payments and so on. So a valuable tool for calculating monthly payments, it's the PMT function.