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!

TWO AMORTIZATION TABLE TUTORIALS IN ONE

In this post I provide two video tutorials together with two Excel workbooks. The first tutorial is short and sweet. In fact, I titled it “Build a Mortgage Amortization Table in Excel in Under 90 Seconds.” For those with less patience for the details and/or those who want a quick and dirty tutorial for building a basic amortization schedule, this video is for you. I recommend you download the corresponding model, and follow along. After watching the video a time or two, I suggest you delete the formulas and attempt to create the table on your own.

The second tutorial video is a 28 minute behemoth I recorded several years ago showing you how to build a more complex, dynamic amortization table in Excel. I get long-winded – I know! – which for those who know me personally know is far too common (and annoying) so bear with me as you slog through the video. However, the detail is good and if you can master what I teach in this video you’ll be in good shape. Again, download the corresponding model and do your best to follow along. Then try to re-create the model on your own.

CHECK OUT THE PERM. DEBT TAB IN MY ALL-IN-ONE (AI1) MODEL TO SEE HOW I USE THESE CONCEPTS TO MODEL BOTH PRIMARY AND SECONDARY DEBT.

TUTORIAL #1 – BUILDING A SIMPLE MORTGAGE AMORTIZATION SCHEDULE

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, but it’s a great start.

Inputs: Loan Amount, Interest Rate, Term.

Formulas: Payment, Period, Starting Balance, Principal, Interest, and Ending Balance.

Basic Mortgage Amortization Table v1.0
  • Covers the basics of building a mortgage amortization table
  • Fully amortizing, 360 period mortgage loan

TUTORIAL #2 – BUILDING A COMPLEX, FULLY DYNAMIC AMORTIZATION TABLE IN EXCEL

There are a few concepts you’ll see in this tutorial that don’t appear in the quick video. For instance, the model is fully dynamic to changes in term and payoff date. Thus, the payoff month can change depending on the length of the term or be influenced by any curtailment payments made by the borrower. The formulas I use must account for those scenarios.

I also model lender yield (or APR) which is essentially the lender’s IRR including any fees/points earned by the lender. This necessitates a column entitled ‘Lender CF’ where I run an IRR calculation on the lender’s net cash flow (outflows and inflows).

Inputs: Original Loan Balance, Contract Rate, Amortization, Periods, Term, Curtailment, Origination Fees, and Points.

Formulas: Payment, Payoff @ End of Term, Lender Yield (APR), Period, Beginning Balance, Interest, Principal, Curtailment, Lender Cash Flow, and Ending Balance.

Dynamic Mortgage Amortization Table v1.11
  • File used in video tutorial 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

As always, if you have questions, comments, or would like to just say hello, don’t hesitate to reach out.

About the Author: Born and raised in the Northwest United States, Spencer Burton has over 15 years of real estate investment and development experience. In his current position, Spencer assesses new acquisition, development, and debt opportunities for a $45bn real estate fund. He resides in Dallas, TX.