One of the more difficult aspects of modeling a real estate development is figuring out how to handle equity and debt cash flows. This becomes all the more difficult when a second tranche of debt is introduced. In this Watch Me Build video, I turn on my screen recorder and talk through modeling a capital stack with three capital sources: equity, mezzanine debt, and construction debt.
As with all of the tutorials in our real estate financial modeling Watch Me Build series, I’ve created a downloadable template together with completed worksheet. So download the template file and follow along as we build a three tranche capital stack together.
Are you an Accelerator member? Access this Watch Me Build video with source files here. You might also review the Modeling Development Debt and Modeling Multiple Tranches of Capital Sources Dynamically modules as compliments to this Watch Me Build. 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.
Capital Sources – Equity, Mezzanine Debt, and Construction Debt
The primary purpose of this Watch Me Build video is to forecast the capital draws from three sources of capital: equity, mezzanine debt, and construction debt. The exercise assumes that equity funds first. And once the full requirement of equity has been deployed into the project, the mezzanine debt funds next until it has been fully exhausted.
Once both the equity and mezzanine debt have been fully drawn, the construction debt funds the remainder of the uses of capital (i.e. project costs. The sum of the inflows of capital sources (i.e. equity and debt) must equal the sum of the outflows of capital uses.
The challenge in modeling three tranches of capital is to properly track when, and how much, capital is to be drawn from each source and in each period. To do this, I use the following rows under each of the capital sources:
- Available – This line tracks the amount of capital available to draw from at the beginning of the period.
- Draw – This line tracks the actual cash draws. I use a simple MIN() function to handle the waterfall of draws across the capital stack.
- Ending – This line tracks the amount of capital available to draw from at the end of the period.
- Account Balance – This line tracks the balance of the source’s account. In the case of equity, it is used to report the amount of equity deployed and the end of each period. In the case of debt, it is used to both track debt balance and also to calculate interest due.
Carry/Interest Cost of Mezzanine and Construction Debt
One difficult element of modeling development cash flows is tracking interest carry and interest reserve. Interest carry is a Use of Capital that is calculated based on the amount of debt (i.e. a capital source) deployed at any given time. This calculation is circular since the higher the debt balance, the more interest is due, which is funded by debt, which in turn increases the debt balance. And the circular logic goes on to infinity.
There are a several methodologies to handle this conundrum, each deserving their own tutorial. So to avoid distracting from the primary purpose of this Watch Me Build, I only take a cursory look at how to handle the circular nature of mezzanine and construction reserve accounts.
I use a basic, yet fairly powerful, technique for sizing the equity, mezzanine debt, and construction debt amounts. The methodology involves first manually sizing the debt to some loan-to-cost metric, and then iteratively changing the equity until Total Uses equal Total Sources.
Watch Me Build Video – Capital Stack with Mezzanine Debt for Real Estate Development
Before watching the video, download the template and completed file for this Watch Me Build. Then follow along as I build out the capital stack for a hypothetical development.
As I walk through building the capital stack, do your best to follow along. Once you’ve completed the video, attempt to complete the exercise a second time. Try expanding the model to include elements not included in the Watch Me Build, such as a more complete project budget, longer analysis period, and integration with a full DCF.
As always, if you have any questions or comments, please don’t hesitate to reach out.
Download the Source Files for this Watch Me Build Exercise
To make these files accessible to everyone, they are 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 – similar real estate training exercises sell for $100 – $300+). 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.
- Initial release