Hi, this is Wayne again with a topic “Calculate Loan Payments with Excel PMT Function”.
Foreign, I will show you how to use the PMT function or payment function in Microsoft, Excel to calculate the monthly payment for a loan. So here we have a spreadsheet with a list of different interest rates and different lengths of loans that people may be seeking and then also total loan amounts. So let’s say someone is planning to buy a house. That’S worth 548 thousand dollars. That’S the asking price they’re. Looking for a loan of 30 years in length and they’re, probably going to get seven percent interest rate, what would their monthly payment be Excel? Can help us calculate this? Let’S take a look so here on Cell E3, I’m going to click I’ll type, equals and then PMT for payment I’ll put in my left parenthesis and then you can see what Excel is looking for.
First, it’s looking for the rate. What’S the interest rate in this case, it’s here in column, B, cell B3, so I’ll click there and then at this point I could put in a comma and move on, but because the this interest rate is going to be paid over the course of a year With 12 monthly payments, I’m going to need to divide that by 12., next I’ll, put in my comma and next Excel is expecting the number of periods. That’S what nper stands for so the number of payment periods – and in this case it’s talking about years so 30.
So I’ll click here on Cell C3, but because each of those 30 years will have 12 payments one for each month because again, it’s a monthly payment system, we’re going to multiply C3 by 12. I’ll put in my comma and finally, the last required step in my Formula is the present value, in other words, the loan amount really, but that’s what PV stands for present value in this case 450. 000. So I’ll click on that there are two extra arguments you could put in if you want to, but we’re going to stop there I’ll put in my right parenthesis: tap enter on the keyboard board and I’ve calculated the monthly payment for this loan amount.
For this many years at this interest rate now, I could repeat those steps again for each of these other potential loans. But, as you probably know, I can just click on Cell E3 and then double click on that little green Square in the lower right corner of the cell. That’S the autofill handle I’ll, just double click on it, and it automatically applies this same formula all the way down this range of data so that easily we can calculate the monthly payments for these loans thanks for watching. I hope you found this tutorial to be helpful. If you did please like follow And subscribe, and when you do click the bell and you’ll be notified when I post another video, if you’d like to support my channel, consider clicking the thanks button below the video or you could support me through my patreon account or By buying Channel merch and you’ll see information about those options in the description below the video speaking of patreon, I want to give a quick shout out to my five dollar patreon supporters. Thank you so much much for all that you’ve done to support my channel. I really appreciate you .