, , , , ,

How to Build a Fully Dynamic Mortgage Amortization Table in Excel

We were taught how to build a basic amortization table on day one of my real estate finance graduate studies at Cornell University. Over time, those basic skills expanded to include more complex variations of the same basic idea. I thought I’d pass along a more complex, fully dynamic, amortization table in Excel, and I’ve created a video to show you how you can build one yourself. Feel free to download the completed template (see below), and use the model as you see fit.

The features of this amortization table include:

– Fully dynamic
– Monthly or yearly compounding
– Option to include regular curtailment payments
– Lender yield (APR) calculation
– Period variability up to 360 periods
– Early prepayment (balloon) calculation (e.g. 30 year amortization with 10 year maturity)
– Easy to add prepayment penalty calculation
– Use with existing real estate models to quickly calculate debt service and loan balance schedules

Download other free Excel real estate financial models.

Mortgage Amortization Table Template v1.1
  • File used in video on building an amortization table in Excel
  • Version 1.1 uploaded 8/27/2016
    • Corrects an error where payoff amount wasn’t being correctly calculated when including curtailment payments
    • Removed decimal points