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.
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!!
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
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.
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_ 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.
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
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
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!
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.
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.
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 )
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.
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.
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.
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!
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.
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??
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
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.
@@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.
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.
Thank you so much for creating this model for us.
I love your teaching 🥰🥰thank you 🙏
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!!
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
Thank you for sharing, great video!
Thanks 😊 for this awesome 👌 loan amortization calculator.
Kindly make a video on Financial Statement in power Bi.
great, but what if i miss a payment one month
Great calculator, Chandoo! One thing I would add to make this complete and that is the interest saved in relation to time saved.
Thanks for the suggestion.
Agree, knowing interest saved should be a greater motivator for additional repayments rather than simply visibility of a shorter term loan.
Can I use this formula for my car loan
Yes, you can :)
How do i change the payment term to bi-weekly?
divide interest rate with 28
multiply term with 28
and you will have bi-weekly schedule and payment details.
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.
Hmm.. good idea. I will need to research that up a bit but may be in future.
Buddy Cant thank you enough
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)
If explain how to make the graph and conditional calculations that would be helpful
Thank you. why is the =sequence not working on my Excel?
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!
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.
Wow George. Thank you so much for being with me all these years 😍
@@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.
Excellent. I was looking for this calculator with Extra Payments. Thanks Chandoo for this video !
Glad to hear that
Please, Chandoo, consider doing a 2nd part explaining how to do the graphs, the conditional formatting and how to calculate the years reduced. Thanks!
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/
Great tutorial!can u plz tell how to make the dynamic graph from scratch
Thanks, it will be great if you can make it using new dynamic function.
Is there anything that can be done to also show an offset amount?
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
Is there a video with variable rate with extra payments?
Can you do this but giving the exact new payoff date when you make extra payments?
You can see it already on the table. Find the month when your balance becomes zero.
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
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!
No help at all, you're going way to fast, and some of the things are not working
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.
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.
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.
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 )
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.
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.
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.
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!
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.
Thanks man, are from NZ? or Aussie?
You are welcome. I live in NZ.
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.
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??
Thank you for this wonderful video, Please make a 2nd part of this video with Variable rate of Interest.
Hi, why is there a discrepancy between the sum of the months saved column and the total time saved shown on the chart? Thanks
Hi Chandoo, in India, home loan interest rates changes frequently, how to calculate that scenario
Hi chandoo.. great calculator.. can u pls add different currency choosing option will be helpful for other countries also..
This is a great calculator, thanks Chandoo!
Glad you like it!
Very interesting ... what one do when there is a increase or decrease during the period of the loan especially mid month
Thank you a lot! For sharing your knowledge. 👍🏻💪🏻
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
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.
@@chandoo_ is it possible to add practically instead of theoretically? 🙂 In your example that you have shown up.
Sure. I've added the calculation to my template here - chandoo.org/wp/mortgage-calculator-with-extra-payments-excel-download/
@@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.
This is the much needed document for all the people buying home
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)
wish we could get some help on the interest rate changes
Awesome Video! 👌
Very useful one.Thank u chandoo
Great video Chandoo
Good Explain 😊