Oh my gosh. This was so great it let me teach my wife how we need to pay off my house payment. This was amazing and everyone really needs to watch this.
This was super helpful! I followed along and made my own table with you using my own data. Compared it with a word template version & all the math checks out! Difference is this table is superior because I can add in varying extra payments whenever I want! This is an important tool for me. Thanks so much!
Took me a while to figure out how to lock formula values for dragging the cell contents (hint: select value in the formula bar, then hit F4), but this was very helpful. I hope creating this has been profitable for you. -Cheers.
Thanks for positive feedback! Sometimes keyboard shortcuts are hard to remember or figure out. Once you learn them they really can save you some time. Glad you found this video helpful.
I'm using numbers on Mac and struggling to figure out this step. So far all I've found through searching google is directing me to locking the spreadsheet and not the individual cell. I'm going to Format>Arrange>Clicking lock. This results in the entire document locking. I've also tried performing Format>Cell>Protection>lock but am unable to find the protection tab. Any help is greatly appreciated.
Please tell me how he auto-dragged down the formulas with just keyboard commands. I want to know!! 04:28 I figured it out!! He just double clicks the fill tooltip on the bottom-right of the cell!!
Thank you very much for posting this tutorial. I am always hesitant to download "free templates" and being moderately savvy with Excel, this allowed me to build my own and manipulate it accordingly. Very Helpful!!
Thanks for this. I asked my car loan folks or I could pay off my loan quicker so that I don’t have a note when I do go for a house and they weren’t helpful but this is helpful!
If you add an IF function to the Payments, Interest, and Loan Balance columns, such as =IF(G7>0,$F$5,0) for the Payments, then you can have this properly calculate everything no matter what the term of the loan ends up being.
very useful content, after spending lots of time in google and other articles finally I landed here and now I am clear with the concept and calculation. You explained it great, thanks much!
Wow! This is exactly what I was looking for. Great job keeping things clear and simple while delivering everything that I clicked on the video to get. Thank you much! 😎✌️
Thank you very much for this fantastic video. You make it seem so easy and simple, now I just need to go step by step and make my own. I have seen other schedules like this without the extra payment and today I finally found yours and once I get started I can finally do what I have been thinking about for quite some time.
I want to make a schedule just like this but mine would be for a rent to own and I would be adding a $20.00 fee every year that would be added to the payment to allow for an increase in Taxes and Insurance throughout the loan could you include something like that in this schedule? Iwould appreciate your help very much, thanks for your help.
This schedule is not really set up for that, you would need to tweak it a little and change some of the formulas. I’m not 100% what you mean by rent to own.
It would be the same as what I have once it is completed. At he end of every year the only thing would be the payment would increase by a certain amount to cover the cost of taxes and insurance.
Thank you so much! Great video, easy to stop and build my amortization table as I went. The 'canned' loan amortization tables in Excel were hard to adjust for an extra payment. Thanks Calon!!
@@CalonHeindel Same thoughts here! I tried using an online spreadsheet tutorial for the 'canned' loan table and it didn't work for me for the extra payment. I found your video and it's a life saver!! Thank you!!
Maybe it's already been mentioned, but I believe that the bank should calculate interest monthly based on principle balance? So you should shorten the loan duration by even more time. Also don't forget to include the extra payment in your calculation of total money saved, not just the amount of payments not made.
Hi Calon, thank you a lot for your video. I have a doubt at 7:20. It seems that total interest calculated with extra payments ($2000) is taking also into account negative values. If by the moment when negative values begin to appear the loan is already cancelled, shoudn´t it take into account just positive interest values, intead of the whole column?
Hey you are correct that was a mistake on my part. The total interest would not be calculated correctly in that instance. Creating the table itself and all the values would still be done properly regardless however
Hi, it is really useful...can you please let me know how to lock the cell...if I try to drag the cell it is not taking the constant value for example payment...please guide me
Generally the keyboard shortcut is "F4" or "Fn + F4", depending on the computer. If that doesn't work you can always just add the "$" manually to lock a cell reference. When a cell is locked it should look like this: $F$3. Does this help?
@@CalonHeindel Hi thanks very much for the reply Fn+F4 works and one more thing when I apply PMT function I am getting -ve EMI value what is the reason for that?
@@santoshchoudhari3235 Are you saying that you are getting a negative number for your PMT function? Make sure your formula is "-PMT(....,....,....,....)"
You can use the “IFERROR” formula. Just put that in front of the formula, add a comma at the end, then “” (double quote) to show a blank if the formula brings an error.
Thank you so much! I've been looking for something like this to pay down our 2 homes. Nicely done! The only thing you didn't include in your demonstration was the taxes and insurance that's in the loan. Do you have another vidoe that includes this? You rock! I'm following you for other tips! 🙂
I tried to look up ‘amortization’ in the Merriam-Webster’s Collegiate Dictionary. It wasn’t in there. What is your definition of the word? Thanks, Chris
Just clear and simple explanations. thank you but how about the amortization for an FHA loan where the escrow is added each month. Thank you million time.
Hi! Could you show a video on coming up with an amortisation schedule for multiple loans (e.g. differing start dates, maturities, interest rates)? That will be very helpful, thank you!
I think this is awesome, but you mention several times to "lock" the cell and I have no clue what you are doing to lock the cell to drag down. Can you support me in this?
This is what I'm looking for right now. However, what I would lile to do is for the running balance to carry over on the next tab. I am creating a bills spread sheet. A mixture of monthly bills and loans. Every month is a the same list from the month before. I would like to enter in my payment and extra payment to see my balance. Is this possible?
Yes you could just do a formula adding the monthly payment and monthly extra payment together. Formulas can calculate using a different tab then were the formula is entered. The formula would look like this: ="Cell (payment)" + "Cell (extra payment)"
What if your mortgage or car payment is biweekly. That’s how mine are. So some months it’s actually 3 payments vs two as well. Plus interest is paid faster on biweekly payments. Is there a way I can set up the excel sheet different to reflex biweekly
I am not super familiar with semi-annually compounded loans. Are you still paying off the loan monthly? The interest is just compounded semi-annually rather than monthly?
@@SMONclips Yes you would need a bit of tweaking. You'd have to know exactly when the rate changes and correct the formulas from there. Not crazy hard to do, but could get a little annoying. That is a great video idea for the future, I will see what I can do.
@@CalonHeindel absolutely a big help! I watched a few videos on this topic, but this was def the best and the one I based my amortization schedule off of!
You could technically add another column for insurance and taxes. Insurance wouldn't be too hard because its fairly consistent. However, taxes aren't very consistent and changes over the years.
Calculating the monthly payments without Excel would be a headache. The formula can be very confusing. If you don't have excel you can use google sheets for free.
works through it very quickly, you will need to keep pausing and rewinding. to Calon, working a little slower would be much better to show the equations in excel.
Well interest normally accrues monthly. So when you make youre normally payment youre paying off all the interest due at that time. With an extra payment youre reducing the amount owed, which in turn reduces the amount of interest youll pay over the life of the loan
@@CalonHeindel Thank you, is the interest accruing daily on the outstanding loan balance in this example and then shown on a monthly basis, or is it just accruing monthly?
Oh my gosh. This was so great it let me teach my wife how we need to pay off my house payment. This was amazing and everyone really needs to watch this.
Glad you enjoyed it! Thanks for watching!
This was super helpful! I followed along and made my own table with you using my own data. Compared it with a word template version & all the math checks out! Difference is this table is superior because I can add in varying extra payments whenever I want! This is an important tool for me. Thanks so much!
Love to hear that! Glad you were able to follow along and make your own table!
9:20 9:22 9:23 9:23 9:24 9:24
Great video. My wife and I bought our first home, and now we have full transparency over our mortgage!! Thank you!!
Love to hear it! Happy home ownership!
Took me a while to figure out how to lock formula values for dragging the cell contents (hint: select value in the formula bar, then hit F4), but this was very helpful. I hope creating this has been profitable for you. -Cheers.
Thanks for positive feedback! Sometimes keyboard shortcuts are hard to remember or figure out. Once you learn them they really can save you some time. Glad you found this video helpful.
I did it!!! Thank you For the hint 👍👍👍👍
I'm using numbers on Mac and struggling to figure out this step. So far all I've found through searching google is directing me to locking the spreadsheet and not the individual cell. I'm going to Format>Arrange>Clicking lock. This results in the entire document locking. I've also tried performing Format>Cell>Protection>lock but am unable to find the protection tab. Any help is greatly appreciated.
This had me stumped too! Paused the video in just the right spot @3:21 add $ does the trick!!
i still can't get it to work. when I selected the cell and hit F4, the whole file went away. I had to open it up again. What am I doing wrong?
Wow, thanks a lot. I am here after wasting 3 hours on internet. This exactly what I have been looking for. Have great time.
Glad this video could help! Wish you found it sooner lol
Best amortization schedule video on TH-cam that I’ve seen, watched so many. This was simple and easy thanks!
Glad to hear that! Appreciate the feedback!
Nevermind, I saw your suggestions in the comments. Thanks. This is so helpful
Glad I could help!
I was looking at some tutorial videos on how to create a Loan Amortization Table, and I found this is the best one! Thank you Calon
Appreciate the support!
this is the most awesome thing I've ever seen. My life now complete. Thank you.
Hahaha thank you!
This video was so good that I was expecting at least a million subscribers on this channel. This channel needs to be shared!
Hahaha appreciate the support! Maybe one day!
Exactly what I was looking for. Thank you!! I hope you are still making videos like this, it was perfect. Wish you the best
Glad this was a good video for you! Still making videos!
i have gone through multiple videos no one teachs it so good and so simple , simply awesome. Good Job man, Thanks a lot for sharing.
Love to hear that! Thanks for the feedback!
This was legit! I was looking for something like this and being able to keep track at home in an Excel spreadsheet. Appreciate you man
Love to hear it! Appreciate the feedback!
great stuff man. I took it even deeper with further calculations, but you provided the foundations with this. Thanks!
Please tell me how he auto-dragged down the formulas with just keyboard commands. I want to know!! 04:28
I figured it out!! He just double clicks the fill tooltip on the bottom-right of the cell!!
Hahaha yeah that’s how to do it
Thank you very much for posting this tutorial. I am always hesitant to download "free templates" and being moderately savvy with Excel, this allowed me to build my own and manipulate it accordingly. Very Helpful!!
Glad this video was able to help! I appreciate the feedback, thank you!
Your video was a big help with visualizing my student loans. Thank you
Thanks!
Happy to help!
I was looking for something like this ! Very well explained and straight to the point. Thank you so much, you are the goat🐐
Hahaha thanks I appreciate the support!
Thanks for this. I asked my car loan folks or I could pay off my loan quicker so that I don’t have a note when I do go for a house and they weren’t helpful but this is helpful!
Glad this could help you out!
If you add an IF function to the Payments, Interest, and Loan Balance columns, such as =IF(G7>0,$F$5,0) for the Payments, then you can have this properly calculate everything no matter what the term of the loan ends up being.
Agreed. This function will show a zero instead of a negative loan balance. Much easier to identify the last payment due.
Thank you. This made all the difference.
I was thinking what went wrong with my model turning negative when having prepayments. Your explanation was right on point! Thanks for sharing!
very useful content, after spending lots of time in google and other articles finally I landed here and now I am clear with the concept and calculation. You explained it great, thanks much!
Glad this was helpful for you! Appreciate the support and feedback!
Thank you Calon! I was able to figure it out based upon your video. Very practical -- both the video and my new amortization table!
Wow! This is exactly what I was looking for. Great job keeping things clear and simple while delivering everything that I clicked on the video to get. Thank you much! 😎✌️
Glad this video was helpful!
Thank you very much for this fantastic video. You make it seem so easy and simple, now I just need to go step by step and make my own. I have seen other schedules like this without the extra payment and today I finally found yours and once I get started I can finally do what I have been thinking about for quite some time.
I want to make a schedule just like this but mine would be for a rent to own and I would be adding a $20.00 fee every year that would be added to the payment to allow for an increase in Taxes and Insurance throughout the loan could you include something like that in this schedule? Iwould appreciate your help very much, thanks for your help.
This schedule is not really set up for that, you would need to tweak it a little and change some of the formulas. I’m not 100% what you mean by rent to own.
It would be the same as what I have once it is completed. At he end of every year the only thing would be the payment would increase by a certain amount to cover the cost of taxes and insurance.
Thank you so much for this video, really helped me plan around putting extra payments onto my current loan.
Glad this video could help!
This video was the biggest life saver!!!!!! Thank you so much
Glad this could help!
This was perfection - thank you.
Aw thanks! Appreciate the support!
I checked this against my mortgage calculator online and its matches!
It works and its simple to use!
Thank you so much! Great video, easy to stop and build my amortization table as I went. The 'canned' loan amortization tables in Excel were hard to adjust for an extra payment. Thanks Calon!!
Glad to hear that! I appreciate the support! Thanks!
@@CalonHeindel Same thoughts here! I tried using an online spreadsheet tutorial for the 'canned' loan table and it didn't work for me for the extra payment. I found your video and it's a life saver!! Thank you!!
@@kramerproaxe1895 Awesome! Thanks for the feedback, much appreciated! Glad this video was able to help you
very simple, easy to follow and straight to the point. Thanks Calon!
Glad this was helpful!
Super helpful!!!
Thank you! You move a bit too quick for me, but definitely helpful!
Thanks for the comment! Yeah you might need to slow it down or watch it a few times to get all the information.
This was so simple! Thank you so much!
Thanks for the support!
EXCELLENT -Exactly what I was looking for ! thanks.
Glad you found this video useful!
Thanks!
Appreciate the support!
Thanks for this! Would love to learn how to calculate total interest saved when making an extra principal only payment. Any suggestions?
You my friend just earned a subscriber
Haha love the support! Thank you!
Amazing Video! thank you very much for the smooth walk through! please keep us up with your wisdom!
Haha I appreciate that! Glad this was an easy video to follow along with.
Really helpful. Thanks for the video
Appreciate the support! Glad this video was useful for you
Wonderfully helpful!!!! THANK YOU!!!
Glad you found this helpful!
Great video..very helpful, straight to the point
Appreciate the feedback!
That was brilliant mate thank you! I use MAC Numbers so I had to modify the formulas a bit and find the matching one for PMT..but it worked..Stoked! 🤓
Happy to hear that! Glad you were able to tweak it to make it work.
SOOOO Helpful!!! Thank you!!!
Glad you thought so!
This is exactly what I needed. Well done and thanks for the video!!!
Glad this could help!
Fantastic! Easy to follow and exactly answered some of my questions, especially doing "what if" scenarios! Thank you!
Glad this was useful for you!
Awesome, thanks for sharing your mad skills, this really is powerful tool and will me and others plan!!! Thanks again
Glad this was useful! Appreciate the support!
Maybe it's already been mentioned, but I believe that the bank should calculate interest monthly based on principle balance? So you should shorten the loan duration by even more time. Also don't forget to include the extra payment in your calculation of total money saved, not just the amount of payments not made.
Hi Calon, thank you a lot for your video. I have a doubt at 7:20. It seems that total interest calculated with extra payments ($2000) is taking also into account negative values. If by the moment when negative values begin to appear the loan is already cancelled, shoudn´t it take into account just positive interest values, intead of the whole column?
Hey you are correct that was a mistake on my part. The total interest would not be calculated correctly in that instance. Creating the table itself and all the values would still be done properly regardless however
@@CalonHeindel Ok, thank you Calon
Simple need and clean !! Thanks
Appreciate the support!
Great video! Thanks!
Thanks for the support John!
Thank you! Super easy to follow on Google Sheets. For those who want to lock the cell in the formula just add $ in front of the atrribute. Ex: $B$3
Exactly! Thanks for explaining that
Hi, it is really useful...can you please let me know how to lock the cell...if I try to drag the cell it is not taking the constant value for example payment...please guide me
Generally the keyboard shortcut is "F4" or "Fn + F4", depending on the computer. If that doesn't work you can always just add the "$" manually to lock a cell reference. When a cell is locked it should look like this: $F$3. Does this help?
@@CalonHeindel Hi thanks very much for the reply Fn+F4 works and one more thing when I apply PMT function I am getting -ve EMI value what is the reason for that?
@@santoshchoudhari3235 Are you saying that you are getting a negative number for your PMT function? Make sure your formula is "-PMT(....,....,....,....)"
This is very helpful to better understand our own home mortgage loan. Thank you!
Glad I could help!
Love you content.. please keep it coming
Appreciate it! Thanks for the support!
Great job Calon!
Nice and simple. I like it. How do you change the negative to blank or zero? I am getting a #NUM! error message.
You can use the “IFERROR” formula. Just put that in front of the formula, add a comma at the end, then “” (double quote) to show a blank if the formula brings an error.
Thank you so much! I've been looking for something like this to pay down our 2 homes. Nicely done! The only thing you didn't include in your demonstration was the taxes and insurance that's in the loan. Do you have another vidoe that includes this? You rock! I'm following you for other tips! 🙂
I don’t have a video that includes that right now. I’ll have to do that in the future. Thanks for the support!
Easy to follow and very useful!
PLEASE DO AN EXCEL TEMPLATE FOR BANK CHECK WRITING (FOR PRINTING ONTO CHECKS) THAT ALSO INTERACTS WITH THE LEDGER?
Super helpful, thank you so much!
Thanks for the support!
Used your video to create one on my new vehicle. Thank you very much!
Glad we could help!
This was great and very helpful. Thanks for doing this!!
No problem!
I wish they would teach this in school!
They should!
Bro u gave us very nice knowledge.
Really appreciate.
Thanks a lot.
Thanks you! Appreciate the feedback!
Clear and helpful
Thanks!
Can you make one with the graft ?
Just posted a video showing how to make graphs for this!
YOU MADE IT EASY TO GRASP!
Thanks! Very helpful tool
I tried to look up ‘amortization’ in the Merriam-Webster’s Collegiate Dictionary. It wasn’t in there. What is your definition of the word? Thanks, Chris
“The action or process of gradually writing off the initial cost of an asset”
Thank you for sharing - are you sure the total interest formula works with overpayments?
If the difference from overpayment is entered in extra payments column then yes
Just clear and simple explanations. thank you but how about the amortization for an FHA loan where the escrow is added each month. Thank you million time.
Thanks for the support much appreciated! That could be a good video to create in the future. Thanks for the idea!
Could you provide the formula for a LTV column? How that changes over time with the extra payment?
That would be a good idea for a future video. I’ll see what I can do
@@CalonHeindel thanks so much. Great content. You got a new subscriber.
@@Mj-kb6ig Thanks for the support!
Can you create an amortization chart for biweekly payments?
Great idea for an upcoming video! I’ll have to get working on that
Thank you for the clear and well detailed explanation. Question..does that extra payment feature apply solely to the principal balance?
Very useful, simple and sweet.
Glad to hear it!
Hi! Could you show a video on coming up with an amortisation schedule for multiple loans (e.g. differing start dates, maturities, interest rates)? That will be very helpful, thank you!
Great video man thank you
I think this is awesome, but you mention several times to "lock" the cell and I have no clue what you are doing to lock the cell to drag down. Can you support me in this?
Me too 🤦
select the cell value to lock in the formula bar then press F4
great explanation - thank you !
Glad to hear that! Thank you!
This is what I'm looking for right now. However, what I would lile to do is for the running balance to carry over on the next tab.
I am creating a bills spread sheet. A mixture of monthly bills and loans. Every month is a the same list from the month before. I would like to enter in my payment and extra payment to see my balance. Is this possible?
Yes you could just do a formula adding the monthly payment and monthly extra payment together. Formulas can calculate using a different tab then were the formula is entered. The formula would look like this: ="Cell (payment)" + "Cell (extra payment)"
What if your mortgage or car payment is biweekly. That’s how mine are. So some months it’s actually 3 payments vs two as well. Plus interest is paid faster on biweekly payments.
Is there a way I can set up the excel sheet different to reflex biweekly
How could you fit late payments in this schedule?
This table isn’t set up for late payments, however that is a great idea for a future video. I’ll see what I can do
Super helpful. Thanks!
No problem!
Hi Calon, your table shows monthly compounding. What if interest is compounded semi-annually, as it usually does in Canadian Mortgage calculations.
I am not super familiar with semi-annually compounded loans. Are you still paying off the loan monthly? The interest is just compounded semi-annually rather than monthly?
absolutely perfect
Appreciate the support! Thanks for watching!
@@CalonHeindel just curious, what if the rate changes? For instances with variable rates. Would it be a lot of tweaking ?
@@SMONclips Yes you would need a bit of tweaking. You'd have to know exactly when the rate changes and correct the formulas from there. Not crazy hard to do, but could get a little annoying. That is a great video idea for the future, I will see what I can do.
Wow, this is awesome, thank you!
Glad we could help!
@@CalonHeindel absolutely a big help! I watched a few videos on this topic, but this was def the best and the one I based my amortization schedule off of!
@@ngee4925 Thats awesome to hear! Thank you for your feedback!
@@CalonHeindel anytime!
Can it be added taxes and insurance to this Amortization table. I love the way you explained this tutorial.
You could technically add another column for insurance and taxes. Insurance wouldn't be too hard because its fairly consistent. However, taxes aren't very consistent and changes over the years.
Awesome video, thanks for the help!
Thanks for watching!
Thank you but why can't excel have one of these already?!
Haha would be nice if they did!
Hi!im doing this in google sheet, the only thing I can’t figure out is how to lock a cell to ‘$"… I’m Swedish so for me it’s "kr".. can you help me?
Not sure what the keyboard shortcut for you would be. You could manually enter two “$” to lock the cell as well
Can you show how to calculate the installment amount manually without using excel with just loan amount, interest rate & the period of installment?
Calculating the monthly payments without Excel would be a headache. The formula can be very confusing. If you don't have excel you can use google sheets for free.
can you add a bi monthly equation?
thanks in advance.
How can you do this with a construction loan where the first year of the loan is interest only?
Need a way to change interest rate mid way after a 5year renewal
Makes so much sense!
Glad to hear that!
works through it very quickly, you will need to keep pausing and rewinding. to Calon, working a little slower would be much better to show the equations in excel.
Appreciate the feedback! Hopefully you were able to complete the formulas without too much rewinding.
Super great guide!
Glad I could help!
How to manage the Escrow amounts that goes from monthly payments?
What would be the best way to incorporate having a extra payment that applies to interest only or interest first?
Well interest normally accrues monthly. So when you make youre normally payment youre paying off all the interest due at that time. With an extra payment youre reducing the amount owed, which in turn reduces the amount of interest youll pay over the life of the loan
@@CalonHeindel Thank you, is the interest accruing daily on the outstanding loan balance in this example and then shown on a monthly basis, or is it just accruing monthly?