I've been searching for a tutorial like this forever. This was extremely clear and now, I'm able to build on the template to account for multiple variable rate changes. Thanks!
Love this calculator. I like to make one of payments and monthly overpayments. It would be great to see how I can add an overpayment column in and what formulas will make this work.
Excellent Video!! Clear and concise. Here in Ireland our variable rates fluctuate with the ECB so the VLOOKUP wont do, I'd have to add them manually on the month the change happened. My Lender point blankly refuses to give me any kind of insight such as you have provided so I can scrutinise their calculations, for that I'm grateful. Most of these videos assume no missed payments,..... The big thing for me is what exactly happens when a payments is missed. I cannot for the life of me get an answer to this. Dont suppose you have a tutorial on that?
Thanks for your comment and watching the video Orion! I will look into making a tutorial which accounts for missed payments soon. In the meantime, let me know if you have any other feedback or video suggestions. Take care!
Great table, thanks. Would the interest payment not vary if payment is due at the beginning of the month or end of the month? How would you calculate for both payment options?
This is super helpful, thank you so much! I have a question: How do I include extra payments for this exercise? When I try to include them, the scheduled payment is being reduced, but my loan balance term/months are not decreasing. I'm aware in some countries you can decide either to decrease the scheduled payment or reduce your loan time. Could you please help me?
Hi Luis, you can try to adjust the term manually but I think it may require some VBA coding to make this automatic - I’ll look into incorporating this in a future video. Thanks for watching!
Agreed, this would be fantastic to have. I make regular extra payments each month to my loan in order to pay it off sooner. We also have an offset account whereby the money that is in that account is calculated with the current interest rate at the end of each month. That calculated amount is used to increase the monthly principal and reduce the monthly interest. Being able to have a couple of columns where I can add both the offset saving and the extra payments would be awesome!
Can be possible client wise auto update loan amotozation table? Also if possible interest rate change so auto update automatic in excel Extra Payments means (Start at Payment No,Extra Payment,Payment Interval,Extra Annual Payment,Payment,Total Extra Payments) Additional Payment already showing in your video ,Variable or Fixed Rate ,Impact of interest rate HIKE on your loan EMI & repayment schedule & Impact of interest rate CUT on your loan EMI & repayment schedule ? how to create in excel & Suppose provide only interest
What I don't understand after using this to create a table. Is that the payment is slightly inaccurate to my actual payment. This is by about 30 dollars each interest rate hike. I noticed on the mortgage disclosure that it mentions that the compounding period is semi-annually. Is this what is causing the small difference? Does this table take into consideration the type of compounding?
If i want to change from weekly to bi-weekly payments would i just change the function so interest rate divided by 26 and the increase the number of periods accodingly.