Create Your Billing Statement Template Excel for Mortgage Effortlessly

Billing statement template excel for Mortgage

okay you're a Microsoft Excel and you want a formula that will allow you to calculate the monthly repayments on a loan so I'm borrowing 25 000 over five years with a nine percent interest rate now the function we're going to use for this calculation is the PMT function PMT has three mandatory arguments and two non-mandatory now the first mandatory argument is rate so what's our interest rate for the loan well that's stored here in C3 now because we're calculating monthly repayments we need to divide this value by 12. this is an annual interest rate we're working out monthly repayments there are 12 months in a year so we divide this by 12. comma in per number of periods so number of repayments now our loan term is five years so I'm going to reference C4 but again because we're calculating monthly repayments we need to return the number of months not the number of years so I need to times that amount times the 5 by 12. comma present value now the present value is the loan amount the amount that we need to repay now I'll come on to these two non-magitory arguments at the moment but for now we'll close the bracket and press enter so you can see that the monthly repayments are going to be 518.96 now we are getting a negative value there if you want a positive value then just put a minus sign in front of your formula and now we have a positive monthly payment amount now let's have a look at those non-mandatory arguments the first is future value so in other words what balance do you want to reach after the last payment now by default that's zero and that's probably the case for you you're looking to pay off the loan in full so we can leave that as it is comma Now type allows you to specify whether you're going to make your repayments at the end of the month or the beginning of the month if you make them at the beginning of the month then the repayment is slightly lower I'll show you that 515.10 rather than the default 518.96 we'll leave those two non-mandatory arguments in their default state our next step is to work out the total value of the loan so what is the total repayment value now to do that we would multiply the monthly payment value by the number of repayments so that would be equals the monthly payment amount times the loan term in years times 12. so 31 137.53 so the total interest would be the total amount paid minus the loan amount so you can see that we're actually paying six thousand one hundred thirty seven dollars and 53 cents in interest now with these calculations at hand we can start to play around with the variables in the loan to see if we can reduce the total interest paid so what I'm doing here is I'm borrowing three hundred thousand dollars for house purchase I've got an interest rate of six percent and I'm paying it over 30 years so my monthly payment is nearly one thousand eight hundred dollars but you can see that the total interest amount is huge it's actually more than the original amount that I've borrowed now obviously if I reduce the interest rate to something like five percent it does reduce the total interest paid now let's assume we still have a six percent interest rate the other thing that can really reduce the total interest paid is the loan term if I reduce this to 25 years you can see I'm paying a lot less interest over the term of the loan if I reduce it to 10 years may have an unaffordable monthly payment amount but look at the total interest paid okay that's all I wanted to cover in this particular video hopefully that's useful if it is please give me a thumbs up and subscribe and I'll see you next video foreign foreign foreign foreign