, ,

All-in-One (Ai1) Walkthrough #1 – Permanent Debt Tab (Updated Nov 2019)

In an effort to provide greater instruction on how to use our All-in-One Underwriting Tool for Real Estate Development and Acquisition, we’ve developed a series of walkthrough videos and posts on the methodology behind the various components of the model.

This walkthrough, our first in the series, will detail how the Permanent Debt Module is built (see Perm. Debt Tab). Below we post a video of the walkthrough together with a brief description of the key components of the Perm. Debt tab.

If you haven’t already, you can download the model here.

Video Walkthrough – Permanent Debt Module


Key Components of the Perm. Debt Tab

Quick Note About Format Convention

As always, blue font cells are input cells, black font cells are outputs. Green font cells are values calculated on other tabs and brown/orange font cells are default outputs that may be used as an input when the user deems it necessary.

Activate the Permanent Debt Module

To turn the Permanent Debt Module on and reveal the Perm. Debt Tab, set the Permanent Financing Module drop-down to Yes on line 29 of the Summary tab. Once this is set to yes, the tab becomes available and the cash flows modeled on that tab will flow through to the rest of the model.

Senior (Initial) and Junior Debt

The model allows for the inclusion of permanent, fixed-rate senior debt as well as a second tranche of junior debt. The available inputs are identical for the two layers. The two layers are co-terminous, funding and maturing at the same time. To not use junior debt, simply set the junior debt loan amount to $0.

Senior (Refinance)

The model also includes the option to refinance the Senior (Initial) loan in the middle of the analysis period. Simply enter a ‘Payoff Month’ for the Senior (Initial) loan that is less than the analysis end month, and the Senior (Refinance) section becomes available. Inputs for the Senior (Refinance) section are largely the same as the other tranches of debt.

Funding Date

The funding date for the permanent debt is automatically calculated in cells D6 and E6. The date is calculated as either time zero (analysis start) in the case of acquisition investments, or in the first stabilized month (as calculated on the Property CF tab) in the case of value-add or development investments.

Value and Cost

The stabilized value and acquisition/development cost are brought in from the Property CF and Summary tabs to use in calculating loan-to-value (LTV) and loan-to-cost (LTC). LTV and LTC on the senior debt alone as well as on the combined debt stack are calculated in cells C11-D11 and C22-D22 respectively.

Loan Assumptions

Available loan term assumptions include loan amount, loan fees, interest rate, amortization, and interest-only period. The loan term to maturity is automatically calculated to line up with the exit date (i.e. end of analysis or sale date). Assumptions are identical for senior and junior debt.

Amortization Tables

Based on the loan assumptions, four amortization tables are built and displayed on the Perm. Debt tab. The first two are for the senior debt (initial) and senior debt (refinance), and include aspects typical of amortization tables (balance, payment, interest, principal) but also include lines to show loan funding, payoff, and monthly interest rate. A third table for junior debt has identical outputs. The fourth table, ‘Total Permanent Financing’, likewise shows the same outputs and represents the sum of the senior and junior debt outputs.


Frequently Asked Questions about the Permanent Debt Tab in the Ai1 Underwriting Model

 

Set the “Permanent Financing Module” drop-down to “Yes” on line 29 of the Summary tab. This activates the Perm. Debt tab and connects its cash flows to the rest of the model.

The model supports:

Senior (Initial) fixed-rate debt

Junior debt (second tranche, co-terminous with senior)

Senior (Refinance) debt triggered by entering a payoff month for the initial senior loan

The funding date is automatically calculated:

For acquisitions, it defaults to time zero.

For development/value-add, it aligns with the first stabilized month from the Property CF tab.

Each tranche requires inputs for:

Loan amount

Loan fees

Interest rate

Amortization period

Interest-only period

Maturity is auto-calculated to match the model’s exit month.

Yes. Enter a Payoff Month for the Senior (Initial) loan that is earlier than the model’s end month. This triggers the Senior (Refinance) section with a new set of inputs.

LTV and LTC metrics are calculated using:

Stabilized value from the Property CF tab

Acquisition or development cost from the Summary tab
They are displayed both for individual tranches and for the combined debt stack.

Each table shows:

Monthly loan balance, payment, interest, principal

Loan funding, payoff, and monthly interest rate
There are separate tables for:

Senior (Initial)

Senior (Refinance)

Junior Debt

Combined “Total Permanent Financing”

Blue = user inputs

Black = outputs

Green = values pulled from other tabs

Brown/Orange = default outputs that may be used as manual inputs


About the Author: Spencer Burton is Co-Founder and CEO of CRE Agents, an AI-powered platform training digital coworkers for commercial real estate. He has 20+ years of CRE experience and has underwritten over $30 billion in real estate across top institutional firms.

Spencer also co-founded Adventures in CRE, served as President at Stablewood, and holds a BS in International Affairs from Florida State University and a Masters in Real Estate Finance from Cornell University.