Hands up if this video gave you even more adulting anxiety 🙋🏻 TIMESTAMPS 00:00 How to Calculate Monthly Payments 00:33 Intro to Mortgage Calculator Template 00:58 Monthly Payment for Fixed-Rate Loan 02:33 Calculate Monthly Interest Payments 04:55 Double Check Your Calculations 05:49 How Interest Rate affects Total Interest 06:17 How Extra Payments affect Total Interest 07:05 Pro Tips for this Mortgage Calculator
Thank you Kristian, it feels weird but satisfying at the same time! Other than mortgage calculations I'm assuming you use Google Sheets for expense and savings tracking as well?
Hi Jeff, thank you for this excellent video. I wanted to ask you if, when making extra payments, the monthly payment should decrease or if it always stays the same?
@JeffSu yes this gave me anxiety... adulting stinks. Let's couple high interest rates and sellers asking for what can only be considered incomprehensible greed. It's bad, all bad lol
@@JeffSu I know that I mean how it’s it calculated onto the 400,000 because it’s doesn’t seem like it’s a lump sum percent rate because 3.3% of 400,000 does not equal to 230,000 so how do the implement this 3.3% in the equation the amounts to you paying over half or the value of the loan more back to the bank
Hey Jeff, Great video as always! Just quick note that when dividing the percent by 12 it's not very accurate - for better results add 1 (for 103.3%) and root the number of months (or add to the power of 1/12) and then subtract 1. I know it's a bit complicated at first, so maybe that's why you made it simpler :) Keep up the great work!😁😁
Jeff, this is awesome. Love it. Very easy to understand. One question, what if it's base on a variable rate instead of fixed? let's say 2 years in, rate increases and 3 years in, rate reduces. How to adjust accordingly?
Great video. Download link for template doesn’t seem to be working. Not to worry tho you video was very clear and I have followed along to create my own version. Thank you! Now to work out the benefits overpaying will have for me 😅
Just watched your video, and I already subscribed to your channel. Great tutorial, Easy to understand even for a beginner level. But it was a tad too fast for me. OK. I did not watch Ali's video. But when I put the numbers he used, I got the same PMT$8485.24. Is it correct or wrong?
Hey Jeff, I haven't read all the comments (someone probably already pointed this out) but you verbally added Total Interest to calculate Total Principal in the second row for Total Principal. But the formula you entered was correct. Great service you're providing to home buyers! As a retired investment banker who joined his wife as a Realtor, I used to use Excel all the time, but have had relatively little need over the last 20 years. I say this to say you are not only helping home buyers, you're also helping an old dog like me save the mental energy needed to figure out how I would lay this out for my potential clients. Hat tip to you, and just so you know, and I would be glad if you beat me to the punch, we need to create a spreadsheet that goes the next step and can compare the real economic consequences of the 3 different scenarios (payment options) the lender offers after our offer to the seller has been accepted, including different interest rate buy-downs. Peace, --N
Can you help me figure out how the bank calculates my payment. I can't figure it out. I borrowed 5017.85 at 19.495 apr, 84 payments, i got the money on 9/8/23 , payments due each 20th with first payment made on 10/20/23 , last on 9/20/30. THey say my payment is 110.62, i can not figure it out. Please help. One thing i noticed is 84 payments could be 83 periods ... not sure how this figures. THanks cp
I'm on line 10 and putting your formula in Interest column. It continues giving me a % figure which is doesn't make sense. (24525.00%) instead of the interest amount. What am I doing wrong?
Did you ever make the video on variable rates? I want to track my mortgage like you showed but the interest rate keeps going up randomly in South Africa.
In a scenario where a loan is paid off early with extra payments applied to the principal (inconsistent times), do you know how to calculate the interest rate (not amount) paid after the loan is paid off? For example, if the term of the loan is 60 months with an interest rate of 6%, but someone paid it off in 50 months? This has proven difficult to find the answer to so any help is greatly appreciated.
I agree that it’s essential to know this stuff! It’s definitely eye opening and really emphasizes how major the effect of a 1% increase in rates is 😅 Gotta always negotiate down! As a finance major, I was taught to calculate this using the good ol’ BA II Plus financial calculator but your template does a nicer job of presenting it I’d say :3 And it’s a nice choice of colors! Considering the color pallet of your dual monitor setup, it’s definitely cohesive ^-^ Any chance of an apartment tour some time in the future? :p
oke I have 2 questions if i may: 1 how can i change the currency from dollar to euro? 2 i have my mortgage for a few years now should i start with that date? i also have a 30 year plan. i think i should in order to see the full plan but just to be sure ;-)
Such a great video. I love how you taught us all of the PMT formulas. I didn't know how to use them before. Random question for you. Is there a way to have the sheet auto-remove all the rows at the bottom once the balance hits $0? I'm guessing this ties into the MIN/MAX functionality or an IF=$0/THAN stop populating rows below formula with an extra step.
thanks. Monthly interest is (1 + Ann rate) ^ (1/12) -1 . It is very close to the Ann rate / 12 but here it is causing your monthly payment to be $10 more than it needs to be :)
@@JeffSu @Jeff Su you paid 5.000 extra over 10 years (50k) to save 53k and finish the loan 5 years earlier. If you ask for 450k, and use 5k annually as you illustrated, you will save 25k and finish the loan 4 years earlier 😉 you would need to spend your annual bonus.
@@JeffSu when you do the extra payment for the mortgage, do you have to let the loaner know that you’re paying towards the principal? Or should that be a known thing? Thank you for taking the time out of your busy schedule to reply as well!
@@JeffSu Haha nice. I added a COUNTIF formula to count the number of cells between c10 and c369 that contain a value greater than 0. Now when I change the extra payments I can see how many months the payment(s) remove
@@JeffSu It's not as convenient as your sheet. It allows me to make hypothetical changes such as more monthly payment/a lumpsum payment, and see how that impacts the payout schedule
@@JeffSu thanks! But I've a q regarding amortisation. Bank uses daily interest rate to calculate the interest for the given month. And interest rate changes multiple times in a month. Is there a workaround to this? I mean can i automate this in excel or python somehow? Also, the bank substract the whole payment from the closing balance and add interest rate and transaction cost for the upcoming month. Which is really odd tbh. I can provide you with the all the data if need to be. Thanks in advance.
damn I bought a house earlier this year and I did my own mortgage calculator sheet, but after watching this video realizing how primitive my sheet was haha, needs an adjustable/LPR base video urgently Jeff, your video always creates great value in my life.
Hahaha well 80% of the value from calculators are just the monthly payment aspect, since that basically tells you whether you can afford the mortgage. Will do the variable rate one down the line 😁
Great video ! Your example has 1 mortgage payment every month, I would like to know what to do if My mortgage payments are bi-wekly , 26 payments / year ? Thank you !!
This is just brilliant. I needed something like this. I would really like to vote for variable rates too... It has been 2 years now with fixed and recently had to fight with the bank to get a decent deal considering the inflation. So payments (rates) change from October.
Instead of the additional $5k payments towards your mortgage - consider taking that money and instead investing it in S&P500. At an average return of 10% a year, after 30 years your $50k will be $270k.
