Mortgage Calculator With Extra Payment
Vložit
- čas přidán 26. 09. 2020
- If you can use a spreadsheet, you can make your own mortgage calculator! Learn how to do it yourself and see the impact of making extra payments. Learn the trick I used to make the calculator match the amortization schedule in my mortgage documents.
Technical Work Performed by Ellen Keil
Creative Consulting by Erika Malos-Keil
Royalty free music from Bensound
Very thorough. Paced so that I, as a first timer, could follow. Clear explanations. Patient tone of voice. I now have an amortization schedule with extra payments. A+++
EXCELLENT TUTORIAL!!!! I stopped and started this video a gazillion times while I simultaneously made my Numbers spreadsheet mortgage calculator. Thank you so much for being extremely clear and making a valuable tool for homebuyers!
I’ve been trying to find something for Numbers too. Good to know this will work on the Mac version.
Thank you for taking the time to make this video. This will be an instrumental tool in determining my strategy for paying off my mortgage while my interest rates swing wildly
This, hands down, is the BEST TUTORIAL I have ever watched.
There have been many videos, especially those concerning mortgages, but this video or lesson offers a detailed explanation in a very professional manner. Thank you for this video.
EXCELLENT video!!! I paused and started it a number of times, and managed to build the exact form. I was able to apply this formula to all of my current loans to the penny (or close enough). Not just to my mortgage but all of them (truck, camper, and boat). Every time when I pay off a loan, I roll the paid loan payment onto my next loan inline, so it compounds and my total debt is paid off faster. With this calculator I can see exactly when a loan is going to paid off, and how much (and when), I will be rolling the old payment to the next. This ables me to get an estimated picture of when ALL my loans are going to be paid off. Worth the effort and time. THANKS!
Thank you for this tutorial! There were a couple of places I had to go back and watch parts of it over because the steps weren't clearly expressed, but by watching it, I was able to figure out what I needed to do to fix it. I love that you had the insight to make this video to help those of us that aren't up to par on Excel!
Shelly, same with me...this video is by far the greatest...
While Googling and watching many other videos I've been trying to figure this out for months. I used it with my Numbers spreadsheet and with a little tweaking, your video worked perfectly! THANK YOU!!!
Great video! You should definitely consider making more financial advice content for your channel. You make things simple, clear, and most important - authentic. CZcams is filled with content creators that provide advice that is more complicated than it really is; which you've demonstrated in this video how simple it is. Keep up the good work!
I appreciate your excellent instruction. You made it super easy to follow along and I matched my amortization schedule to the penny. Now it is much easier for me to plan scenarios for early mortgage payoff. Thank you very much for your time and this excellent video.
Well done, I was able to use this with another video to create a calculator from Canada! Thank you so much!
This is super useful and interesting! Just what I have been searching for. and the tutorial is easy to follow! You are awesome, please do more of these interesting videos. Thanks much!
I watched a different video and found this one was a bit clearer. It was able to help me add in extra payments. Nicely done.
Absolutely Excellent!!! Very easy to follow and understand on a subject that every home owner should understand and use.
Very well done! I copied everything over on to my own. Thank you!
this is so simple to follow, a great reminder from my classes.
Wow!! Very nice!! Thank you for sharing!!
Excellent and Outstanding video!
You are awesome!!! This was very easy to follow and there are so many people I know who will take advantage of this video. Thank you for sharing your knowledge. I look forward to other financial advise you can share with us.
Thank you so much! Super helpful!
Success!!!! Great tutorial.
Awesome! You really hit the nail, the best tutorial, keep it up man you really are helping people like me (first-time buyer), Thank you much
This is VERY good, very helpful to me, it is what I've been looking for. Thank you!
Great job ! Great job !
Thank you very much for this video! This has to be the BEST video ever, i have been searching for so many videos to help me gauge mortgage calculation and I am so grateful because of your video :) God Bless You!
Super helpful, thank you!
Great content! Thank you!
Great Great Tutorial , Thank You Sir !
Great job. Thank you!
Thank you for this. Today is Jan 1, 2024 - and I created a 15 year house payment plan ! thanks !
Cool
Totally Great Video !! Thanks !!
Thanks for this tutorial. This will help me with our goal of paying off my mortgage early.
Thank you so much for this excellent tutorial.
Thank you so much. I loved this excel calculator
Thank you for the easy to follow instruction
You rock. Thank you so much!
Thank you for the video, is very useful
Thanks..this what I was looking for with Extra Payments functionality
Great Video!
Your tutorial is so straightforward and easy to follow! Thank you so much! Will you be making a video on how to make a budget on excel?
Thank you sir!
Nice Tutorial. It helped me a lot. Thanks for the same.
Thanks for this. I was able to match to the penny without any tweaking. I was able to add a "Months Early" field to the top with the following calculation:
=B5-(MATCH(0,G8:G368,0))+1
Great job thank you
Great stuff
Very detailed info.
Thanks! Details matter for this one, but it should still be easy to make the calculator.
Thank you very much.
What I used the rounding that you recommended, this caused me to be off by two dollars after pulling all the rows down. What might my error be?
great video
I'm British (UK resident) I *do* have a mortgage calculator of my own which I wrote quite differently because I can't get my head around the pmt function (so I calculated the *daily* compounding that we have here manually) It's not as accurate as yours and drifts by a few pounds over the course of each year (through rounding errors) but I just "correct it" to match the bank's statement annually.
What having one has taught me is just _overpay, overpay, overpay!_ In short, through having it (the calculator), I look set to have save more than 70% of the time I would have had the mortgage and around 85% of the interest I would otherwise have been charged! Each £10 paid on the mortgage was saving £17 in interest at the beginning (not so much now at all) but is entirely tax-free! What an incentive - Ask myself _What if I throw another £46.87 at the mortgage today or go out to watch a film with friends....._
I will try to work through your pmts function and change it to annual for if I ever get another mortgage (or writing a spreadsheet for someone else when they see what I've done {as in paying mine off so early}).
Thank you for sharing
Two things 1. I like the beginning and ending music. 2. My spreadsheet did not match my statement exactly. When you round monthly payment (B6) do you drag it down the column as well? Or do you leave (C10) with the MIN function in.? Thanks
Thank you so much. I managed to do my own and have a plan for my path to financial freedom.
Hi. Can you tell me if this will work for a loan already several years in...like 13 years in?
@@just1missp It will work. Just ensure you update your data correctly.
Thank you so much for this calculator. Wonderful presentation. Since then I switched to using a Heloc in the first lien position. I was wondering if you would consider making a calculator for this application since interest is compounded daily. Thanks again.
Great video!!! Helped me understand alot. However I ran into a problem at the end when using the =MAX function. When I added like you stated it only showed me the value in the first cell of the interest column. Im pretty sure Im adding correctly but need some assistance. Thanks
Great Video, Thank you so much for making it. I have one question: The Original Int. You copy it from Total Int. 184,,,, but when you change the Rate it does not copy as the Total Inter?
When trying to enter rounding for the monthly payment I could not get the formula to work. The video portion showing this was hard to see it as it moved quickly. Can someone give me the =round formula for the monthly payment please. BTW this class was the best I have ever seen. Clean and simple to follow. Thank you! Please do more!
Hi, I've been looking for and using other spreadsheets and this is by far the easiest. Can you make one for those who have insurance & escrow included in their mortgage payments?
Escrow + insurance are not included in loan amortization schedule since they are not part of paying down the loan balance.
@@ThomasGillot true, they stick around like weeds in the yard, 😆
GREAT VIDEO! when it comes to rounding the monthly payment and the interest expense, try using the ROUNDDOWN function, instead of just ROUND. I found that when I did that, I did not have any cents left over at the end of the spreadsheet. keep up the good work!
hi tnx budy., i used it to my system.,
Beautifully done! Thank you for making this. It was super helpful!
Edit: I have a question. In the above example, if extra payments are made, what would be the formula to calculate the actual number of months in which the loan is paid off?
You can see the actual number of months by looking at his "Month #" column. The loan will be payed off in the month where the total principal = loan. The subsequent cells after the loan is fully paid off, will have 0 or - as their updated opening balance.
Awesome
Is there a way to add or take into account property taxes and home owners insurance? Maybe having them show in my monthly payment?
Holy Guacamole BATMAN 😎 I have found my EXCEL AMORTIZATION KING!
I need the formula for recurring extrapayments, that shows how much I would save in one cell.
I really like how you do the comparisons at the end--that's helpful! But I do have a question: I had made another schedule previously that used IPMT in the interest calculation, which involves NPER. Somehow that one comes up about a thousand dollars different in only a year's time on a $230.000 loan and I don't understand what's going on. The way you do it is much simpler, but the payoff looks a lot better the other way, so I'm confused.
Will this work with any excel file, or is there something that needs to be added to your excel file?
Thanks for a great video. I'm a little confused because the payment calculated by the spreadsheet is about $20.00 less that the payment on my lender's provided table. it's amazing how much small extra payments affect the interest savings.
Thank you for your video. I followed everything up to 9:30. The original calculation changes each time we change the terms of the loan. How was the original interest figure maintained?
Can I create this spreadsheet eventhough my mortgage started 2 years ago? Adjustments? Include escrow and insurance?
Hello and thank you for your detailed video, I currently have an Amortization table that is in use and I seem to be off on my calculation by approximately, $1300 dollars I hope this will get me straightened out. Also why don't Amortization table have Escrow associated into the equations? It is part of monthly payments. One more question, by adding the $2.71 on to the last payment isn't that only relevant if you didn't make any extra payments? if you made extra payments to the schedule wouldn't your interest change making the $2.71 a non-important entity? Thank You in advance.
Could easily convert this to a retirement calculator including social security, stocks, pensions, and savings
Figured out how to drag on my own. I give you a A+ on the video and a E- on answering peoples questions.
It's a video on CZcams, he doesn't owe anyone any follow up.
What should I do if I have variable rate of interest..what formula should I change
Hi. Can you tell me if this will work for a loan already several years in with extra payments already made?
QUESTION! (first of all brilliant video super helpful) My question is: say I have a 10 year fix but the loan is still to be paid off in 30 years as per your example, how will this affect this calculator. As I can find out the amount paid in interest and priciple paid on the banks website, is there a way to see how overpaying would change the amount only within the 10 year fix period? I hope this makes sense. Will.
Great video, thanks for the info, QQ: What happens if instead of doing 1 single extra payment, you make 2?. Thanks again
Man it looks so easy when he said BOOM..LOL
Hi! I’m trying this in google sheets, everything works EXCEPT dragging the rows down… it autofills some of it but it doesn’t leave “B3” on interest but instead goes to B4, B5 etc… how do I solve this?
Really would like the same tutorial fr bi-weekly payments....PLEASE!!!!
how do banks account for future property taxes or how that changes the loan amount if the loan was 220,000, which is part of the escrow accont?
Thank you for sharing. Everything was working fine until I copied and pasted the total interest into the original interest. The values remain the same regardless of changing the interest rate or term. Any ideas as to why?
Can the extra payment amount varies from months to months or it needs to be fixed amount? Thanks
how did you drag the worksheet ? Thank you.... Anyone please......
At 2:10, you skipped right to a partially built spreadsheet. Where did that come from? How did you create it? Thanks.
At 3:17 you mentioned how US mortgages differ from outside the US where the compounding works differently? Well, my Irish mortgage as an EU state does not match up using this formula. I'm off by only 1.70 per month. Can you point me in the direction as to how I can fix this European compounding problem. Thanks.
🔥🔥🔥
Very good video.. but I have a question .. there is a big change in total interest payment between adding more down payment vs paying same in the extra monthly $..! Am I missing something? For ex:
If I choose to put extra 25k in first month extra payment rather than towards down payment it changes big .. how is that? Can you plz explain
what if you want to see yearly payments and balances instead of monthly - accurate payments
Is it possible to show one with Escrow
Matched
How do you build a model with a fluctuating interest rate?
It looks like you are using Excel spread sheet, and not sure if open office uses the same formulas. Also, I notice the annual re-cap of princ/int is missing. I think most loans are based from date of payment to date of payment so the computation is not always 30 days. And if you make an extra payment at a different time than the regular payment, then some of the extra payment will be interest and some principal. Then you would have to insert a row and hopefully everything will self-recalculate. Amortization schedules are approximate figures and work well enough for us to use them.
How can you do this with a construction loan where the first year of the loan is interest only?
Hi, do you have a tutorial for Bio weekly payment or can you tell me what needs to be adjusted in this spread sheet?
This is what I am looking for too!
good day Moneyness, when I round off my interest payment, and I drag down to month 360, my spreadsheets become #VALUE! in all my spreadsheet meaning the formuale for interest payment is copied, but the values are invalid. I lost you there. Please can you help me here. This is the only part that is giving me a problem.
Suppose , customer don't know how much money he paid. He knows only the loan amount he took first and the current balance of the loan and also the interest rate and the duration of the loan. is it possible to find out the amount he paid ? Example- He took 100 dollar. and current balance is 40 dollar. Interest rate 9%. Time 12 months. How can i estimate the amount he paid ? Is it possible ?
This is for Excel, do you have one for Google sheets?
Hi thanks for video it’s very useful
But I want to how to reduce monthly payment (not the period) by just paying extra
So I'm using google sheet everything work except the total Principal increase every time I ass a extra payment, does any one know how to fix this (I'm using =MAX just like it said in the video)
My worksheet was to the penny until I started making extra payments. I don't necessarily make them on the due date of the normal payment. How do I record extra payments at a different frequency and on different dates than the "normal payment"?