Mortgage Calculator With Extra Payment

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

[@dtjones928]

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+++

[@mrelator1074]

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!

[@LaithGraham]

I’ve been trying to find something for Numbers too. Good to know this will work on the Mac version.

[@MrWilliamrjh]

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

[@khalidbinmahfooz1488]

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.

[@cyndigoldstein8627]

This, hands down, is the BEST TUTORIAL I have ever watched.

[@zeevondee]

Great video! You should definitely consider making more financial advice content for your channel. You make things simple, clear, and most important - authentic. KZbin 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!

[@shellythompson4695]

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!

[@frankayala3950]

Shelly, same with me...this video is by far the greatest...

[@raftinguru]

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!

[@cathydouglas2930]

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!!!

[@rehofmann33]

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.

[@rodneywillis8516]

Absolutely Excellent!!! Very easy to follow and understand on a subject that every home owner should understand and use.

[@gillianleonard992]

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.

[@adamlassiter1710]

Thanks for this tutorial. This will help me with our goal of paying off my mortgage early.

[@ptj492]

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!

[@obioravo]

Well done, I was able to use this with another video to create a calculator from Canada! Thank you so much!

[@alicechee8017]

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!

[@idasantiagoorlandorealtor4589]

this is so simple to follow, a great reminder from my classes.

[@bradross9856]

Thank you for this. Today is Jan 1, 2024 - and I created a 15 year house payment plan ! thanks !

[@CCCCCCCCCCCmany]

Cool

[@kalvinkelley9879]

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!

[@andreacleo6247]

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.

[@pauleff3312]

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

[@TheMDM1956]

This is VERY good, very helpful to me, it is what I've been looking for. Thank you!

[@richc2109]

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

[@staceyschaefer3192]

Thank you for the easy to follow instruction

[@blazezoolander7473]

Very well done! I copied everything over on to my own. Thank you!

[@pratik9876]

Thanks..this what I was looking for with Extra Payments functionality

[@prudencelegadima7482]

Thank you so much. I managed to do my own and have a plan for my path to financial freedom.

[@just1missp]

Hi. Can you tell me if this will work for a loan already several years in...like 13 years in?

[@prudencelegadima7482]

@@just1missp It will work. Just ensure you update your data correctly.

[@joseavila8752]

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

[@nelson6446]

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?

[@maricornillos]

Thank you so much for this excellent tutorial.

[@dle7725]

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?

[@ThomasGillot]

Escrow + insurance are not included in loan amortization schedule since they are not part of paying down the loan balance.

[@dle7725]

@@ThomasGillot true, they stick around like weeds in the yard, 😆

[@ARHemant]

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?

[@rummy3397]

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.

[@aimanaal831]

Great job. Thank you!

[@sandravhg]

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?

[@andrewsansone5785]

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!

[@VighneshSivan]

Thank you so much. I loved this excel calculator

[@damonmelendez856]

Super helpful, thank you!

[@carlkellogg5342]

Figured out how to drag on my own. I give you a A+ on the video and a E- on answering peoples questions.

[@richc2109]

It's a video on KZbin, he doesn't owe anyone any follow up.

[@ViajeroTemporalOficial]

I need the formula for recurring extrapayments, that shows how much I would save in one cell.

[@parsamahdi3304]

Excellent and Outstanding video!

[@2ncs]

Thank you sir!

[@gnarfish5555]

Wow!! Very nice!! Thank you for sharing!!

[@sandradahl9650]

Really would like the same tutorial fr bi-weekly payments....PLEASE!!!!

[@rondab3376]

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.

[@michaelpichotta5553]

Success!!!! Great tutorial.

[@williamcox6054]

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.

[@john26hanson]

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.

[@oscarfernandez9964]

Amazing thank you very much

[@marcpak4909]

Very detailed info.

[@moneynessdotbiz4363]

Thanks! Details matter for this one, but it should still be easy to make the calculator.

[@kookooo16]

Holy Guacamole BATMAN 😎 I have found my EXCEL AMORTIZATION KING!

[@hsford4]

Thank you very much.

[@mirbelal1818]

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 ?

[@keeloraz9452]

Great stuff

[@flourishingweeds9921]

Hi, do you have a tutorial for Bio weekly payment or can you tell me what needs to be adjusted in this spread sheet?

[@TricommStrategies]

