OHH!!! If you love comedy, how about Tragic Comedy? If so, you will love the tragic-truth comedy about amortization table: The Amortization Table shows how much the bank steals (contractually extracts) from you each payment period!! Thnaks for watching even though it is not solid comedy!
Thank you so very much for not only walking through this process but sharing your worksheet and formulas! I was trying to figure out a private loan we have (that I pay overage on) and this is the first resource I found that let me accomplish what I was trying to figure out. which is what is the actual interest and balance of my loan! Again thank you!!!
Your Amortization table with variable rate is an excellent tool planning tool, and your presentation made it very easy to understand the conditional formulas you utilized. Thank you so much.
Awesome video taking into account that these days everybody is trying to manage a better deal with the bank. Your video gives us the possibility to play with figures and find out what is going to be the monthly rate in case we make an consistent early payment, if we have the money of course
Thanks so much for this. Couldn't work it out myself and couldn't find a solution anywhere until I found your video. Clearly, not many people like bankers! I laughed at your opening facetious remark '...that's how much the banker steals from your payment...' - so even Excel tutors don't like them, eh? Just like the rest of us, then, who don't like them either! Well, I guess those bankers brought all that public 'dislike' on themselves by their own choices to rip-off, rather than serve customers. That's part of the price they now pay for their crimes. Reputational damage, self-inflicted. Anyway, thanks for helping to solve my initial problem regarding how to create a variable interest calculator. So pleased to have found your video! Can't express my gratitude enough. The calculator my team is attempting to build is intended to be used by financial counsellors (in Australia - although hopefully this comment might inspire counsellors in other countries to build something similar too) who are seeking to assist people in financial distress. The idea is for the resulting data (charts, graphs, etc) to provide a clear visual representation of where things went wrong so that legal arguments can be presented to judges and regulators on behalf of these financial abuse victims and hopefully save a few more homes from being unjustly foreclosed upon. Ultimately, the team would like to build a multiple loan calculator with variable interest capabilities, as many people affected have been encouraged to 'invest' in multiple properties, so multiple loans are involved; and, each loan is typically variable in nature. Wish me luck! And, thanks again. BTW, I liked and have subscribed to ALL your videos now. Peace.
I have 4 videos for amortization table videos. If you search for this: 'Excel Amortization Table Tricks' Click on the link for the playlist and you will find all 4. I hope there will be enough tricks in these to get your amort table done!
Loved this tutorial. I ran across it while looking for a solution for one of my clients. I did make one change so far in my version of the spreadsheet. In the version you have you can not make a change to the Length of the loan in Years. I modified the PMT function by changing the nper to $C$9*$F$9-A12, so cell B13 reads =-PMT(F14/$F$9,COUNT(A14:$A$372),E13). I can't see any problem this modification causes at this point. Looking forward to viewing your other videos
I have no idea. I love Macs and used them exclusively in the early 1990s, but I had to switch to a PC because I worked exclusively in the business world and working in the business world and using a Mac is too hard to do. Basically it came down to the fact that if I wanted a job, I had to learn the PC, not a Mac. That means that now in 2011 I am Mac illiterate. In addition, Excel for the Mac and Pc are totally different. Sorry about that.
Fibonacci numbers are the following sequence of numbers: 0,1,1,2,3,5,8,13,21,34,55,89,144... In Excel: A1 = 0 A2 = 1 A3 = =SUM(A1:A2) Copy SUM formula down.
I remembered that when you you go the bank to get a loan you have 2 options (at least in my case):1. equally monthly payments (like in the video) or 2. Descending monthly payments (the first rate is the biggest while the last one is smallest). Moreover the difference between the first and the last rate is quite important (the last rate could be half of the first one). How can we simulate this in Excel? Once again awesome video!
nice explanation Mike. I had tried to calculate variable interest due and ppl amt using excel's ipmt and ppmt, but couldn't get it to work with variable rates. I am trying to replicate Australian variable interest home loans. They are generally calculated on the daily balance, and some allow additional sums into an 'offset' account, which reduces the daily outstanding balance. I should be sweet now. Thanks.
@olsoncor If you make bi-period payments, the annual periods will be 26 (half of 52 weeks). However, given the APR, this calculation may generate a small problem based on my understanding as 26 bi-weekly periods = 26x14=364 days, which is 1 day less than 365 natural days per year. This could generate an accounting issue and you need to be careful while calculating your period rate.
OK, time the two methods then, Alt + = is much faster than =A1+A2. Small differences do matter, IF you use Excel 8 hours a day, otherwise, it does not matter much.
Try this video: Excel Finance Trick 9: Daily Interest But Monthly Deposits?! Otherwise, I would read the contact and figure out the math used for that particular contact.
Hi I was wondering if there is an option for irregular amounts being paid in amortisation table so if the stranded payment is paid weekly when the loan is monthly?
Hi, What is the difference of locking pressing f4 twice rather than once when youre copying it down the numbers. When I do it and copy it on either f4 once or twice, its the same outcome.
Mike, I am stumped on how to apply the following. Australian banks usually lend on a std variable rate. They calculate interest daily, and add it to the loan balance monthly. I presume it is this figure pmt is calculated on. I also understand they add the interest before calculating the pmt on the due date. I have set up a schedule to calculate interest every day, and sum it every month.....but am bewildered how to work out the pmt. any help appreciated.
What if you wanted to make bi-weekly payments and track the difference in the amount of interest you pay? Would your payment periods just double and everything else stays the same? Thanks in advance.
Well, naturally somebody like you would want to post something pertaining to Excel :D. And the Fibonacci sequence is easy, add the previous two numbers, but I only know three digits of PI Good thing there's a formula for it, eh? ;-)
Thanks for the video. Really well presented and informative. I was wondering if you have a video that shows how to create a variable rate amortization table, with additional principal payments (that while the additional payments are applied to the loan they don't reduce the monthly payment amount until the variable rate actually changes). I had a shot at doing it myself, but pretty much just made a mess!
I have a question. normally when you take a loan, regardless of interest rate goes up or down. the total payment always stays the same? furthermore if you have more on this topic, I would like to learn more about it, thanks
Thanks for your lesson! What if a deferment period is added in? Which consequences would it have in the figures of this chart? I was said the interests would run though during this period.
Would it be possible to incorporate a grace period where you dont pay both interest and principle for say 3 years but then the new principle balance would be the amount of interest accruing during the grave period?
This is a great video! I'm still not matching the banks amortization schedule and I think it's because they you a basis of 'Actual/365', how would I modify the spreadsheet you helped me create to factor with this basis? MANY many thanks in advance for your input!
Thanks PinaoMan10 I think I know what the problem is but not the solution. You are correct that the payment doesn't change in the standalone spreadsheet provided but when you factor in this scenario below it does change. I modified the spreadsheet comparing fixed to variable (side by side) and factoring the difference in payment (with hypothetical prime rate increases) that you save on variable into overall savings. Did you also factor in the formula you have to use for semi-annual compounded vs the monthly compounding formula used?
wow , amazing how simple you do it , it s just exactly what i needed , one question , you added an extra payment , and the formula calcutes the new balance , what if i added other loan . sorry my english its awfull , i mean what if instead of adding an extra payment , i did a loan refinancing , , how can i calculate the new balance? thank mrexcel......:-P
Hi this is a very nice video. its a very clever trick. could you also take a case where the interest rate and the principal are variables. e.g. my home loan gets disbursed based on the construction stage and also interest rate is variable. I am not able to reproduce the calculations in excel. The bank does it in thier ugly computerized statements that i have no clue about.
Back in the 90's i had to figure how to make my own formula to do this. Bank branch did not know how to do it. When the rate changes you change to rate.
Thanks for sharing your variable rate spreadsheet, it was extremely helpful and an excellent video. In Canada most of our products are calculated semi-annually so I changed the payment to reflect semi-annual payments, ie (F13/2+1)^(1/6)-1. The monthly payment is now correct however the payment does not stay constant if I use the same rate for a period of moths, each payment is slightly different. Do you have any suggestions? Thanks
On the topic of Fibonacci, I guess that must be pretty simple to do the Fibonacci sequence in Excel by adding the previous two cells. Anyway, I still can't quite understand what the purpose of an amortisation table is, probably because - as much as I love your videos - I can't watch a non-comedy video for 14 minutes :P. Interesting though.
So how would I tweak this table if I'm paying a loan off at $200/month at a variable rate but there is no set amount of time it has to be paid off. And what if a payment is skipped (essentially paying zero for one month with NO penalty). How would you account for that? My guess would be putting a -200 in the lump sum column for that month but not sure. Lastly, what if I make a payment early? It looks like daily interest is being calculated for the loan currently. Thanks!!
I beg to differ. Press equals, then up twice, then plus, then up once, then enter. Excluding the one click to select a cell, I make that zero clicks. Anyway, only a bureaucrat who can't see for the red tape would give a thought to such a small discrepancy :P. I know they're all about efficiency, but 4 clicks can be done in just over a second, if you're an efficient typer you can get the formula =A1+A2 done in four/five seconds. Probably less. Meh.
Oh and, not that it matters, and you obviously know this, but it could be done using SUM or just simply =A1+A2, then copy that down, it's a lot simpler. :P
wow , amazing how simple you do it , it s just exactly what i needed , one question , you added an extra payment , and the formula calcutes the new balance , what if i added other loan . sorry my english its awfull , i mean what if instead of adding an extra payment , i did a loan refinancing , , how can i calculate the new balance? thank mrexcel......:-P
OHH!!! If you love comedy, how about Tragic Comedy? If so, you will love the tragic-truth comedy about amortization table:
The Amortization Table shows how much the bank steals (contractually extracts) from you each payment period!!
Thnaks for watching even though it is not solid comedy!
This is an exceptional video. Great job!! You transformed a totally dry presentation into pure entertainment. Thanks a lot man.
Thank you so very much for not only walking through this process but sharing your worksheet and formulas! I was trying to figure out a private loan we have (that I pay overage on) and this is the first resource I found that let me accomplish what I was trying to figure out. which is what is the actual interest and balance of my loan! Again thank you!!!
Your Amortization table with variable rate is an excellent tool planning tool, and your presentation made it very easy to understand the conditional formulas you utilized. Thank you so much.
You are welcome!
Yes, I knew a smart guy like you knew! The Excel thing was what I really wanted to post!
Yes, I am glad that there is Excel for so many things!
Awesome video taking into account that these days everybody is trying to manage a better deal with the bank. Your video gives us the possibility to play with figures and find out what is going to be the monthly rate in case we make an consistent early payment, if we have the money of course
Thanks so much for this. Couldn't work it out myself and couldn't find a solution anywhere until I found your video. Clearly, not many people like bankers! I laughed at your opening facetious remark '...that's how much the banker steals from your payment...' - so even Excel tutors don't like them, eh? Just like the rest of us, then, who don't like them either! Well, I guess those bankers brought all that public 'dislike' on themselves by their own choices to rip-off, rather than serve customers. That's part of the price they now pay for their crimes. Reputational damage, self-inflicted.
Anyway, thanks for helping to solve my initial problem regarding how to create a variable interest calculator. So pleased to have found your video! Can't express my gratitude enough. The calculator my team is attempting to build is intended to be used by financial counsellors (in Australia - although hopefully this comment might inspire counsellors in other countries to build something similar too) who are seeking to assist people in financial distress. The idea is for the resulting data (charts, graphs, etc) to provide a clear visual representation of where things went wrong so that legal arguments can be presented to judges and regulators on behalf of these financial abuse victims and hopefully save a few more homes from being unjustly foreclosed upon. Ultimately, the team would like to build a multiple loan calculator with variable interest capabilities, as many people affected have been encouraged to 'invest' in multiple properties, so multiple loans are involved; and, each loan is typically variable in nature. Wish me luck! And, thanks again. BTW, I liked and have subscribed to ALL your videos now.
Peace.
I have 4 videos for amortization table videos. If you search for this:
'Excel Amortization Table Tricks'
Click on the link for the playlist and you will find all 4. I hope there will be enough tricks in these to get your amort table done!
It depends on exactly what the contact says. All math for loans come from the wording of the contact (credit cards, especially).
Thanks for the video, I was trying to calculate the amortization of a mortgage and this helped tremendously.
OMG you seriously just helped me with my excel project for my intermediate finance class! AMAZING thank you so much =)
Loved this tutorial. I ran across it while looking for a solution for one of my clients. I did make one change so far in my version of the spreadsheet. In the version you have you can not make a change to the Length of the loan in Years. I modified the PMT function by changing the nper to $C$9*$F$9-A12, so cell B13 reads =-PMT(F14/$F$9,COUNT(A14:$A$372),E13). I can't see any problem this modification causes at this point. Looking forward to viewing your other videos
Hey, how about a constant PMT over the year even when changing rate? How do you do that?
@ExcelIsFun
to lock a cell in the mac version you use command + T ( or apple key + T). Thanks for the video! This is great!
I am glad that the video helped!
I have no idea. I love Macs and used them exclusively in the early 1990s, but I had to switch to a PC because I worked exclusively in the business world and working in the business world and using a Mac is too hard to do. Basically it came down to the fact that if I wanted a job, I had to learn the PC, not a Mac. That means that now in 2011 I am Mac illiterate. In addition, Excel for the Mac and Pc are totally different. Sorry about that.
If you have specific Excel questions, THE best Excel question site is:
mrexcel[dot]com/forum
Fibonacci numbers are the following sequence of numbers:
0,1,1,2,3,5,8,13,21,34,55,89,144...
In Excel:
A1 = 0
A2 = 1
A3 = =SUM(A1:A2)
Copy SUM formula down.
You are welcome!
Priceless Thank you very much, I have really learnd alot compared just reading my book on excel
This is still very useful. My amort table was a little different, but i found a way to apply the COUNT function. Big Thanks!
The best video out there!
Glad you like it!!!
Just trudging through the process now. Will check out those amort videos. Thx
I am glad that it helps!
I remembered that when you you go the bank to get a loan you have 2 options (at least in my case):1. equally monthly payments (like in the video) or 2. Descending monthly payments (the first rate is the biggest while the last one is smallest). Moreover the difference between the first and the last rate is quite important (the last rate could be half of the first one). How can we simulate this in Excel? Once again awesome video!
nice explanation Mike. I had tried to calculate variable interest due and ppl amt using excel's ipmt and ppmt, but couldn't get it to work with variable rates.
I am trying to replicate Australian variable interest home loans. They are generally calculated on the daily balance, and some allow additional sums into an 'offset' account, which reduces the daily outstanding balance. I should be sweet now. Thanks.
@olsoncor If you make bi-period payments, the annual periods will be 26 (half of 52 weeks). However, given the APR, this calculation may generate a small problem based on my understanding as 26 bi-weekly periods = 26x14=364 days, which is 1 day less than 365 natural days per year. This could generate an accounting issue and you need to be careful while calculating your period rate.
OK, time the two methods then, Alt + = is much faster than =A1+A2.
Small differences do matter, IF you use Excel 8 hours a day, otherwise, it does not matter much.
Thanks for this hint! such an easy way to avoid recursive reference :) beer for You!
Thanks for this video. Exactly what I need.
Mind blowing video...
this is what i needed the most thank you
Such an awesome video, it really did help me with an issue i had. Thanks for posting it.
Nothing. But the $ in front of the letter is not necessary because the column never changes if you only copy it down.
Try this video:
Excel Finance Trick 9: Daily Interest But Monthly Deposits?!
Otherwise, I would read the contact and figure out the math used for that particular contact.
Hi
I was wondering if there is an option for irregular amounts being paid in amortisation table so if the stranded payment is paid weekly when the loan is monthly?
I am sorry, I do not have a video for that.
Hi, What is the difference of locking pressing f4 twice rather than once when youre copying it down the numbers. When I do it and copy it on either f4 once or twice, its the same outcome.
Thanks a million, needed to refresh my memory!
shout out my guy just saved me big up!! ;)
Thank you so much for the explanation. Do you have a video on accelerated weekly payments using variable interest rate?
Mike, I am stumped on how to apply the following. Australian banks usually lend on a std variable rate. They calculate interest daily, and add it to the loan balance monthly. I presume it is this figure pmt is calculated on. I also understand they add the interest before calculating the pmt on the due date. I have set up a schedule to calculate interest every day, and sum it every month.....but am bewildered how to work out the pmt. any help appreciated.
What if you wanted to make bi-weekly payments and track the difference in the amount of interest you pay? Would your payment periods just double and everything else stays the same? Thanks in advance.
Well, naturally somebody like you would want to post something pertaining to Excel :D. And the Fibonacci sequence is easy, add the previous two numbers, but I only know three digits of PI Good thing there's a formula for it, eh? ;-)
Thanks for the video. Really well presented and informative. I was wondering if you have a video that shows how to create a variable rate amortization table, with additional principal payments (that while the additional payments are applied to the loan they don't reduce the monthly payment amount until the variable rate actually changes). I had a shot at doing it myself, but pretty much just made a mess!
Loan Armotization table with fix Rate You can use IPMT & PPMT function too.
LIFE SAVIOR THANK YOU
I have a question. normally when you take a loan, regardless of interest rate goes up or down. the total payment always stays the same? furthermore if you have more on this topic, I would like to learn more about it, thanks
Thanks for your lesson! What if a deferment period is added in? Which consequences would it have in the figures of this chart? I was said the interests would run though during this period.
How would you do the calculation for the payment to be the same throughout the loan?
Would it be possible to incorporate a grace period where you dont pay both interest and principle for say 3 years but then the new principle balance would be the amount of interest accruing during the grave period?
This is a great video! I'm still not matching the banks amortization schedule and I think it's because they you a basis of 'Actual/365', how would I modify the spreadsheet you helped me create to factor with this basis? MANY many thanks in advance for your input!
Thanks PinaoMan10
I think I know what the problem is but not the solution.
You are correct that the payment doesn't change in the standalone spreadsheet provided but when you factor in this scenario below it does change.
I modified the spreadsheet comparing fixed to variable (side by side) and factoring the difference in payment (with hypothetical prime rate increases) that you save on variable into overall savings. Did you also factor in the formula you have to use for semi-annual compounded vs the monthly compounding formula used?
wow , amazing how simple you do it , it s just exactly what i needed , one question , you added an extra payment , and the formula calcutes the new balance , what if i added other loan . sorry my english its awfull , i mean what if instead of adding an extra payment , i did a loan refinancing , , how can i calculate the new balance? thank mrexcel......:-P
Hi this is a very nice video. its a very clever trick. could you also take a case where the interest rate and the principal are variables. e.g. my home loan gets disbursed based on the construction stage and also interest rate is variable. I am not able to reproduce the calculations in excel. The bank does it in thier ugly computerized statements that i have no clue about.
Back in the 90's i had to figure how to make my own formula to do this. Bank branch did not know how to do it. When the rate changes you change to rate.
The given table is impressive. Can you help me with the same table without change in EMI amount.
Excellent.
Thanks for sharing your variable rate spreadsheet, it was extremely helpful and an excellent video. In Canada most of our products are calculated semi-annually so I changed the payment to reflect semi-annual payments, ie (F13/2+1)^(1/6)-1. The monthly payment is now correct however the payment does not stay constant if I use the same rate for a period of moths, each payment is slightly different. Do you have any suggestions?
Thanks
I don't know what you mean? I got the payment to stay the same.
Hi, do you have an amortization table with variable rate with lump sum payments and early payoff?
I wish I could have your email sir, I have a query that I think you could assist with
On the topic of Fibonacci, I guess that must be pretty simple to do the Fibonacci sequence in Excel by adding the previous two cells. Anyway, I still can't quite understand what the purpose of an amortisation table is, probably because - as much as I love your videos - I can't watch a non-comedy video for 14 minutes :P.
Interesting though.
This is the list of Amort videos that i have:
youtube [dot] com/course?list=EC1C040B9C3D6B319B
I cannot locate the workbook for this Magic Trick.
Thanks for Your Video, Great Job!
So how would I tweak this table if I'm paying a loan off at $200/month at a variable rate but there is no set amount of time it has to be paid off. And what if a payment is skipped (essentially paying zero for one month with NO penalty). How would you account for that? My guess would be putting a -200 in the lump sum column for that month but not sure. Lastly, what if I make a payment early? It looks like daily interest is being calculated for the loan currently. Thanks!!
How do you account for late payments?
can't download the file
thanks, found it very useful!
Thanks! You helped me heaps :)
Thanks for sharing !!
that was amazing! thanks so much!
the download link doesn't work
I just checked the server. It is up and working.
@@excelisfun I tried it multiple times, it keeps loading. I tried from mobile and laptop.
I beg to differ. Press equals, then up twice, then plus, then up once, then enter. Excluding the one click to select a cell, I make that zero clicks.
Anyway, only a bureaucrat who can't see for the red tape would give a thought to such a small discrepancy :P. I know they're all about efficiency, but 4 clicks can be done in just over a second, if you're an efficient typer you can get the formula =A1+A2 done in four/five seconds. Probably less.
Meh.
hmm.. when I used count function I had to put minus 1 as well, else it wont give me the correct numbers..
superb! thank you
Your formula is 4 clicks.
My formula is 2 clicks:
Alt + =
Faster is better!!!
Oh and, not that it matters, and you obviously know this, but it could be done using SUM or just simply =A1+A2, then copy that down, it's a lot simpler. :P
Awesome thank you.
seems very difficult, that is exactly what i need!!
I didn't see this worksheet
Alright, you win :P.
lifesaver
you could just do 360-B
There's a lot of really great countries without banks...look at North Korea, wow what a great country, and they only need 6 banks!
fan - tastic
Hehe, I know what the Fibonacci sequence is, but thanks :P.
You are welcome!
wow , amazing how simple you do it , it s just exactly what i needed , one question , you added an extra payment , and the formula calcutes the new balance , what if i added other loan . sorry my english its awfull , i mean what if instead of adding an extra payment , i did a loan refinancing , , how can i calculate the new balance? thank mrexcel......:-P