Mortgage Calculator

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.

45 comments

  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?

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

    Reply
  3. fan – tastic

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

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

    Reply
  6. Thanks! You helped me heaps 🙂

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

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

    Reply
  9. Awesome thank you.

    Reply
  10. Thank you so much! This helped me get back to my banker on an error at their end 🙂

    Reply
  11. Thanks for sharing !!

    Reply
  12. I didn't see this worksheet

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

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

    Reply
  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!!

    Reply
  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?

    Reply
  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

    Reply
  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

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

    youtube [dot] com/course?list=EC1C040B9C3D6B319B

    Reply
  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!

    Reply
  21. You are welcome!

    Reply
  22. that was amazing! thanks so much!

    Reply
  23. thanks, found it very useful!

    Reply
  24. I am glad that it helps!

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

    Reply
  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!

    Reply
  27. You are welcome!

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

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

    mrexcel[dot]com/forum

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

    Reply
  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

    Reply
  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

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

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

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

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

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

    Reply
  38. I am glad that the video helped!

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

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

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

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

    Reply
  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!

    Reply
  44. 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.

    Reply
  45. Alright, you win :P.

    Reply

Leave a Reply