3-Tier Waterfall Real Estate Model
Waterfall models in real estate projects are one of the most difficult concepts to understand. Cash flow from a development or investment project can be split in a countless number of ways. A waterfall is a method of splitting profits among partners in a transaction that allows for profits to follow an uneven distribution. The waterfall structure can be thought of as a series of pools that fill up with cash flow and then once full, spill over all excess cash flow into additional pools.
This Excel model contains 2 types of waterfall methods: the American Waterfall and the European Waterfall. The American Waterfall distributes profits based on a pro rata basis between the investor and the sponsor, effectively sharing the risk between the two. On the other hand, the European Waterfall distributes profits first to investors until they get back their capital and preferred returns, and then starts distributing profits to the sponsor, effectively minimizing the risk to the investor, and increasing the risk to the sponsor.
So, a quick overview of the model, in the contents tab you can see the structure of the model and by clicking on any of the headlines to be redirected to the relevant worksheet.
On the manual tab you can feed the general information for the model such as: model name, responsible, timeline of the model and date and currency conventions.
Additionally, there is a description of the color coding of the model in the same tab. Inputs are always depicted with a yellow fill and blue letters, call up (that is direct links from other cells) are filled in light blue with blue letters while calculations are depicted with white fill and black characters. Grey cells are restricted cells which you must not change.
There is also a color coding for the various tabs of the model. Yellow tabs are mostly assumptions tabs, grey tabs are calculations tabs, blue tabs are outputs tabs (that is effectively results or graphs) and finally light blue tabs are admin tabs (for example: the cover page, contents, and checks).
In the assumptions tab, the user can change the potential rental income along with the reimbursements and vacancy percentages, as well as the various expenses such as the property taxes, insurance, utilities, and common area maintenance charges.
Additionally, the user can set the year where the real estate property will be sold, the exit multiple to be used based on the Net Operating Income of the same year, as well as the exit selling costs.
Afterwards, the user needs to input the financing assumptions. First the users fill the uses of the funds such as the property acquisition costs, the required refurbishments, and the costs related to the acquisitions. Then the user sets the amount to be finance by debt, as well as the interest rate and the loan maturity. The remaining equity financing is calculated, and then the user needs to split the equity financing between the sponsor and the investor.
Finally, the user needs to set the equity waterfall assumptions, both for the American waterfall and the European waterfall, such as the hurdle rates for each Tier and the distribution splits between the sponsor and the investor at each Tier.
In the Cash Flows tab, the operating and financing cash flows are calculated and result in the expected cash flows of the real estate project, as well as the cash balance, from a company perspective.
The next tab is the Waterfall tab, where the project and equity cash flows are calculated along with the relevant metrics such as the internal rate of return, the net present value, the payback period and the cash on cash multiple.
Afterwards, the American Waterfall is calculated for the 3 Tiers, and the relevant metrics for the investor and the sponsor are calculated. The same logic is followed for the European Waterfall, which results in the same metrics for the investor and the sponsor, such as the internal rate of return, the net present value, the payback period and the cash on cash multiple.
All this information is summarized in the Summary tab, in terms of operating and financing assumptions, metrics, sources and uses, cash flows, as well as in terms of waterfall assumptions and their resulting outputs, for both the sponsor and investor, as well as for the American and European waterfall method.
Summary information is also presented in the Graphs tab, such as the rental income, the operating and financing flows, the total cash flows and cash balance, the sources and uses of cash, the project and equity cash flows, the investor and sponsor flows, the various metrics for the investor and sponsor (for both the American and European waterfall), and finally the cash flow distribution per Tier for both types of waterfall.
Finally, the checks tab where the most critical checks are aggregated. Whenever you see an error message in any page, you should consult this page to see where the error is coming from.
