Building a Mortgage Calculator in Excel with Amortization Table

In this video, we're going to build a rudimentary Mortgage Amortization Table for you to practice your Excel skills as well as have a powerful tool to use in your home purchase decision-making process.

[juanvalladares]

This is one of the best videos explaining a Mortage carculator in excel, great stuff!

[Ralphthecharm]

Very good video. I was able to build my mortgage calculator with your instructions. Thank you.

[tyx]

You hero, thank you for this, I was stressing about how to keep track of this on a ss and you saved the day.

[monicabouteiller]

Wow! You explained this very well. Thank you! I look forward to using this very soon.

[sheilacase]

Great video and instruction! Really learned a lot! Thanks!

[std]

Hi Sergio,
Thanks for your video. How can I put for the extra payment like 1 time in a year, not every month like your demonstration.

[cindywalsh]

Hi Sergio. I loved being able to build my own calculator. I learned so much in this small video alone. Would you be able to direct me on how to make the payments come out accelerated biweekly? Also, I make an extra payment monthly but also make additional payments through the year. Any ideas how I could set that up? I love to watch the principal go down and it gives me incentive to make more payments against the principal. Thank you again!

[nirmalsurendranmenon]

Hey Sergio, excellent video. I do have a question though. Your total payment does not include the interest..why is that ? I believe the interest should be included too as that is something you are paying as well from your pocket ?

[CheriseWilliams-kxks]

This is amazing. One question, however what if I make extra payments of varying amounts on different months. How does work into this scenario?

[nrshapiro]

Hi. Good review. I have a mortgage calculation that I need to solve. I would like a formula to calculate the net Present Value of the interest payment stream (so a payment that declines each montg for the term of the loan). I need it to calculate bank prepayment penalties. Any ideas?

[pjdebutler]

Nice job, thank you. So sad that the price inputs and mortgage rates are now so incredibly high. Would make me laugh at your comments if I weren't crying.

[DavidThompson-jzue]

How can you edit this so you can make a half payment biweekly?

[justTanyaM]

Hi Sergio! How would you adjust the spreadsheet to calculate a one time additional payment or a additional payments at different times? Someone may want to add additional payments but not every month. Can this be modified?

[dmehta]

how do we see yearly amortization instead of monthly. can you please explain. Any way to see Annual please.

[marcolsen]

It would be so kind if you saved your Excel file from the video and included it in a link here in the comments for others to play with!

[juliemarshall]

Do you have something that includes escrow payments calculations?

[garyhowe]

Hi Sergio, great video and I have replicated with a few additional features (interest saved and months saved) that work when the loan is set to max term but then fail when it's reduced. I've tried all I can to fix but it's beyond me. Are you able to offer any assistance ?

[bhavneshparikh]

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

[billcaudill]

If you added an extra column to show principal amount would that mess up your spreadsheet?

[MariaHernandez-bbpo]

What if you don't make extra payments every month. How will you set up a formula for that?