This is what I am looking for too!

[@alexisayala7061]

Great video, thanks for the info, QQ: What happens if instead of doing 1 single extra payment, you make 2?. Thanks again

[@dmehta3476]

what if you want to see yearly payments and balances instead of monthly - accurate payments

[@belinej.p3481]

Good tutorial, but impossible to see what formula you use

[@whoruxd9616]

Thank you for the video, is very useful

[@geekdeed]

Hi thanks for video it’s very useful But I want to how to reduce monthly payment (not the period) by just paying extra

[@davidcutter1030]

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?

[@jumpenjackflash4964]

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.

[@JoseFernandez-sl2ek]

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?

[@bluhaws4796]

Is there a way to add or take into account property taxes and home owners insurance? Maybe having them show in my monthly payment?

[@catsaniga2302]

Great job ! Great job !

[@sarahdieppa1848]

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.

[@jkbc]

Man it looks so easy when he said BOOM..LOL

[@dtgreen6]

You rock. Thank you so much!

[@inderids]

Great Great Tutorial , Thank You Sir !

[@GeraldP65]

Totally Great Video !! Thanks !!

[@ellexusse]

Thank you so much! Super helpful!

[@aac993]

At 2:10, you skipped right to a partially built spreadsheet. Where did that come from? How did you create it? Thanks.

[@paudieb]

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.

[@shaileshrawat5333]

Nice Tutorial. It helped me a lot. Thanks for the same.

[@andre2070]

Great content! Thank you!

[@nicovianes]

Awesome video BUT, if your interest rate is 2% with 30 years mortgage and you put a monthly extra payment of let say $200, Would it be better to invest that monthly $200 into stock market if the stock market makes you an average 5% return??? Thank you!

[@LielaAbouzeenni]

Can I please get some help? I have a variable rate mortgage, and I added an extra column where I can change the rate each time it changes, so each payment is calculated according to it's line. But the problem is my monthly payment and interest don't match up with my mortgage document. thank you.

[@RajkumarMani3]

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

[@Spartan9952]

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.

[@skipsurre3396]

Great job thank you

[@sethellingford6200]

Great Video!

[@Krock1969]

Can I create this spreadsheet eventhough my mortgage started 2 years ago? Adjustments? Include escrow and insurance?

[@Powercat808]

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

[@juniortj9428]

Great, but you are assuming the interest rate will stay the same for 30 years. Majority of rates change every 3 and 5 years. First 3 to 5 years you could lock in 4%. 3 to 5 years after that you could renew for 2% or even 5%

[@frankayala3950]

Guru....🤯

[@isakwendesten4695]

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?

[@shawnstangeland3011]

Could easily convert this to a retirement calculator including social security, stocks, pensions, and savings

[@rauldaley4845]

Awesome

[@standupman6422]

What happens if your rate changes every 5 years? You can have a 25 year mortgage but nothing says the rate won't change? And if you double up payments with weekly payments on your mortgage...

[@lesliewilliams6342]

Would be helpful if you could share the template that you created! (if made on Google sheets) Would save a lot of folks some time so they don't have to recreate it. Thanks for all of you knowledge either way! Very helpful.

[@judyjohnson1012]

This is for Excel, do you have one for Google sheets?

[@Djtrav121]

How can you do this with a construction loan where the first year of the loan is interest only?

[@bhavneshparikh8283]

Can be possible client wise auto update loan amotozation table? Also if possible interest rate change so auto update automatic in excel Extra Payments means (Start at Payment No,Extra Payment,Payment Interval,Extra Annual Payment,Payment,Total Extra Payments) Additional Payment already showing in your video ,Variable or Fixed Rate ,Impact of interest rate HIKE on your loan EMI & repayment schedule & Impact of interest rate CUT on your loan EMI & repayment schedule ? how to create in excel & Suppose provide only interest

[@ronaldrabanes4083]

hi tnx budy., i used it to my system.,

[@darleenhernandez9158]

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"?

[@manuelugaldeultreras842]

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?

[@rickg4802]

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

[@ananthamurthy1221]

What should I do if I have variable rate of interest..what formula should I change

[@carlkellogg5342]

how did you drag the worksheet ? Thank you.... Anyone please......

[@manuelugaldeultreras842]

great video

[@terantreeexperts11]

how is the late mortgage fee calculated?