# Excel Magic Trick 407: Amortization Table W Variable Charge

See how to make a Amortization Timetable / Table with a variable fascination fee. See the PMT perform, finance methods and a mobile range in a perform that will shrink as we duplicate it down a column.
See the Shrinking range trick for functions.

1. 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?

2. Hi, do you have an amortization table with variable rate with lump sum payments and early payoff?

3. fan – tastic

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

5. sir can you give me your number or other personal contact where i can talk to you.

6. Thanks! You helped me heaps đź™‚

7. Thanks a million, needed to refresh my memory!

8. Hey, how about a constant PMT over the year even when changing rate? How do you do that?

9. Awesome thank you.

10. Thank you so much! This helped me get back to my banker on an error at their end đź™‚

11. Thanks for sharing !!

12. I didn't see this worksheet

13. I cannot locate the workbook for this Magic Trick.

14. Such an awesome video, it really did help me with an issue i had. Thanks for posting it.

15. 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!!

16. 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?

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

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

19. This is the list of Amort videos that i have:

20. 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!

21. You are welcome!

22. that was amazing! thanks so much!

23. thanks, found it very useful!

24. I am glad that it helps!

25. Priceless Thank you very much, I have really learnd alot compared just reading my book on excel

26. @ExcelIsFun

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

27. You are welcome!

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

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

mrexcel[dot]com/forum

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

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

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

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

34. @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 = 26×14=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.

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

36. Nothing. But the \$ in front of the letter is not necessary because the column never changes if you only copy it down.

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

38. I am glad that the video helped!

39. OMG you seriously just helped me with my excel project for my intermediate finance class! AMAZING thank you so much =)

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

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

42. Just trudging through the process now. Will check out those amort videos. Thx

43. 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!