Excel Magic Trick 407: Amortization Table W Variable Rate
Вставка
- Опубліковано 12 жов 2009
- Download Files:
people.highline.edu/mgirvin/Y...
See how to create a Amortization Schedule / Table with a variable interest rate. See the PMT function, finance tricks and a cell range in a function that will shrink as we copy it down a column.
See the Shrinking range trick for functions. - Наука та технологія
Yes, I knew a smart guy like you knew! The Excel thing was what I really wanted to post!
This is an exceptional video. Great job!! You transformed a totally dry presentation into pure entertainment. Thanks a lot man.
Yes, I am glad that there is Excel for so many things!
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!!!
I am glad that the video helped!
I am glad that it helps!
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!
OMG you seriously just helped me with my excel project for my intermediate finance class! AMAZING 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
This is still very useful. My amort table was a little different, but i found a way to apply the COUNT function. Big Thanks!
Thanks for the video, I was trying to calculate the amortization of a mortgage and this helped tremendously.
Thanks a million, needed to refresh my memory!
Such an awesome video, it really did help me with an issue i had. Thanks for posting it.
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!
@ExcelIsFun
to lock a cell in the mac version you use command + T ( or apple key + T). Thanks for the video! This is great!
Priceless Thank you very much, I have really learnd alot compared just reading my book on excel
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.
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
It depends on exactly what the contact says. All math for loans come from the wording of the contact (credit cards, especially).
You are welcome!
Hey, how about a constant PMT over the year even when changing rate? How do you do that?
Thanks for this video. Exactly what I need.
This made my day. Thank you!
this is what i needed the most thank you
Just trudging through the process now. Will check out those amort videos. Thx
Thanks! You helped me heaps :)
that was amazing! thanks so much!
Thanks for this hint! such an easy way to avoid recursive reference :) beer for You!
thanks, found it very useful!
Thank you so much for the explanation. Do you have a video on accelerated weekly payments using variable interest rate?
Thanks for sharing !!
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!
shout out my guy just saved me big up!! ;)
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.
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!
superb! thank you
Awesome thank you.
The best video out there!
Glad you like it!!!
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.
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!
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!
Mind blowing video...
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.
LIFE SAVIOR THANK YOU
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?
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.
If you have specific Excel questions, THE best Excel question site is:
mrexcel[dot]com/forum
Excellent.
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?
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.
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?
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.
seems very difficult, that is exactly what i need!!
lifesaver
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
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.
Nothing. But the $ in front of the letter is not necessary because the column never changes if you only copy it down.
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.
The given table is impressive. Can you help me with the same table without change in EMI amount.
@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.
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.
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!!
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.
I cannot locate the workbook for this Magic Trick.
How would you do the calculation for the payment to be the same throughout the loan?
Loan Armotization table with fix Rate You can use IPMT & PPMT function too.
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.
Alright, you win :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
I am sorry, I do not have a video for that.
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.
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? ;-)
What if my amortization schedule changes? If the first 5 years amortized over 40 years, then next 5 years amortized over 25 years?
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
fan - tastic
hmm.. when I used count function I had to put minus 1 as well, else it wont give me the correct numbers..
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.
How do you account for late payments?
This is the list of Amort videos that i have:
youtube [dot] com/course?list=EC1C040B9C3D6B319B
I didn't see this worksheet
can't download the file
you could just do 360-B
Thanks for Your Video, Great Job!
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
Hehe, I know what the Fibonacci sequence is, but thanks :P.
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.
Your formula is 4 clicks.
My formula is 2 clicks:
Alt + =
Faster is better!!!
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.
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!
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