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!!
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!
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!
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.
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, 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(....,....,....,....)"
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!!
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.
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
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
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! 🙂
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)"
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?
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.
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?
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 would you calculate a 15 year mortgage amortization where the monthly payment is applied bi-weekly? Unlike most banks, mine will apply the payment to help save us on interest instead of holding it until the remainder of the monthly comes in the next 2 weeks. I can get the table to calculate the monthly, but it's way off because of how my payment applies. Any ideas?
In order to get bi-weekly payments you essentially need to divide the payment calculated in this video by 2. It sounds like your loan takes interest into account differently as well. There are lots of videos on UA-cam telling you how to create a bi-weekly table. However, I tried finding a video for you and the special loan but couldn’t find a good one from my quick search. I’m sorry!
All good and I appreciate the response!! I’ll keep looking on here as well. It’s mainly just taking the normal monthly, divide in half, paid every other week. I can’t get to how to calculate interest since the payment applies bi-weekly, but someone has to know of a way. Thank you again!!
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!
You can use “fn + F4” on your keyboard. If that doesn’t work you just need to add 2 dollar signs. For example to lock/anchor cell “B4” it would look like “$B$4”
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?
Im so newbie to excel. Doesnt seem to lock cell formula and drag them down. Can you show me how. Also how do you drag the cell contents to 360 rows so quickly
@@_sonicfive You essentially would have to divide the annual interest rate by 365. Then each period would be days instead of months. That would be a good video idea for the future. I will try and work on that.
Hey Calon Heindel. Is it possible to have an interest rate that is not fixed over time with regards to paying back that loan? Like can the 5.50% increase to 7.50% 24 months down the road?
It is possible to do that if you know when your interest rate will be changing. You would have to set up the formulas differently in order to capture the changing interest rate. With a higher interest rate your monthly payments would increase. The table in this video isnt setup to account for a changing interest rate.
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.
Great video. Very helpful, but I have a question about a possible formula that could be used for a consistent extra payment. Let's say I want to make an extra payment in every January in order to pay off my mortgage faster. Plugging in manually that extra amount more than 15x would be time consuming. Is there a formula in excel I could use for quick computing? I'd highly appreciate if you answer it. Thank you.
Unfortunately, there is no formula if don't pay extra consistently on every payment. The bank would literally have to keep track of each payment and adjusts accordingly. There is, however, a formula if you make extra on EVERY payment. Here it is: P(n) = Pr^n - A (1 - r^n)/(1-r) P(n) = Principle remaining after n payments P = Initial Principle r = 1 + i, where i is periodic interest n = number of payments A = periodic payment So, using example from the video. P = 150000, r = 1 + 0.055/12, n = 360, P(n) = 0, A = 951.68 (instead of 851.68), and you find n 0 = 150000 (1 + 0.055/12)^n - 951.68 (1 - (1 + 0.055/12)^n) / (1 - (1 + 0.055/12)) n = 280.262 www.wolframalpha.com/input?i=0+%3D+150000+%281+%2B+0.055%2F12%29%5En+-+951.68+%281+-+%281+%2B+0.055%2F12%29%5En%29+%2F+%281+-+%281+%2B+0.055%2F12%29%29 So, just like in the video with $100 extra on every payment, you will pay off on 281st payment.
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!
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!
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
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
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?
Best amortization schedule video on UA-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!
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!
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
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!
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.
Thanks!
Happy to help!
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!
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 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 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!
Thank you Calon! I was able to figure it out based upon your video. Very practical -- both the video and my new amortization table!
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!
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!
Thanks!
Appreciate the support!
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!
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.
very simple, easy to follow and straight to the point. Thanks Calon!
Glad this was helpful!
I checked this against my mortgage calculator online and its matches!
It works and its simple to use!
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.
Thank you so much for this video, really helped me plan around putting extra payments onto my current loan.
Glad this video could help!
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 video was the biggest life saver!!!!!! Thank you so much
Glad this could help!
Can you make one with the graft ?
Just posted a video showing how to make graphs for this!
You my friend just earned a subscriber
Haha love the support! Thank you!
This was so simple! Thank you so much!
Thanks for the support!
This was perfection - thank you.
Aw thanks! Appreciate the support!
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
Fantastic! Easy to follow and exactly answered some of my questions, especially doing "what if" scenarios! Thank you!
Glad this was useful for you!
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.
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.
Great job Calon!
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
SOOOO Helpful!!! Thank you!!!
Glad you thought so!
PLEASE DO AN EXCEL TEMPLATE FOR BANK CHECK WRITING (FOR PRINTING ONTO CHECKS) THAT ALSO INTERACTS WITH THE LEDGER?
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”
Simple need and clean !! Thanks
Appreciate 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
This is exactly what I needed. Well done and thanks for the video!!!
Glad this could help!
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!
Great video..very helpful, straight to the point
Appreciate the feedback!
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
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
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.
Love you content.. please keep it coming
Appreciate it! Thanks for the support!
Thank you for the clear and well detailed explanation. Question..does that extra payment feature apply solely to the principal balance?
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.
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
Great video! Thanks!
Thanks for the support John!
Wonderfully helpful!!!! THANK YOU!!!
Glad you found this helpful!
Easy to follow and very useful!
Really helpful. Thanks for the video
Appreciate the support! Glad this video was useful for you
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!
This is very helpful to better understand our own home mortgage loan. Thank you!
Glad I could help!
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!
Thank you but why can't excel have one of these already?!
Haha would be nice if they did!
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)"
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
Clear and helpful
Thanks!
EXCELLENT -Exactly what I was looking for ! thanks.
Glad you found this video useful!
How do you this on an iPad?
That would be a little tough but you could use google sheets
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!
I wish they would teach this in school!
They should!
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?
What happen next month when i missed one months installment please answer
It depends on the loan, but generally it’ll hurt your credit score and could take longer to pay off the loan
@@CalonHeindel thanks..
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
Used your video to create one on my new vehicle. Thank you very much!
Glad we could help!
Bro u gave us very nice knowledge.
Really appreciate.
Thanks a lot.
Thanks you! Appreciate the feedback!
How can you do this with a construction loan where the first year of the loan is interest only?
YOU MADE IT EASY TO GRASP!
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
How would you calculate a 15 year mortgage amortization where the monthly payment is applied bi-weekly? Unlike most banks, mine will apply the payment to help save us on interest instead of holding it until the remainder of the monthly comes in the next 2 weeks. I can get the table to calculate the monthly, but it's way off because of how my payment applies. Any ideas?
In order to get bi-weekly payments you essentially need to divide the payment calculated in this video by 2. It sounds like your loan takes interest into account differently as well. There are lots of videos on UA-cam telling you how to create a bi-weekly table. However, I tried finding a video for you and the special loan but couldn’t find a good one from my quick search. I’m sorry!
All good and I appreciate the response!! I’ll keep looking on here as well. It’s mainly just taking the normal monthly, divide in half, paid every other week. I can’t get to how to calculate interest since the payment applies bi-weekly, but someone has to know of a way. Thank you again!!
@@coreyh2565 Good luck!
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!
Super helpful, thank you so much!
Thanks for the support!
I am not very efficient with use of excel. In the second column of your amortization schedule you locked a cell. How is this done.
You can use “fn + F4” on your keyboard. If that doesn’t work you just need to add 2 dollar signs. For example to lock/anchor cell “B4” it would look like “$B$4”
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?
How to manage the Escrow amounts that goes from monthly payments?
Very useful, simple and sweet.
Glad to hear it!
Im so newbie to excel. Doesnt seem to lock cell formula and drag them down. Can you show me how. Also how do you drag the cell contents to 360 rows so quickly
Need a way to change interest rate mid way after a 5year renewal
Minor detail: isn’t the interest rate calculated daily in most cases?
It really depends on the loan. Most home mortgages calculate interest monthly
Thanks.
How would I calculate daily accrual for a credit card example ?
@@_sonicfive You essentially would have to divide the annual interest rate by 365. Then each period would be days instead of months. That would be a good video idea for the future. I will try and work on that.
How can I get my extra payments to recalculate my loan amount once I have added one?
It should automatically update if you used the same formulas in the video.
How did you lock the cell?
This was great and very helpful. Thanks for doing this!!
No problem!
Great video man thank you
can you add a bi monthly equation?
thanks in advance.
Hey Calon Heindel. Is it possible to have an interest rate that is not fixed over time with regards to paying back that loan? Like can the 5.50% increase to 7.50% 24 months down the road?
It is possible to do that if you know when your interest rate will be changing. You would have to set up the formulas differently in order to capture the changing interest rate. With a higher interest rate your monthly payments would increase. The table in this video isnt setup to account for a changing interest rate.
Thanks! Very helpful tool
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.
Hey Calon. I need some help with the amortization chart. Mine is a little bit more complicated than this. I am open to paying! Let me know
Send me any email at madesimple001@gmail.com for any business inquiries.
Great video. Very helpful, but I have a question about a possible formula that could be used for a consistent extra payment. Let's say I want to make an extra payment in every January in order to pay off my mortgage faster. Plugging in manually that extra amount more than 15x would be time consuming. Is there a formula in excel I could use for quick computing? I'd highly appreciate if you answer it. Thank you.
You could enter that amount for the first year. Copy that and duplicate it down to the end of the amortization table
Unfortunately, there is no formula if don't pay extra consistently on every payment. The bank would literally have to keep track of each payment and adjusts accordingly. There is, however, a formula if you make extra on EVERY payment. Here it is:
P(n) = Pr^n - A (1 - r^n)/(1-r)
P(n) = Principle remaining after n payments
P = Initial Principle
r = 1 + i, where i is periodic interest
n = number of payments
A = periodic payment
So, using example from the video. P = 150000, r = 1 + 0.055/12, n = 360, P(n) = 0, A = 951.68 (instead of 851.68), and you find n
0 = 150000 (1 + 0.055/12)^n - 951.68 (1 - (1 + 0.055/12)^n) / (1 - (1 + 0.055/12))
n = 280.262
www.wolframalpha.com/input?i=0+%3D+150000+%281+%2B+0.055%2F12%29%5En+-+951.68+%281+-+%281+%2B+0.055%2F12%29%5En%29+%2F+%281+-+%281+%2B+0.055%2F12%29%29
So, just like in the video with $100 extra on every payment, you will pay off on 281st payment.