How to make a Loan Amortization Table with Extra Payments in Excel
ฝัง
- เผยแพร่เมื่อ 15 ต.ค. 2020
- Excel Templates: calonheindel.etsy.com/
Start a Print On Demand Etsy Store with Printful:
www.printful.com/a/8269674:5b...
Looking to make money as a freelancer? Sign up and use Fiverr:
go.fiverr.com/visit/?bta=7370...
Standing Desk:
amzn.to/3AOe32H
Book that changed my life:
amzn.to/3Q4peME
Great book on business:
amzn.to/48QOs9U
Royalty Free Music:
share.epidemicsound.com/64td0d
Learn how to create a loan amortization table with an added extra payments feature in Excel. Learn how much interest and principle you will pay each month on a loan. All you need to know is the loan amount, interest rate, and length of the loan. Excel will do all the calculations for you.
Learn how adding extra payments to your monthly loan payments will save you lots of money in the long run. Easy to follow and understand.
Disclaimer & Full Disclosure: We are a a participant in the Amazon Associates Program, So If you purchase something from our affiliate links will get a small commission with no extra cost to you. This makes it possible for us to keep producing VIDEOS!
°We NEVER collect, store, use, or have access to any data about you.
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!
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!
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!
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?
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!
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!
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!
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
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!
Fantastic! Easy to follow and exactly answered some of my questions, especially doing "what if" scenarios! Thank you!
Glad this was useful for you!
Nevermind, I saw your suggestions in the comments. Thanks. This is so helpful
Glad I could help!
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!
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!
very simple, easy to follow and straight to the point. Thanks Calon!
Glad this was helpful!
Thank you Calon! I was able to figure it out based upon your video. Very practical -- both the video and my new amortization table!
Thank you so much for this video, really helped me plan around putting extra payments onto my current loan.
Glad this video could help!
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.
This is exactly what I needed. Well done and thanks for the video!!!
Glad this could help!
This was great and very helpful. Thanks for doing this!!
No problem!
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!
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!
Great job Calon!
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!
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!
Used your video to create one on my new vehicle. Thank you very much!
Glad we could help!
This was perfection - thank you.
Aw thanks! Appreciate the support!
This is very helpful to better understand our own home mortgage loan. Thank you!
Glad I could help!
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!
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.
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.
SOOOO Helpful!!! Thank you!!!
Glad you thought so!
This video was the biggest life saver!!!!!! Thank you so much
Glad this could help!
Wonderfully helpful!!!! THANK YOU!!!
Glad you found this helpful!
Awesome video, thanks for the help!
Thanks for watching!
Great video man thank you
Super helpful, thank you so much!
Thanks for the support!
Simple need and clean !! Thanks
Appreciate the support!
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.
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!
Great video!
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
Thanks! Very helpful tool
great explanation - thank you !
Glad to hear that! Thank you!
THANK YOU!
You my friend just earned a subscriber
Haha love the support! Thank you!
YOU MADE IT EASY TO GRASP!
Super helpful. Thanks!
No problem!
I checked this against my mortgage calculator online and its matches!
It works and its simple to use!
Great video..very helpful, straight to the point
Appreciate the feedback!
Great video! Thanks!
Thanks for the support John!
Easy to follow and very useful!
Makes so much sense!
Glad to hear that!
Love you content.. please keep it coming
Appreciate it! Thanks for the support!
Super great guide!
Glad I could help!
Really helpful. Thanks for the video
Appreciate the support! Glad this video was useful for you
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 useful, simple and sweet.
Glad to hear it!
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!
Bro u gave us very nice knowledge.
Really appreciate.
Thanks a lot.
Thanks you! Appreciate the feedback!
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!
Clear and helpful
Thanks!
Thanks!
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.
I wish they would teach this in school!
They should!
Thanks you!!!
No problem!
Super good and helpful
This was really helpful. Thank you!
Question. My student loan has a federal and provincial component each with different interest rates, however, I only make 1 payment (not sure how it gets divided up between the 2). Any ideas on the best way to organize this table? Not sure if I should treat it as 2 separate loans or if there's a way to keep it together.
That's a great question. I would assume you'd need to divide into two amortization schedules. Then they probably combine the two and give you a single payment. I haven't heard of a loan with 2 interest rates before. However it is possible the interest rates are combined to get you 1 amortization schedule. I tried researching this a bit, but couldn't find much on it. Sorry I couldn't be of more help.
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!
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.
nicely done, wow
Appreciate that!
This is so awesome, thank you!!! I am just purchasing a new home and promised myself to pay more each month. This table will motivate me to do so... I wonder, I will have a monthly PMI for some time and I want to shorten that time with extra payments as well... where in the spreadsheet would I add the PMI and calculate when it will disappear from my payments?
PMI is no longer collected on conventional loans once you hit 80% of your LTV (Loan to Value ratio). So, say you have an original loan balance of $100,000, once you owe less than $80,000 your lender should stop collecting PMI. So, add a column after the monthly payment amount for your PMI, because that's an amount that doesn't change, you'll include that column into your formula until you have paid off 20% of your original balance, then just zero out the remainder of that column. Hopefully that helps!
Thank you. Did you skip adding the extra payments to the total paid under total interest paid?
If you pay extra for a particular month it wont pay off interest for that month. However, giving extra payments will help you pay off the loan faster so you will be paying less in interest overall.
PLEASE DO AN EXCEL TEMPLATE FOR BANK CHECK WRITING (FOR PRINTING ONTO CHECKS) THAT ALSO INTERACTS WITH THE LEDGER?
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!
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
Love this!! Used to have it right on my mortgage web site but they removed it...am so angry
Happy to 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(....,....,....,....)"
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.
Great video and thank you, aside from the application it actually help me in understanding how these loans worked. Is there a way to set this up for an existing loan that is already a few years old, along the way I made extra payments that I didn't keep track of? Since there are those extra payments I made I don't think I would be able to use my original loan information, I would think that would be a bit off.
It would be hard to figure out the extra payments you had previously made. If you know the original loan amount, rate, and number of periods you could determine how much more time you have on the loan. To be 100% accurate in the monthly payments you would need to know when and for how much you made the extra payments.
@@CalonHeindel thanks again. Just make sure I am following you correctly. Although i have the current maturity date, the current balance, payment , and rate there is no way for me create a custom amortization schedule over a specific period of time, my current maturity date in my case? The bank gives me the updated date that it will paid off and what not I just like the flexibility of having it on a spreadsheet and being able to modify the extra payments on the fly and see what type of effect it has. Again thank you for taking the time to respond i really do appreciate it.
@@agent2130 Well what you could do is plug in the original numbers to the model. Your original loan, rate, periods. This will get you the correct monthly payment. Then take the original loan balance and subtract the remaining loan balance, this will get you the total extra payments you have put towards the loan. Put that number into the extra payments section for period 1. Then change period 2 to the current period youre at on the loan. Then you can looks at the schedule from there and enter extra payments. Does that make sense?
@@CalonHeindel Yea absolutely does. I will give that a shot thanks again.
How to manage the Escrow amounts that goes from monthly payments?
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?
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.
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
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.
How can you do this with a construction loan where the first year of the loan is interest only?
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.
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
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.
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”
The volume on this video is Low, But thanks for the lesson very helpful.
Yeah apologies for the audio, but glad this video could help
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
Not sure if I did this wrong but mine does not hit $0 on the loan balance it stops at $7.34 and then goes negative. Did i mess it up?
How did you lock the cell?
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.
How to you auto fill the rest of the column without dragging it down?
There is a small box in the bottom right corner of the cell. Double click it and the column will autofill.
Need a way to change interest rate mid way after a 5year renewal
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