Simplify Your Finances with an MS Excel Bill Sheet for Mortgage

Ms excel bill sheet for Mortgage

so last year i took out a 400 000 loan to buy this apartment and i spent ages negotiating with the bank because as you soon see a 0.3 change in the interest rate would mean i pay an additional three thousand dollars in interest payments on the flip side if i work hard to squeeze in as many as possible on the videos i can make extra payments as soon as possible and i can actually pay fifty thousand dollars less over the course of the loan interested to see how all this works let's get started hey friends welcome back channel if you're new here my name is jeff and today we're walking through step by step how to calculate your monthly mortgage payments given three things the amount you borrowed the interest rate you agree to and the loan repayment period i've linked this entire template down below so feel free to make a copy and follow along you only need to input some numbers and formulas in the cells highlighted in yellow and everything else will be automatic so i bought around 400 000 and the interest rate i got in the end was 3.3 and it was a 30-year mortgage one of the more common time frames please note the calculations we're going over today is for a fixed-rate mortgage where the 3.3 interest rate does not change over 30 years versus an adjustable rate or variable rate mortgage where the interest rate may change over the lifetime of the loan calculations are slightly different i'll touch on that at the end of the video to change the years into months simply multiply 30 by 12 and for monthly payment you actually want to use the pmt payment formula and since this rate is annual but you want the monthly payment you need to divide this by 12 right the number of periods is 360 30 years times 12 months and the present value is simply the loan amount now you see this number is negative which makes sense since you are paying back the bank or whoever loaned you the money but for the purposes of today's monthly mortgage payment calculation we need this to be positive so you simply add a negative sign in front if only getting out of debt were this easy in real life oh wait at this point you can actually already start to play around with these three numbers to see how monthly payment is affected fun activity if you watch ali's video on buying a house you can actually plug in the numbers from his video to see if he made a mistake comment down below with what you found out okay but you still don't know how much total interest you owe the bank and how much less you can pay if you make extra payments on top of monthly payments within those 30 years starting with cell b10 let's say the first payment due date is january 1st 2022 the beginning balance is 400 000 and the monthly payment since this does not change we can actually press function f4 to lock this number in place the interest is simply the beginning balance multiplied by the monthly interest rate which is 3.3 percent function f4 to lock it divided by 12 and the principal payment is simply the monthly payment minus the interest payment put another way interest plus principal equals monthly payment we'll skip over the extra payment for now the ending balance is the beginning balance minus the principal amount you've paid back so far minus any extra payment you made that month this is month one of paying back our loans and the total interest is just month one interest so far and the total principal is actually month one principal plus any extra payment we made because extra payment goes towards paying off your principal not the interest very important to remember and now pay special attention to row two first use the e date formula and reference the first payment due date and want to add one month each time the beginning balance is just the ending balance of last month the monthly payment we actually want to use the minimum function uh first field the monthly payment function f4 to lock second field is actually the beginning balance of this month plus the interest of this month you see why we do this towards the end of the video since the way we calculate interest principle and ending balance do not change highlight these cells highlight the row down below command or ctrl d to drag down for month number we simply add 1 to the cell above the total interest is this month's interest plus the total interest we've paid back so far the total principal is this month's principle plus any extra payments we made plus the total interest we paid back so far so three numbers there now we can highlight the second row drag it all the way down the row 369 command or control d and boom everything is now auto magically calculated you can see at the end of the 360th month or 30th year the ending balance is now zero let me quickly change the colors back real quick all right to double check our work we want to make sure the total cumulative principle we pay back is equal to our initial loan amount and that's why we use the maximum function on this row right here and yes we see the 400 000 is equal to our initial loan amount it's also interesting to see the total interest we need to pay back the bank so that's why we use the maximum function again on the total interest row as you can see the total interest payment we owe is a whopping 230 000 over the course of 30 years i'm so screwed to see how total interest is affected by the initial loan terms command or control c and then command and control shift v to paste without formatting and for difference uh you use total interest minus original interest and since these two are the same right now it's a zero if we got a better rate say three percent the new total interest we owe is two hundred and seven thousand dollars meaning we pay twenty three thousand dollars less if the rate were four point five percent just a tiny one point five percent increase right we would actually pay an additional ah almost a hundred thousand dollars more on the flip side let's see how making extra payments on top of your monthly payment will actually decrease the total interest you pay and your loan repayment period here i plan to take five thousand dollars of my annual bonus and make a one-off extra payment every year for the first 10 years you'll see the total principal doesn't change it's still 400 000 but i actually pay 53 000 less in interest payments and if you scroll all the way down i actually finish all my loan repayments uh by the 301st month so in around 25 years instead of 30. by paying back some extra money earlier i pay back less overall and i pay it back faster this is because an extra payment decreases the ending balance of that month right so the beginning balance of the next month is lower decreasing the interest amount you pay for that month so more of your monthly payment goes towards paying off your principal this is also a great time to bring up why we use the minimum function at the beginning if you scroll all the way down for this last payment this last payment amount is actually less than the initial monthly payment so if we didn't use the minimum function let's just copy and paste the initial monthly payment here you'll see we actually pay back more than necessary and i don't think your bank would tell you if you made this mistake last pro tip you can use this template to calculate any fixed rate loan it doesn't have to be a mortgage you just need to know the loan amount the interest rate and the loan repayment period let me know in the comments if you want an adjustable rate mortgage calculator it's basically this just over and over again see you on the next video in the meantime have a great one