How to make a Loan Amortization Table with Extra Payments in Excel
Вставка
- Опубліковано 29 вер 2024
- Excel Templates: calonheindel.e...
Start a Print On Demand Etsy Store with Printful:
www.printful.c....
Looking to make money as a freelancer? Sign up and use Fiverr:
go.fiverr.com/....
Standing Desk:
amzn.to/3AOe32H
Book that changed my life:
amzn.to/3Q4peME
Great book on business:
amzn.to/48QOs9U
Royalty Free Music:
share.epidemic...
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!
this is the most awesome thing I've ever seen. My life now complete. Thank you.
Hahaha thank you!
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
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
This was so simple! Thank you so much!
Thanks for the support!
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.
Used your video to create one on my new vehicle. Thank you very much!
Glad we could help!
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!
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?
Simple need and clean !! Thanks
Appreciate the support!
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(....,....,....,....)"
Bro u gave us very nice knowledge.
Really appreciate.
Thanks a lot.
Thanks you! Appreciate the feedback!
YOU MADE IT EASY TO GRASP!
Very useful, simple and sweet.
Glad to hear it!
Great video man thank you
Thanks! Very helpful tool
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!
How did you lock the cell?
great explanation - thank you !
Glad to hear that! Thank you!
Super helpful. Thanks!
No problem!
Great video!
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.
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.
Super great guide!
Glad I could help!
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?
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.
Started out great. You move way too fast, which is fine because I can pause and rewind. However, you assume, for example, at marker 3:18, I know how to "lock the sum". Since I cannot see your keyboard, I have no idea how you filled the payment column and what "function" you used. Also, I don't understand why "extra payments" shows up in cell C11. Moving on to another channel.
Sorry you felt this way. Appreciate the feedback.
Is there any way to do it so that the extra payment instead of shortening the duration of the loan it lowers the monthly payment?
Not really. Most loans are set to have the same monthly payment. There may be exceptions in some cases but generally you can’t change the monthly payment on the average loan.
@@CalonHeindel I live in Mexico and here on a car loan you can pick and choose where you want the extra payments to go. They can go either to shorten the time of the loan or to bring down the monthly payment. However I don’t know and haven’t been able to find how they do the math on that
Bro way to quiet turn up the audio so I can hear your beautiful voice
Will do! Thanks for the feedback
what if i wanna add an extra payment for every 3rd month only?
If you follow this exact template you can add extra payments whenever you want and the values will adjust.
Very frustrating that you didn't show us how to lock the cells. And the voice was very quiet. Otherwise, the content is good.
I would love this for biweekly payments also
He kinda does show but I had to look it up to be confident. It’s the $ he adds in the formula.
You basically just add a $ to each character in the cell. For example F4 would be $F$4, then you double click on the cross or dot on the right bottom corner of your cell. That will copy that formula all the way down the column.
Bad volume. If I crank it up, when the adds kick in, KABOOM!
How to break weekly instalment
You would need to create more columns and change the Nper. You would need: 52 Weeks X 30 Years = 1560 weeks. The easier way would be to take a weekly payment and times it by 4 to put in the extra payment column.
step 1: Make sure your excel is in english
Haha good tip!
@@CalonHeindel Yeah, learned the hard way. I was going nuts trying to figure out why my formulas wouldnt work. lol
way to fast, hitting the play pause, back button is out of control, I would say most folks are here, bc they know nothing of the sort , slow down dude.
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.
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
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
PLEASE DO AN EXCEL TEMPLATE FOR BANK CHECK WRITING (FOR PRINTING ONTO CHECKS) THAT ALSO INTERACTS WITH THE LEDGER?
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
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”
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)"
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
Why is there no spreadsheets with a reduced payments, and not term reduced. Add an overpayment and pay less next month? Please make one....... Will be the only one on the web.
That’s a great video idea. Might be able to make that for the future
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.
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”
Good night. My monthly loan payment is 500, How can i reduce that. Plz
Well if you pay more each month you'll pay off the loan faster. Or you'd have to get a new loan in order to lower your monthly payments.
Nevermind, I saw your suggestions in the comments. Thanks. This is so helpful
Glad I could help!
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!
How do you make a one-time purchase of this program / software so you don't lose amortization spreadsheet due to internet glitch and subscriptions 🤔
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.
This was perfection - thank you.
Aw 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
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.
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
Yes that is possible. This video however shows how to create a table using a fixed rate. In the future I could think about creating a video like that.
@@CalonHeindel thanks for it
But rate is both system fix and variable rate
Also add customer change auto update loan amotozation table
Thank you but why can't excel have one of these already?!
Haha would be nice if they did!
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!
Please kindly increase the volume of video poor volume.
Yeah volume was pretty bad on this video
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
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
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!
Thank you! I can pay my car off early!
That’s awesome!
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?
Really needed to crank the volume on this one. 👎
Yeah the volume isn’t great unfortunately
yes this is nice and all, but how do i create one automatically ? Thus, within the formula bar ? And..... your sound is so low !
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!
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!
Lacking explanation and moving to quickly through the steps.
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..
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!
Thank you so much for this video, really helped me plan around putting extra payments onto my current loan.
Glad this video could help!
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.
Fantastic! Easy to follow and exactly answered some of my questions, especially doing "what if" scenarios! Thank you!
Glad this was useful for you!
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.
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!
Can you make one with the graft ?
Just posted a video showing how to make graphs for this!
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.
I HAVE TRIED QUITE A FEW TIMES WATCHING THIS VIDEO AND SLOWING IT DOWN BUT I AM STILL STUCK ON 1:42 WHEN YOU GO TO THE % AND $ SIGN AND THE RATE CHANGES TO 0% MONTHLY AND THEN IT CHANGES TO 0.46% MONTHLY. WHEN I TRY IT MINE STAYS ON 0%. ANY IDEA WHAT I MIGHT BE DOING WRONG OR COULD YOU TELL ME EXACTLY WHAT YOU ARE CLICKING ON WHEN YOU STARTING CLICKING ON IN THAT AREA? IT WOULD BE VERY HELPFUL.
Yeah so I’m just telling excel to show more decimal places. If you did the formula the same way I did. The number is 0.46%, excel just isn’t show that many decimal places so it says 0%
Suppose , customer don't know how much money he paid. He knows only the loan amount he took first and the current balance of the loan and also the interest rate and the duration of the loan. is it possible to find out the amount he paid ? Example- He took 100 dollar. and current balance is 40 dollar. Interest rate 9%. Time 12 months. How can i estimate the amount he paid ? Is it possible ?
Yes you can essentially follow the same method. You use the PMT function in excel. You just need to know the PV (Loan Amount), RATE, NPER (# of periods). Just make sure you change the rate to months. If 9% is the yearly rate in your example, then the rate you use would be 0.75% monthly (9%/12). He would have paid $8.75 per month for 12 months, for this loan.
What about when you make multiple extra payments per month and the bank calculates those extra payments against the monthly owed (not principal only payment) first so that the remaining part of the month is calculating interest on less principal.
Then don't make those payments. Won't help get your mortgage down. Call bank and see if they take principal only extra payment. If not, invest that money in the S&P and make a bulk payment at the end to finish paying mortgage earlier. Best of luck to you!
Question: I'm in the middle of paying off my home. Can I just use my current outstanding principal as the loan amount?
you could, depends on what you're trying to calculate. Certainly, the monthly payment will change because the loan amount is now different.
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.
help please when I enter =-PMT(F3,F4,F2) I get error 508 what am I doing wrong?
I ended up just dividing my rate by 12 and entering that as a decimal. That fixed it all.
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.
can you add a bi monthly equation?
thanks in advance.
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!
Need a way to change interest rate mid way after a 5year renewal
Clear and helpful
Thanks!
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
How do you this on an iPad?
That would be a little tough but you could use google sheets
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!
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!
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!
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!
I recommend a bit slower and my second request is how can we add a graft to this to show an inter section of interest and payment ,
A graph wouldn't be too hard to create based off this table. I'll work something up and throw a new video out there
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?
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
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!
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.