No video :(

How to calculate Mortage with Extra Payments in Excel

Подписаться 633 тыс.

Просмотров 32 тыс.

50% 1

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 RU-vid, 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

Опубликовано:

4 авг 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 72

@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 9 месяцев назад

Thank you so much for creating this model for us.

@anastasia_tee1384 Год назад

I love your teaching 🥰🥰thank you 🙏

@alvarojflores Год назад

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

@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

@vishalkashyap3456 Год назад

Awesome 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.

@HowToAnalyst Год назад

Thank you for sharing, great video!

@shreyajayesh Год назад

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

@monishnagarajan5917 Год назад

Great video Chandoo

@hari60700 Год назад

Very useful one.Thank u chandoo

@peepshah Год назад

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

@chandoo_ Год назад

Glad to hear that

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

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

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

@swatiprabhakar2952 Год назад

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

@chrism9037 Год назад

This is a great calculator, thanks Chandoo!

@chandoo_ Год назад

Glad you like it!

@miteshgedia2986 Год назад

Good Explain 😊

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

@arbazahmad7177 Год назад

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

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

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

@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

@snackplaylove 3 месяца назад

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)

@gowthamrajb9773 Год назад

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

@Snarsophagus 9 месяцев назад

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 3 месяца назад

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

@ricky-rickreddy8674 Год назад

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

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

@EricCawaling Год назад

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

11 месяцев назад

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

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

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

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

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

@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

@akilaliyanage Год назад

Is there a video with variable rate with extra payments?

@jaswinderbhatia9665 Год назад

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

@Kevin.l888 7 месяцев назад

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

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

@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

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

@lawrencetaylor6509 Год назад

great, but what if i miss a payment one month

@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

@goodone05004 4 дня назад

Thanks man, are from NZ? or Aussie?

@chandoo_ 4 дня назад

You are welcome. I live in NZ.

@anthonycarabaille6141 3 месяца назад

Can I use this formula for my car loan

@chandoo_ 3 месяца назад

Yes, you can :)

@manigandanganesan5454 4 месяца назад

Buddy Cant thank you enough

@christinet2089 4 месяца назад

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

@chandoo_ 4 месяца назад

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

@ashishgoel19891 11 месяцев назад

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_ 11 месяцев назад

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 11 месяцев назад

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

@chandoo_ 11 месяцев назад

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

@ashishgoel19891 11 месяцев назад

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

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

@Jarhead63 10 месяцев назад

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

@chandoo_ 10 месяцев назад

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.