Excel Magic Trick 407: Amortization Table W Variable Rate

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.

Category:

Science & Technology

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

[slickbt25]

This is an exceptional video. Great job!! You transformed a totally dry presentation into pure entertainment. Thanks a lot man.

[ExcelIsFun]

You are welcome!

[carlosh1403]

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

[ExcelIsFun]

I am sorry, I do not have a video for that.

[ExcelIsFun]

If you have specific Excel questions, THE best Excel question site is:

mrexcel[dot]com/forum

[jjbb81]

How do you do this on a mac?

[ExcelIsFun]

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.

[cinnemonn]

@ExcelIsFun

to lock a cell in the mac version you use command + T ( or apple key + T). Thanks for the video! This is great!

[carlosh1403]

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



[Ifrite]

@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.