Excel Magic Trick 407: Amortization Table W Variable Rate Video

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.

Save & Share:

Mynotoar: 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.

planiolro: 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

ExcelIsFun: 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: 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.

ExcelIsFun: I am glad that you like it. I just added a Playlist on Amortization Tables (3 videos).

Mynotoar: Hehe, I know what the Fibonacci sequence is, but thanks :P.

ExcelIsFun: Yes, I knew a smart guy like you knew! The Excel thing was what I really wanted to post!

Mynotoar: 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? ;-)

Mynotoar: 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

planiolro: 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!

ExcelIsFun: Yes, I am glad that there is Excel for so many things!

ExcelIsFun: Your formula is 4 clicks. My formula is 2 clicks: Alt + = Faster is better!!!

ExcelIsFun: It depends on exactly what the contact says. All math for loans come from the wording of the contact (credit cards, especially).

Mynotoar: 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.

ExcelIsFun: 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.

Mynotoar: Alright, you win :P.

Author: ExcelIsFun; Uploaded: Oct 14, 2009; Duration: 14:6; Views: 673

Tags: excel  amortization  schedule  table  variable  interest  rate  pmt  function  excelisfun  mike  gel  girvin  highline  community  college  cell  range  in  that  will  shrink  as  we  copy  it  down  column  wiki wickes www.facebook.com wikipedia whsmith wilkinsons weather waterstones warehouse weather forecast