Dynamic Amortization Schedule (Updated 2.06.2020)
This is a dynamic amortization schedule for debt that gives the user the capability to model a loan with an interest only period up front and an amortizing floating rate debt repayment period once the interest only period is over. This module also allows the user to model other more straightforward scenarios such as an interest only loan and a loan with no interest only period and a fixed interest rate.
Along with the download below, there are now two videos. The first explains how to use this module and also how the formulas work for those who are interested in learning more about the mechanics. The second video is a Watch Me Build that shows you how to roll up the monthly data into annual periods.
Dynamic Amortization Schedule Features
This module is built to account for a 360-month (30-year) period with an interest only period of the users choosing, a floating rate or fixed rate amortization period of the user’s choosing, and the ability to select a balloon payment date. The module has a place to insert the floating rate debt forecast (row 19) and if you would like to use this module for a fixed interest rate loan, you just need to insert the same interest rate across the entirety row 19.
Additional Learning Component
I also added a second sheet to this model, as mentioned in the video, for those who are less experienced with building amortization tables and want an easy-to-understand model to see how a basic, fixed rate amortization table works. The payment function in this module is a bit more complex due to having to constantly recalculate the payment for floating rate debt, so the second sheet is provided to give a more basic understanding of straight forward amortization schedules.
This module can be inserted into your own model by simply linking the payment line form this module into the debt payment section of your model being careful to properly sync up the dates.
Hope you find the module and video useful.
Video Walkthrough – Dynamic Amortization Schedule
Convert The Data to an Annual Schedule
Download the Dynamic Amortization Schedule
To make these files accessible to everyone, they are offered on a “Pay What You’re Able” basis with no minimum (enter $0 if you’d like) or maximum (your support helps keep the content coming – similar real estate Excel modules sell for $100 – $300+). Just enter a price together with an email address to send the download link to, and then click ‘Continue’. If you have any questions about our “Pay What You’re Able” program or why we offer our models on this basis, please reach out to either Mike or Spencer.
Frequently Asked Questions about the Dynamic Amortization Schedule in Excel
