What’s your role in commercial real estate? 

Developer? Development Lender?  Qualified Opportunity Zone Fund (“QOF”)?

Actually, it doesn’t matter.  If you oversee Excel modeling for any of the above, chances are you end up dealing with not only your own internal development Excel models, but someone else’s as well.

Maybe it’s a model you were bequeathed by the person previously in your position, or it’s one you picked up off the heap of prior deals that was close enough.  “We’ll just tweak that last one” are famous last words, often of someone who isn’t responsible for the tweaking.

30 Years of Commercial Real Estate Excel Models

At Realogic, we have been dealing with Excel development models for over 30 years. When we first started the company, it was always hard for us to efficiently develop and use Excel models in a cost-effective manner.

Why? Because we had not embraced standardization.  We were doing what you are likely doing:  Need a model for a multi-family project, for example? Then let’s start with the last Excel model used, even though it was for a mixed-use development with multiple phases.  This just isn’t smart or efficient.

Commercial Real Estate Development in Qualified Opportunity Zones

One area of commercial real estate where Realogic is seeing a tremendous amount of interest and activity right now is Opportunity Zones. Even in this uncertain economy, developers, investors and fund and asset managers continue to flock to QOZs and QOFs because of the tremendous tax benefits they offer. As a result, QOF development analysts, including our own, are dealing with an almost overwhelming number of deals.

Standardizing Your Commercial Real Estate Excel Models

According to Novogradac, a national accounting, valuation and consulting firm, as of June 30, 2022, Qualified Opportunity Zone Funds had raised a total of $30.5 billion since the QOZ program’s inception, with $6.1 billion raised in the first half of 2022 alone.²

So, if they are going to underwrite deals accurately, whether it be one or hundreds, analysts must embrace the standardization of modeling practices.  A standardized approach is necessary if your analysts are spending more time evaluating a spreadsheet for integrity rather than evaluating the specific deal parameters and returns.

The Benefits of Standardizing Your Commercial Real Estate Excel Models

Realogic has been developing our standardized and modular approach towards financial modeling for commercial real estate for many years, and we have found it offers many significant benefits, including:

  • Accuracy: Do not be dependent upon someone else’s model! Your firm should have a model that has been vetted for accuracy, project after project. Unfortunately, auditing someone else’s formulas instead of vetting their underwriting assumptions is often considered a cost of doing business. However, that cost can be reduced or even eliminated completely.
  • Accuracy Pt II: Tested platforms behave as they should, no unexpected gotchas. We have all heard that the majority of Excel models have errors.  However, these models are often not standardized models, nor are they back tested models confirmed against prior results and specific test cases.
  • Speed: Familiarity with a model allows users to work quickly. A well-organized and flexible model allows users to quickly ascertain if proposed investments are in line with their return expectations.

Development models should be architected with the ability to grow as ideas take shape. Developments often start as low detail, high expectation projections and quickly evolve into more detailed combinations of reality and projections.

  • Institutional Knowledge: The combination of each of the above components becomes part of your company’s DNA.

Similar vs Dissimilar Components In Commercial Real Estate Excel Models

Our approach to standardized Excel model development is multi-fold, but simple in concept:  we’ve learned which components of each model are the same for each deal and which components differ.  This will clearly change from organization to organization, but our experience should help give you some ideas.

Operating Components In Commercial Real Estate Excel Models

The first thing to standardize that was obvious to us was the Operating Component. This is the product type under development, such as hotel, multi-family, commercial, student housing, senior living, mixed use, multi-phase, etc.

In our case, it happened to be yes to all of them.  We had to break the operating component into its own worksheet or set of worksheets, if more than one.  There are a few major advantages to this method:

Advantage One: Excel modelers often try to get to a lofty goal of a single page of input. However, we feel this approach is too constraining.  Well-designed worksheets will increase accuracy and speed via a logical layout.

Combining the development or capital structure into one worksheet, along with the future operating components, leads to a tangled mess.  Think of an Excel worksheet as a specific tool designed for a specific job. Data from other worksheets can be brought over easily and efficiently.

Advantage Two: A multi-worksheet approach allows for a clean framework for mixed-use or multi-phased developments.  Need to add another phase? Add another worksheet.  Need to add a retail component to your office park? Simply add another worksheet.

Advantage Three: The worksheet approach is a simple way to achieve sensitivity analysis and turn leasing scenarios or phases on and off with ease.

Once you separate the operating components and turn them into interchangeable modules, the similarities of a development project start to become visible.

Development and Construction Modules In Commercial Real Estate Excel Models

Development/Construction should be its own module or worksheet in Excel.  There are common components regardless of product type being developed:

We have contemplated the nature of each and will be addressing best practices about modeling each of these components in future blog posts. Specific project details may change on a project by project basis, but the need to set up a series of events bounded by a time frame such as the Analysis Start, Land Hold, Construction, Lease Up and Operations don’t change.

Can we think of every possible deal term ahead of time?  No.  However, we can create unlimited user defined time frames and uses throughout the model if it’s architected correctly.

Reusing The Components of Your Commercial Real Estate Excel Models

We could continue with a discussion about standardized Executive Summaries, Sources and Uses, Returns and Waterfalls.  All of these can be modularized and standardized for re-use.  Analyzing a stream of cash flows will not change from product type to product type.  Some will be smooth, others lumpy, and others will have a set of singular events.

Modeling a multi-tiered waterfall, with profit catch-ups based upon monthly, quarterly or annual timing and compounding, along with LP and GP return calculations on both a gross and net basis sounds hard—and it is. But it’s much harder to build a new model and test it every time you need something slightly different.

Standardizing Your Commercial Real Estate Excel Models Doesn’t Happen Overnight

Improving your team’s performance is incremental, but like a lot of things, you just need to start at the beginning.  As mentioned earlier, we have been working on our standardization process for a long time. We didn’t accomplish it overnight, but now our team is able to accurately and effectively evaluate the dozens of deals our QOF clients are seeing in non-standardized models.

Additional Resources On Financial Modeling For Commercial Real Estate

If you’d like to learn more about Realogic’s comprehensive suite of standardized, modular Excel models, visit the Built Excel Models page on our website.

Or, if you’d like to see our ready-to-use Excel model for commercial real estate development in action, watch Part 1 and Part 2 of our demo video.

In addition, there’s another post on our blog about financial modeling for commercial real estate you may find helpful and insightful. It’s on reviewing market rent inflation in Argus Enterprise models.

Or, perhaps you have a project or set of projects that do not fit neatly into the categories mentioned above. If so, we would love for you to share it with us by emailing us at info@realogicinc.com so we can better understand how we might be able to help you.

Development pro-formas have long been the domain of Microsoft Excel. Before you start, mentally run down the modules that you have standardized and ask if a tried and true component can be re-used prior to unleashing your creativity.

About The Author

Jim Pettinger is CEO of Realogic. In 2024, the commercial real estate publisher GlobeSt. named him one of CRE’s Best Bosses. That same year, Crain’s Chicago Business recognized him as one of Commercial Real Estate’s Notable leaders. Jim has worked in commercial real estate for nearly 30 years and is an expert in financial modeling for real estate as well as Argus Enterprise. He has extensive experience in many facets of commercial real estate, including underwriting, due diligence, lease administration, lease abstraction, budgeting and closing support. You can reach Jim at jpettinger@realogicinc.com.

*Sources:

1-GAO-22-104019, OPPORTUNITY ZONES: Census Tract Designations, Investment Activities, and IRS Challenges Ensuring Taxpayer Compliance (novoco.com)
2- Opportunity Zone Funds Exceed $30 Billion in Fundraising – The DI Wire