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
How do I activate the Permanent Debt Module in the Ai1 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.
What types of debt can I model in the Perm. Debt tab?
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
How is the funding date for permanent debt determined?
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.
What inputs are required for each loan tranche?
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.
Can I model refinancing of senior debt within the analysis period?
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.
What are the LTV and LTC calculations based on?
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.
What is included in the amortization tables on the Perm. Debt tab?
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”
What do the different font colors indicate in the model?
Blue = user inputs
Black = outputs
Green = values pulled from other tabs
Brown/Orange = default outputs that may be used as manual inputs
