Excel Interactive Loan Amortisation Table - Change repayment frequency, include balloon payment etc
HTML-код
- Опубликовано: 11 сен 2024
- This video shows you how to create an interactive amortisation schedule from scratch in Excel, which you can interact with by changing the repayment frequency (from annually, to semi-annually to monthly and quarterly), by changing the repayment timings (end of or beginning of the period ), by changing balloon payment amounts and by changing interest rates, term length and principal amounts.
Thanku so much sir learn the perfectly how we can make quarter type loan thank once again
If someone like you were to develop a tool box with all the different scenarios that are just mentioned in these commentary’s and more and charge a small fee to use the template you could make a lot of money.
How about cases when Principal and Interests have different payment periods? So for instance, Interests are to be paid quarterly and the principal is to be repaid semi annually.
How to auto update loan amotozation with extra payment aditional payment also client wise if interst rate change ? If only interst receive how to calculate in excel
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
Hi
Well done - really helpful video. Great explanation of the concepts!
I notice however when you have a Balloon Payment with an In Advance loan, the end result is not quite correct. For example with a $100,000 facility over 60 months @ 5% and a Balloon of $20,000 - it shows the amount owing at the end of the term as $19,917.01 and not $20,000?
Hi, Thank you for you comment. Much appreciated.
Am I correct in saying that this issue only arises when the payment type is 1 (i.e. each loan repayment is made at the beginning of each period)?
@@excel4accounting780 Hi - Yes that is correct - only occurs when using Type 1 - Loan Repayment at the beginning of the period
@@brucejhall52 Thanks for the confirmation. Yes I was previously aware of this issue and this is caused by an error within the Excel functions themselves as they use the incorrect discount factor in this scenario only. The only way to fix this issue is to build the loan amortisation table without using Excel's PMT, PPMT and IPMT function. I have uploaded another video to this channel which shows this. This is the link ==> ruclips.net/video/Xq2Yh2oD12k/видео.html
Very helpful, thank you! Can you do something similar but with a grace period? (interest-only period).
Hi, Thanks for your feedback much appreciated. A loan schedule with a grace period is a good idea but depends on how we can manipulate the excel functions. If possible I will let you know.
@@excel4accounting780 a lot of count...... If you build it horizontally and then and index to transpose???
Hi, this is very insightful and helpful. Thanks! But I want to know, how do I create an amortization schedule that shows interest repayments monthly and equal annual repayments. E.g I take a loan for for say 2m for 4 years and I want to make 4 equal annual repayments (500k every year), however, I want to make interest repayments monthly (48 interest repayments for the 4 years). How do I create an amortization schedule to show this?
Hi, Thanks for your feedback much appreciated. Unless the loan is an interest only loan whereby you would pay monthly interest and only repay the principal at the end of the loan term, usually most loan repayments will include paying principal and interest at the same time hence the excel functions are catered to only these specific scenarios.
How to download sample file?
How to download file
Hi,
Thank you very much for making this video. It is really helping me to build a loan amortization scheduler.
However I am struggling at a point when I apply IPMT function for beginning type of payment then it shows error.
It would be great if you could provide me your email id so that I can email you that model which I am building.
Thank you again for your support in advance.
Hi, Thanks for your feedback much appreciated. When you apply the IPMT function make sure you select all the necessary parameters i.e. the adjusted interest rate, the period number, the total number of periods, the principal value of the loan, the balloon payment and the payment type. This should avoid any error message.