How to calculate Mortage with Extra Payments in Excel

We recently took a mortgage on our house. As I am self-employed, there is a lot of variability in my income. So I wanted to see the impact of occasional top-up payments. Most online calculators couldn't tell me how it would impact my loan term. So I went ahead and built an Excel calculator to give me the answer.
In this video, let me tell you the story of that and walk you thru the process of building such a calculator yourself.
Download the calculator 💻👇
=========================
Don't care how this is built? Just want the calculator? Get it from here 👉chandoo.org/wp/mortgage-calcu...
⏱ In the video:
=============
0:00 - Why I couldn't use the existing mortgage calculators
1:00 - Quick demo of my calculator
2:00 - How to build such a mortgage calculator yourself
3:36 - How the "extra payments" work
4:19 - Setting up the loan amortization table
10:34 - Adding the bells & whistles (chart & conditional formatting)
😎 WATCH NEXT
==============
I got a mortgage to pay, so why not watch a few more videos on my channel 🤣
Break-even analysis and What-if modeling - • Breakeven analysis and...
Which power plan is best for me - • Which power plan is be...
3 Essential date formulas for financial analysis - • 3 Powerful & Essential...
What if modeling in Power BI - • How to create a dynami...
💚 Like Finance Videos? Want me to make more?
========================================
Do you want to learn about financial analysis, modeling concepts here on the channel? Let me know and I can add videos on this topic too. Post a comment with your suggestions.
😍FOLLLOW ME:
==============
Apart from KZbin, I regularly share content on below places. Feel free to check them out and give a follow.
💻 WEBSITE - chandoo.org/wp/
📃 WEEKLY NEWSLETTER - chandoo.org/wp/subscribe/
📷 INSTAGRAM - / chandoo.xlsx
🐤 TWITTER - / r1c1
~
🐟🐠🐡🦈Where do fish keep their money?
In the river bank 🌊🤣
#mortgagecalculator #finances

[@danielhong3534]

Excellent calculator, Chandoo. Thank you so much for the Excel sheet. I was trying to figure out this formula for a while. Thank you again.

[@truongh1005]

Thank you so much for creating this model for us.

[@alvarojflores]

Thank you a lot! For sharing your knowledge. 👍🏻💪🏻

[@anastasia_tee1384]

I love your teaching 🥰🥰thank you 🙏

[@shreyajayesh]

This is the much needed document for all the people buying home

[@HowToAnalyst]

Thank you for sharing, great video!

[@georgejetson9801]

I just stopped by to say thanks for all the valuable lessons you've provided over the years. I've been following you since probably 2008. A fan from Perth.

[@chandoo_]

Wow George. Thank you so much for being with me all these years 😍

[@georgejetson9801]

@@chandoo_ You're welcome. Hopefully sometime when I am in NZ we can meet for lunch. Seems l'm always in a different city or country when you have an in person workshop or meetup.

[@hari60700]

Very useful one.Thank u chandoo

[@vishalkashyap3456]

Awesome Video! 👌

[@peepshah]

Excellent. I was looking for this calculator with Extra Payments. Thanks Chandoo for this video !

[@chandoo_]

Glad to hear that

[@miteshdd]

Thank you, this is certainly helpful and the tenure calculation is a good info to see. I would request you to add Rate of Interest column also for each month as the Interest rates keep on changing through the term loan period

[@monishnagarajan5917]

Great video Chandoo

[@shreyajayesh]

I was practicing this excel to make repayment strategy. There is a slight difference in my case. Although it is easy, you can consider adding this in next part. 1. I purchased under construction property, so the amount disbursed by bank varies as per the stage of completion of work. In your case you directly took a amount borrowed from bank which is applicable for ready possession property. 2. Variable rate of interest. Now the rate of interest has increased by Indian banks which makes calculation little complex. Totally loved your work on this excel. Thanks Chandoo!

[@swatiprabhakar2952]

Thank you for this wonderful video, Please make a 2nd part of this video with Variable rate of Interest.

[@miteshgedia2986]

Good Explain 😊

[@danieltone416]

Great calculator, Chandoo! One thing I would add to make this complete and that is the interest saved in relation to time saved.

[@chandoo_]

Thanks for the suggestion.

[@GunsG57]

Agree, knowing interest saved should be a greater motivator for additional repayments rather than simply visibility of a shorter term loan.

[@chrism9037]

This is a great calculator, thanks Chandoo!

[@chandoo_]

Glad you like it!

[@MChagall]

I made something similar as i bought a house last year. I think you forgot that you can get a mortage as a annuity, a linear loan or balanced against a stock savings account. The comparison on which would fit best for you would be great for such a sheet I feel. Also you could take into account what your expenses would be (including interest and taxes) monthly. Would you prefer to pay more at start or pay less at start but more in total because your income will probably grow and you could use the money better earlier in life or your expected returns on the market offset the extra interest payments.

[@marcovirtual]

Please, Chandoo, consider doing a 2nd part explaining how to do the graphs, the conditional formatting and how to calculate the years reduced. Thanks!

[@chandoo_]

Good idea. I will expand the article in coming weeks with details on the chart. chandoo.org/wp/mortgage-calculator-with-extra-payments-excel-download/

[@Mega.Luke87]

Hello there, great video. Is it much harder to calculate for mixed-mortgages? I'm looking at products that have different initial periods (36,60,120 months) of fixed rate (2% for example) and then they switch to variable, which is whatever EURIBOR base rate + 0,5% for example. What kind of formula would cover for that? Thank you in advance.

[@randygarcia1504]

Hi Good evening your are great and very helpful, Thank you so much. Do you have a sample of mortgage amortization table that combines the option to choose variable and fixed interest rate? I really appreciate it. Thanks again.

[@EricCawaling]

