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.
