In an effort to provide greater instruction on how to use our All-in-One Underwriting Tool for Real Estate Development and Acquisition, we’ll be developing a series of walkthrough videos and posts on the methodology behind the various components of the model. Our hope is that if you are empowered with the how, you’ll be more willing/able to provide feedback to improve 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 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.
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, three amortization tables are built and displayed on the Perm. Debt tab. The first is for the senior debt, and includes aspects typical of amortization tables (balance, payment, interest, principal) but also includes lines to show loan funding and payoff. A second table for junior debt has identical outputs. The third table, ‘Total Permanent Financing’, likewise shows the same outputs and represents the sum of the senior and junior debt outputs.
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.
