Create Flawless Billing Statement in Excel for Financial Services Effortlessly

Billing statement in excel for Financial Services

[Music] in this Excel video 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,000 that's the asking price they're looking for a loan of 30 years in length and they're probably going to get 7% 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 this point I could put in a comma and move on but because 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 NP 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 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 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 $5 patreon supporters thank you so much for all that you've done to support my channel I really appreciate you