Advanced Mortgage Amortization Module (Updated May 2024)

In recent months, I’ve been working on an advanced real estate investment amortization table with interest-only capability, fixed and variable interest rates, multiple interest calculation methods (i.e. 30/360, Actual/Actual, Actual/365, Actual/360), monthly and annual periods, duration calculation, lender yield calculation, average life calculation, static and dynamic debt service payments, loan assumptions, and more.

This module is meant to be added to an existing real estate financial model to provide more robust debt analysis in those cases when its needed. As a result, the entire module is found on one worksheet. Simply drag-and-drop the worksheet into your existing module and link the cash flows from this module to your own discounted cash flow model.

Are you an existing Accelerator member? Learn more about the concepts and metrics in this module in lesson 5 of the Modeling Real Estate Debt course. 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.

What’s Included in the Advanced Mortgage Amortization Module

This mortgage amortization module is on the one hand simple (i.e. only a handful of inputs, all included on one worksheet, etc), and on the other hand comprehensive in scope and capability. I’ve included everything I can think would be needed to model the cash flows of a senior or junior mortgage, assuming all loan proceeds fund in time zero.

Before showing you how to use the module, allow me to share a few of the features included in the module.

Four Interest Calculation Methods

Michael has blogged on this topic several times (e.g. here and here), and so I’ll leave the teaching to him on this. But the model includes the ability to calculate interest assuming 30/360, Actual/Actual, Actual/360, and Actual/365 periods. Or in other words, the interest is calculated assuming either a 30-day month and 360-day year, or some variation of that.

Fixed vs. Variable Interest Rate

The module can accommodate both fixed rate and variable rate loans via a simple drop-down. If you toggle the ‘Rate Type’ to ‘Variable’, the ‘Annual Rate’ column turns blue allowing you to change the periodic rate for each month.

Interest-Only Period

The module allows you to model an interest-only period. This interest-only period can be equal to the entire loan term (i.e. full IO), some portion of the loan term (i.e. partial IO), or none (i.e. no IO). You can likewise adjust the amortization period and loan term, in tandem with the Interest-Only Period, to analyze any number of loan structures.

Static vs. Dynamic Debt Service Payments

The module has the built-in capability to either model debt service payments that are static (i.e. are the same each month) or dynamic (i.e. adjust monthly depending on the interest due and amortization schedule). What this feature does is recalculate the amortization schedule at the end of each period to ensure the loan exactly amortizes based on the amortization period input.

Lender Yield (i.e. APR) Calculation

The module automatically calculates lender yield, based on the loan assumptions entered. This feature helps the borrower understand the true cost of the mortgage, while also helping the lender understand the yield of the loan given the fees and structure.

Note that in the United States, Lender Yield (APR) is most commonly calculated as a nominal rate (i.e. use IRR()*12 logic in Excel). This differs from Europe where lender yield is more commonly calculated as an effective (or compounded) rate (i.e. use XIRR() logic in Excel).

Loan Duration Calculation

The second lender metric that the module calculates is the loan duration. This calculation is made possible due to hidden logic in columns W and X. To unhide those columns click the ‘+’ sign immediately above column Y.

The duration calculation returns the number of periods (denoted in years here) that must pass for half of the time-weighted present value of the debt service payments to be paid. The metric is an important measure of interest rate risk. The longer the duration, the greater exposure the loan has to interest rate risk. To hedge against that risk, a savvy lender might pair the mortgage loan asset with a liability of similar duration and size.

Average Life Calculation

The third advanced metric calculated in this module is the loan’s Average Life (also referred to as Weighted Average Life). This non-time value of money metric measures the period (denoted in years here) in which half the time-weighed principal has been paid.

Lenders use this metric in a variety of ways, including to price the loan (i.e. determining appropriate interest rate) and to compare the risk of two loans of similar loan maturity.

Video Tutorial – Using the Advanced Mortgage Amortization Module

With an overview of the features of the module out of the way, allow me to show you how to use the module. Below you’ll find a video tutorial I recorded on how to use the Advanced Mortgage Amortization Module. There are undoubtedly ways to improve this tool, so please don’t hesitate to reach out with questions or comments you may have.

 

Download the Advanced Mortgage Amortization Module

To make this model 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 – typical real estate Excel modules sell for $100 – $300+ per license). 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.

We regularly update the model (see version notes). Paid contributors to the model receive a new download link via email each time the model is updated.

---

Bonus: Automate Your Amortization Schedule with Our Custom GPT

Manually filling out the advanced mortgage amortization model can be time-consuming and complex. To streamline this process, we have also built a custom GPT that can automate the creation of your amortization schedule. If you are a paid ChatGPT user, you can use our Advanced Mortgage Amortization Schedule GPT to:

• Get Started Quickly: Simply provide the GPT with your loan details, and it will guide you through the necessary inputs.
• Accurate and Efficient: The GPT updates the Excel model for you, ensuring accuracy and saving you valuable time.
• User-Friendly: Ideal for users who may be less familiar with complex financial models, the GPT simplifies the entire process.

Click here to find instructions for accessing and using this GPT by A.CRE (Note that as of May 2024, this tool is only available to paid ChatGPT users).

Version Notes

v3.1

• Updates to Version tab
• Updated placeholder values
• Misc. formatting enhancements

v3.0

• Added ‘Loan Assumption’ feature
  • New Loan vs. Loan Assumption drop-down added to cell F4
  • Logic created to modify labels depending on whether New Loan or Loan Assumption
  • Added ‘Monthly Loan Payment’ input for Loan Assumption
  • Created Conditional Formatting to hide certain inputs specific to New Loan or Loan Assumption scenarios
• Updates to Variable Rate Module
  • Added ‘Benchmark’ column
  • Added link to Chatham rate curve to help complete the ‘Benchmark’ column
  • Added ‘Lender Spread’ input
  • For clarification, changed note in cell F13 when Variable Rate is selected
• Updated placeholder values
• Misc. formatting enhancements

v2.0

• Moved ‘Annual Payments’ rollup to ‘Amortization’ tab to allow for module to be more easily inserted into existing model
• Removed $USD symbol to accommodate non-USD demoninated loans
• Set ‘Duration’ and Alternative ‘Interest Calc Methodology’ sections to gray
• Updated number formatting to ‘Accounting’
• Added ‘Duration’ and ‘Average Life’ calculation
• Added functionality to amortize loan with variable interest rate
• Added ‘Actual/365’ interest calc method
• Added option to model dynamic vs. static monthly payments

v1.1

• Fixed issue related to interest calc

v1.0

• Initial release