Watch Me Build a Dynamic Mortgage Amortization Table in Excel (Updated September 2024)
There are a few basic, yet fundamental, real estate modeling skills you must master before anyone will take you seriously as a real estate professional. Building a mortgage amortization schedule is one of them.
Virtually every model you create will require this skill – or some variation of what you do when you build an amortization table. And the more proficient you are at calculating debt service, bifurcating out principal and interest, and knowing the outstanding loan balance at any given period, the more employable you are!
Are you an Accelerator member? See course 13: ‘Modeling Real Estate Debt’ in the core curriculum for more details about amortization. If you’re Accelerator Advanced member, check out the ‘Advanced Modeling – Real Estate Debt’ endorsement for advanced techniques in modeling debt. Not yet an Accelerator member? Consider joining the real estate financial modeling training program used by top real estate companies and elite universities to train the next generation of CRE professionals.
Two Amortization Table Tutorials in One
In this post I provide two video tutorials, with the Excel worksheets combined into one Excel workbook. The first tutorial is an in-depth ‘Watch Me Build’ video on how to build a fully dynamic amortization table. The second video tutorial is short and sweet – call it a bonus video, where I build a simple mortgage amortization schedule in Excel in under 90 seconds.
The in-depth Watch Me Build is a 25 minute behemoth, showing you how to build a more complex, dynamic amortization table in Excel. It includes many of the features you might find in an amortization table in an institutional model. I also share various modeling techniques that are applicable across modeling tasks.
I should note that in the video I refer to a few techniques addressed in-depth in our real estate financial modeling Accelerator program. So bear with me if a concept or two isn’t fully explained. Of course, if you’re watching these tutorials you’ll most definitely find great value in becoming an Accelerator member. So consider checking that out when you have a moment.
And with that, go ahead and download the corresponding Excel file (tutorial and completed files) below and do your best to follow along. Then try to re-create the models on your own.
Follow Along Using the Excel Files from the Videos
To get the most out of these two tutorials, I highly recommend you download the Excel worksheets I use in the videos.
To make this ‘Watch Me Build’ tutorial accessible to everyone, it is 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 course 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.
Watch Me Build a Fully Dynamic Mortgage Amortization Schedule in Excel
In this 25-minute Watch Me Build style video tutorial, I show you how to:
- Make periods dynamic to loan maturity and loan payoff
- Make the model dynamic to the number of periods in a year
- Layer in curtailment payments (i.e. additional principal payments)
- Model interest-only and amortizing payments
- Calculate the lender yield, inclusive of origination fees and points
- Calculate the loan payoff (i.e. balloon balance) at loan maturity
- Write fully dynamic formulas for nine columns within the Amortization Table section
Inputs: Original Loan Balance, Interest Rate, Amortization, Periods, Term, Interest-Only Period, Curtailment, Origination Fees, and Points.
Formulas: Interest-Only Payment, Amortizing Payment, Payoff @ Loan Maturity, Lender Yield (APR), Period, Beginning Balance, Payment, Interest, Principal, Payoff, Curtailment, Lender Cash Flow, and Ending Balance.
Bonus Tutorial: Building a Simple Amortization Table in Under 90 Seconds
In this tutorial you’ll get down to the basics. You’ll be able to calculate loan payment, starting and ending balance, and learn to bifurcate the debt service payment to determine what proportion goes to interest versus principal. The model is not dynamic, and many wrinkles you see in modeling debt are not covered here, like they are in the more comprehensive video, but if you’re in a hurry this is a great start.
Inputs: Loan Amount, Interest Rate, Term.
Formulas: Payment, Period, Starting Balance, Principal, Interest, and Ending Balance.
ADDITIONAL RESOURCES
As an additional resource to modeling debt, we recommend using our Advanced Amortization Table Creator GPT. This tool allows for more sophisticated tracking and analysis of debt payments and amortization schedules. This tool offers customized schedules for various loan structures, flexible inputs of different loan terms, interest rates, and payment frequencies, and a schedule you can automatically updated based on changes in loan assumptions or refinancing terms. By leveraging the advanced features of this tool, you can create more robust and accurate financial models, streamlining the debt modeling process and gaining deeper insights into your real estate financing strategies.
In addition, you can find our Excel tools for modeling debt here.
Quick Note for Accelerator Members
Are you an Accelerator member? Read further. If not, consider joining the Accelerator.
To get the most out of this tutorial, you might review the following courses/lectures together with this Watch Me Build video:
- Glossary Term: Loan Amortization
- Lecture 2.2 of Introduction to Real Estate Debt: Building an Amortization Schedule
- Lecture 2.3 of Introduction to Real Estate Debt: Modeling Alternative Interest Calculation Methods
- Lecture 2.4 of Introduction to Real Estate Debt: Calculating Payment and Loan Payoff
- Lecture 1.2 of Advanced Concepts in Real Estate Financial Modeling: Modeling Floating (Variable) Rate Debt
- Lecture 1.7 of Advanced Concepts in Real Estate Financial Modeling: Modeling a Construction Loan Takeout
Frequently Asked Questions about Building a Dynamic Mortgage Amortization Table in Excel
Version Notes
v2.01
- Updated custom cell formatting labels to account for single and plural labels
- Combined completed, template, and basic amortization tables files into one
v2.0
- Removed $ to allow use across currencies
- Added option to include interest-only period
- Added Payment column to track amortizing vs IO payments
- Added Payoff column to track actual loan payoff amount at loan maturity
- Changed named cell ‘Payoff’ to ‘Term’
- Made payments and ending balance dynamic to monthly curtailment payments, such that once balance hits zero payments stop
- Added data validation to all input cells
- Released new ‘Watch Me Build’ video for this model
- Misc. formatting improvements/fixes
v1.11
- Misc. formatting enhancements
v1.1
- Corrects error where payoff amount wasn’t being correctly calculated when including curtailment payments
- Removed decimal points
v1.0
- Initial release
