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 countless 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 overall 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 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 the 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 that you must not change.
There is also 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 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 in 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 financed 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 flow, 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 is where the most critical checks are aggregated. Whenever you see an error message on any page, you should consult this page to see where the error is coming from.
Similar Products
Other customers were also interested in...
Airbnb Financial Model
Air BnB Financial Model Template presents the business case of the purchase of up to 5 properties wi... Read more
Multi – Unit Condos Model Template
Multi-Unit Condos Model presents the business case where a property with multiple residential units ... Read more
Real Estate Bundle 2 (6 models)
Planning to start a real estate endeavor but don’t know the financial implications of your investm... Read more
Construction / Development Financial Model
Development & Construction Model presents the case where a property with multiple residential un... Read more
Commercial Real Estate Excel Model Template
Commercial Real Estate Excel model which generates the cash flows of the project as well as for equi... Read more
Real Estate Portfolio Dashboard Model
Real Estate Portfolio Dashboard model presents a series of dashboards that will allow the user to ch... Read more
Mixed-Use Real Estate Model: Leverage / JV Options
A general real estate model to plan all assumptions for up to 7 'uses' for a given property. Include... Read more
Commercial Real Estate Valuation Model Template
A commercial real estate valuation model template assists in running a professional DCF Valuation fo... Read more
Real Estate Acquisition-Rent-Sell Comprehensive An...
An integrated, dynamic and ready-to-use Real Estate Acquisition-Rent-Sell Comprehensive Analysis Mod... Read more
Commercial Real Estate – Lease or Sell Quart...
Commercial Real Estate - Lease or Sell Quarterly Excel Model with 3 Statements, Valuation, and Devel... Read more
You must log in to submit a review.