How to calculate Mortage with Extra Payments in Excel

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

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

[@shreyajayesh]

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

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

[@truongh1005]

Thank you so much for creating this model for us.

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

[@peepshah]

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

[@chandoo_]

Glad to hear that

[@alvarojflores]

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

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

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

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

[@HowToAnalyst]

Thank you for sharing, great video!

[@anastasia_tee1384]

I love your teaching 🥰🥰thank you 🙏

[@swatiprabhakar2952]

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

[@monishnagarajan5917]

Great video Chandoo

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

[@richaagrawal376]

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

[@manoharsagunthalla9215]

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

[@vishalkashyap3456]

Awesome Video! 👌

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

[@DharshaniRajendran]

Sir could you help us know how did you do the time saved message at the top of the graph??

[@hari60700]

Very useful one.Thank u chandoo

[@EricCawaling]

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

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

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

[@Kevin.l888]

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

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

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

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

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

[@ricky-rickreddy8674]

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

[@arbazahmad7177]

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

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

[@miteshgedia2986]

Good Explain 😊

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

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

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

[@卢军宏台长心灵法-u3d]

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.

[@akilaliyanage]

Is there a video with variable rate with extra payments?

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

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

[@goodone05004]

Thanks man, are from NZ? or Aussie?

[@chandoo_]

You are welcome. I live in NZ.

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

[@310888NIVEH]

I am using the Mortgage calculator excel sheet. The only think I don't get is when I change the loan amount, term or rate, the old interest get error to #NAME? error. Am I missing something? Thank you.

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

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

[@jaswinderbhatia9665]

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

[@gowthamrajb9773]

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

[@lawrencetaylor6509]

great, but what if i miss a payment one month

[@anthonycarabaille6141]

Can I use this formula for my car loan

[@chandoo_]

Yes, you can :)

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

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

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

[@manigandanganesan5454]

Buddy Cant thank you enough

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