Loan amortization schedules that are adaptable and can be integrated into various financial models are highly valuable because they offer versatility, accuracy, and efficiency. These schedules allow for easy adjustments and seamless integration with different scenarios and financial tools, enhancing financial planning and analysis. This template was built to easily plug into any of the models you see here on the site or whatever models you are working with.
I built this template just as much for me as for you. It is something I'll probably pull from any time I need to integrate a loan schedule into a model. I think a lot of clients and customers of mine want to add an array of more complex debt financing scenarios. For this reason, I built a template that I think is the easiest plug and play amortization schedule that covers as many cases as possible.
The spreadsheet download comes with two tabs, each completely self-contained. Self-contained just means you can paste the entire tab into a new workbook without any reference errors. You then simply map the top input to relevant areas of your model.
Template Features (Loan Tab)
- Optional interest-only (i/o) period (define the number of months this lasts for). This can be 0 if there is no interest-only period.
- Option to end the loan after the interest-only period or switch to a regular amortization repayment.
- Option for extra repayments of principal.
- Dynamic start month of repayments (adjust the month # that repayments begin).
- Option for a 'term' loan if different than the amortization period.
- Option for early repayment of loan (if 'yes' is selected the model will repay on the manually defined 'end date' any remaining balance).
- Option to dynamically stop payments (this fits your 'exit month' of whatever model you are working in).
- Option to let extra principal repayments reduce future payments or keep future payments the same no matter how much extra is paid. Paying extra principal manually will make the loan repayment happen quick, and principal will be reduced faster.
- The i/o rate is defined separately from the amortization interest rate. These can be adjusted by month as well if desired.
- The general flow for this model is a single loan that is taken out, optional i/o period, and then an amortized repayment schedule that lasts for some amount of time.
- Has all the same features as the 'loan' tab except the funding proceeds are manually defined in column C for how much is drawn each month.
- This works pretty simply; the interest is based on how much is borrowed over time less what has been repaid.
- There is an option to roll this into a regular term loan at a defined date in the future. Until then, it is i/o payments.
- The p/i repayment is based on the principal balance as of the 'month of switch' month number. At that time, if you select 'Yes' in the 'Switch to Amortization' box, then the principal and interest will begin to be repaid.
- All the same features in the other model also exist here in terms of when things are repaid.
- If you select to now switch to amortization, then at the end of the i/o period, the balance of the loan will be repaid.
