Scenario Analysis Tool using the Equity Waterfall Model (UPDATED MAY 2022)

I received a question this morning asking how to use scenario (or sensitivity) analysis with our Real Estate Equity Waterfall Model. I started to answer the question via email, decided to build an Excel file to go along with my answer, and ultimately concluded that a video would be the easiest way to walk you through how I do scenario analysis.

Now keep in mind, Mike touched on this subject in a previous blog post several months back on how to build data tables for real estate analysis. This post/video covers the same topic but comes at it in a slightly different way. In my mind the question comes down to: how do we analyze the effect of changes in the terminal cap rate and changes to cash flow over the hold period on LP, Sponsor, and property level IRR? I hope the video below answers that question and provides you with a foundation to do your own real estate scenario/sensitivity analysis.

I’ve attached a link below to the Excel file used in the video. If you have any questions, please don’t hesitate to contact either Mike or I.

Scenario Analysis tool using the Equity Waterfall Model

Scenario analysis using the data table feature in Excel
Based on an earlier version of the real estate equity waterfall model

Compatibility

This version of the model is only compatible with Excel 2013, Excel 2016, and Excel 365.

Download the Scenario Analysis Toolusing the Equity Waterfall Model

To make this tool accessible to everyone, it is 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 – typical tools sell for $25 – $100+ per license). 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.

We regularly update the file (see version notes). Paid contributors to the tool receive a new download link via email each time the tool is updated.

Proceed to Download Page

Frequently Asked Questions about the Scenario Analysis Tool using the Equity Waterfall Model

What is the Scenario Analysis Tool using the Equity Waterfall Model?

It is an Excel-based tool designed to evaluate how changes in terminal cap rate and cash flow over the hold period affect LP, sponsor, and property-level IRRs using the Equity Waterfall Model.

What question does this tool aim to answer?

The tool addresses: “how do we analyze the effect of changes in the terminal cap rate and changes to cash flow over the hold period on LP, Sponsor, and property level IRR?”

How is the scenario analysis conducted?

It uses Excel’s data table feature to simulate various terminal cap rate and cash flow scenarios, then tracks their impact on different levels of returns.

Is this tool based on a previous model?

Yes. It is based on an earlier version of the real estate equity waterfall model shared previously by the A.CRE team.

What versions of Excel are compatible with the tool?

The tool is compatible with Excel 2013, Excel 2016, and Excel 365.

Where can I download the tool?

You can download it via the “Pay What You’re Able” option. “Just enter a price together with an email address to send the download link to, and then click ‘Continue’.”

Are updates provided for the tool?

Yes. “Paid contributors to the tool receive a new download link via email each time the tool is updated.”

Version Notes

v1.0

Initial release

About the Author: Spencer Burton is Co-Founder and CEO of CRE Agents, an AI-powered platform training digital coworkers for commercial real estate. He has 20+ years of CRE experience and has underwritten over $30 billion in real estate across top institutional firms.

Spencer also co-founded Adventures in CRE, served as President at Stablewood, and holds a BS in International Affairs from Florida State University and a Masters in Real Estate Finance from Cornell University.

Contact Spencer