Thank you. why is the =sequence not working on my Excel?

[@arbazahmad7177]

Thanks 😊 for this awesome 👌 loan amortization calculator. Kindly make a video on Financial Statement in power Bi.

[@richaagrawal376]

Great tutorial!can u plz tell how to make the dynamic graph from scratch

[@snackplaylove]

Very useful for a friend - hd to hack it a bit as he’s on an Interest only mortgage and the variability is all above that (easy enough fix)

[@manoharsagunthalla9215]

If explain how to make the graph and conditional calculations that would be helpful

[@eddiepaterson1583]

Also, can you add an interest rate that changes irregularly. Just like is now happening nearly each month. I did one for my son but I would like tidier appearance and information. Thanks - Australia.

[@rumeyjiffrey5485]

Good video chandoo. Maybe instead of dividing by 12, let user choose interval as well? Also, INT wiuld be better than RIUND? Keep up the good work.

[@ricky-rickreddy8674]

Very interesting ... what one do when there is a increase or decrease during the period of the loan especially mid month

[@pjn2001]

Hi, why is there a discrepancy between the sum of the months saved column and the total time saved shown on the chart? Thanks

[@gowthamrajb9773]

Hi chandoo.. great calculator.. can u pls add different currency choosing option will be helpful for other countries also..

Thanks, it will be great if you can make it using new dynamic function.

[@skfacts57]

Useful for every family who opted for loans..can u add 2 more functions which populating total of principal paid, extra payment, interest paid & closing balance please

[@sharmamunish104]

Hi Sir, is it possible to view the whole data in power bi ( power query) because my excel file contain 50000 records but power query show only 999+ row??

[@Kevin.l888]

Is there anything that can be done to also show an offset amount?

[@akilaliyanage]

Is there a video with variable rate with extra payments?

[@Snarsophagus]

Thank you for sharing and for your work, it is very helpful. There is an error I wanted to share for correction however. To see the error, just add the Principal Paid with the Interest Paid for a given month after an extra payment is made. The sum should equal the monthly payment but does not. The issue appears to be applying the rounded NPER effective term to the principal paid calculations. Thanks again for sharing!

[@jaswinderbhatia9665]

Hi Chandoo, in India, home loan interest rates changes frequently, how to calculate that scenario

[@Sajesh_Nair]

We have loans linked to Repo Rate and it seems every qrt rate is getting changed and banks are taking us for ride. Pls see if you can help us with calculator which will help us to know how much pre-payment is needed after each rate hike to keep the same loan term atleast.

[@lorenebullivant4040]

Hi Chandoo, I'm a total newbie to Excel. Yeah, I know I've been hiding under a rock! 🤣 I'm sitting here Down Under watching the kangaroos jump down the street - true story! 😊I believe this calculator is exactly what I'm looking for. Would you please tell me how I can change the calculator to be fortnightly instead of monthly across all fields? Much appreciated!!

[@lawrencetaylor6509]

great, but what if i miss a payment one month

[@maciiprado5667]

Hi Chandoo ... will you also be creating a sample monthly loan amortization in Excel with the condition of 5 yrs fixed interest rate then variable annual interest rate for the next 10 yrs. based on annuity or diminishing balance. Appreciate your kind advise. Thank you.

[@chandoo_]

Hmm.. good idea. I will need to research that up a bit but may be in future.

[@vyshaksnath]

I also made one for myself using what if analysis goal seek but not able to incorporate change in interest (my loan is a floating interest one)

[@frankboadi1031]

wish we could get some help on the interest rate changes

[@LearnAllways]

Dear Friend, This great video but can you able do another video or part 2 1) Floating interest rate 2) If I increase the Monthly Payment means how it works for example( 3000 instead of paying 3500 every month )

[@bar-b-querangers256]

How can I make a loan calculator which includes the loan date and first payment date? My Excel loan payments never match the bank loan payments. Help!

[@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

[@manigandanganesan5454]

Buddy Cant thank you enough

[@anthonycarabaille6141]

Can I use this formula for my car loan

[@chandoo_]

Yes, you can :)

[@christinet2089]

Can you do this but giving the exact new payoff date when you make extra payments?

[@chandoo_]

You can see it already on the table. Find the month when your balance becomes zero.

[@Logan-yo4rx]

How do i change the payment term to bi-weekly?

[@chandoo_]

divide interest rate with 28 multiply term with 28 and you will have bi-weekly schedule and payment details.

[@user-oi3yb7mm7h]

Keeping other people’s flaws in mind is like keeping a bowl of foul-smelling food they have given you; it contaminates your heart, leaving no room for positive thoughts.

[@ashishgoel19891]

Its great video. Than you for sharing this. Can you please also add or show how much money we can save by paying extra amount at particular year or month. Right now you have shown how much month will be reduced if we pay extra amount. Surely it will also save the money also. Can you please show that if possible how to calculate the same. @chandoo

[@chandoo_]

Great suggestion! You can "theoritically" consider the interest reduction as money saved. Although, true savings would depend on the opportunity cost of paying off early vs. investing or using the money for fun.

[@ashishgoel19891]

@@chandoo_ is it possible to add practically instead of theoretically? 🙂 In your example that you have shown up.

[@chandoo_]

Sure. I've added the calculation to my template here - chandoo.org/wp/mortgage-calculator-with-extra-payments-excel-download/

[@ashishgoel19891]

@@chandoo_ Thanks for adding that option will explore and use it. You are absolutely right true saving is depending upon paying off early vs investing. Do you have video on this topic then please share the link.

[@Jarhead63]

No help at all, you're going way to fast, and some of the things are not working

[@chandoo_]

This is an advanced tutorial. You can pause the video where you want. There is a sample file and an article link in the video. I am not sure what you are expecting me to do.