What Can You Learn from Reviewing 23 Real Estate Financial Models that Are not The Best in the World

My followers know that one of the projects I’ve been working on for the last couple of months was creation of a real estate financial model for a US-based investment fund. They do a lot of deals, and in order to choose the best ones they need to evaluate dozens of proposals. The fund’s manager has formulated the task very simple: “We want to have the world’s best multi-family real estate financial model”.

Over the years they have tried different versions of RE financial models with mixed success. By the time they have contacted me for the project, the fund has collected a whole library of models that were not the best in the world. Some models were better than the others. Some models were more detailed (and complicated) while others were too simplistic.

To produce something that is best in class, you need to evaluate the competition first. I did just that and started my work by reviewing the other models that the client was using previously, as well as the industry models that were available to me. I can’t share or publish the model, as it is proprietary to the client, but at least I would like to share some interesting findings that might be useful for your modeling too!

Construction Cost Assumptions are Far from Real Life

Most of the models use some sorts of approximations for modeling the construction costs. Some very simple models just assume that construction happens at year 0 and start modeling from the time when the project is completed. More advanced real estate financial models check for the number of months the construction process takes place and then equally split the costs in equal installments over the construction phase. Even more advanced models allow users to check the starting and the ending month for each component of the construction budget. But they still split costs equally over these months.

However, in real life construction costs usually follow a bell-shaped curve. At first months few activities happen, then the project takes off and the most intensive construction happens in the middle of the project. Final construction months usually are less active as well (see a typical chart).

While the total investment is the same, planning construction costs in a form of a bell curve is closer to real life and has some advantages from the investor point of view. Typically equity is invested first and the bank is the last one to invest into the project. The bell curve allows a longer use of equity and shorter use of construction loan. It also allows to size the equity investment in a more correct way and gives a more accurate forecast of interest payments.

Visual Presentation Is Important

While most of the time a financial modeler spends creating formulas and making calculations, the readers of a financial model are usually concerned just with a couple of key numbers. It is much easier to grasp the key numbers with the help of charts. Having a dashboard that presents the numbers is usually very well received and easily understood by users.

The models I have reviewed could be split into 3 goups. Some of them concentrated just on the numbers and had no charts whatsoever. Few models had a good amount of charts – not too many, not too few. And there was one model that, in my opinion, was just too heavily concentrated on charts. I had a feeling that the authors of that model tried to make some sort of an app in Excel.

My opinion here is straighforward and is based on hundreds of client meeting. The model makes the back-end calculations, but you present the outputs to the client. The output page could be a mix of numbers and charts, or just a dashboard, however, the presentation should be neat and well-looking, ideally formatted in the corporate colors of the client. The back-end calculations should also be well formatted and logically structured so that a professional modeler can easily understand the model.

Unified Financial Modeling Standards? Seems Noone Has Ever Heard of Them.

It is interesting to see how different models use different colors and cell formats. Amazing, but the 23 models used as much as 23 different formatting conventions! Probably the most common approach was using the blue color for assumptions but even this varied as different background colors were used.

Some real estate financial models are using only the black-grey-while color scheme. OMG, that’s just so boring! On the other extreme, some other models really looked like a rainbow – they used all sorts of colors but the overall impression was somewhat unprofessional.

Again, my approach is somewhat neutral. I don’t like financial models that are purely black-and-white. I prefer using a limited number of colors to format table and section headings, visualize the outputs, etc. Tip: take some 3 to 5 colors from a client’s logo use them within the model to format table headings and charts. The clients just adore to see that the model is already build in their corporate colors and style.

At the end of the day, we are probably very far from having some unified look for financial models. Each major company has its own formatting convention. Even if you look at industry authorities you will see that the models by Financial Modeling Institute are very much different from those by let’s say Corporate Finance Institute. So, the industry still has a long way to go before introducing some worldwide standards.

Monthly Data is Important, but How do you Keep the Model Simple?

I faced a huge trade-off between precision and simplicity. Looking from a RE fund’s perspective, the model should have some 10 year horizon. If you model it monthly, then it gets too complicated, difficult to review by average users with limited financial modeling experience and impossible to print. If you make an annual real estate financial model, the numbers will be very incorrect, especially in the earlier years of the projections. Some models solve it by having the first 2 years projected on a monthly basis and then – on an annual basis. However, it is a nightmare from the modeling point of view. Too many changes in the formulas, too many ways to make modeling errors.

After a couple of iterations I have found a great way to solve this. I’ve split the model into 2 parts. First, the back-end calculation part that was made on a monthy basis. Second, the front-end summary part that just summarized annual data from the monthly tabs. Making a clear summary and different color coding for the tabs allowed the model to stay intuitive and easy to understand by non-modelers.

Structure and Formatting Make Life Easier

Real estate projects are complex enough to model. This is the reason why most of the models I’ve reviewed were too complex to review even for me. (I’ve been doing financial modeling and model auditing since 2001). I would say that if an experienced financial modeler spends a couple of hours trying just to understand the data flow in the 40+ tabs that are included in a model, that’s a clear sign that a model is way too complicated.

However, even a 1-tab real estate financial model could be a mess. The number of tabs as such is not an indicator of a financial model’s simplicity. During my review I’ve seen a couple of nice models that had more tabs than other models. However, their structure was logical, the tab names were clear, the tabs were grouped by colors, etc.

For one model I even got a feeling that it was purposefully created the complicated way to justify the price.

“Making a Complex Model Is Easy. Making a Simple Model – Next to Impossible“

Tip: in a complex financial model make a table of contents with links to the corresponding tabs. Groups tabs by colors, e.g. green tabs are annual summaries while yellow tabs are monthly back-end calculations. Use numbers and letters in tab names, e.g. “A1. Cover tab”, “A2. TOC”, “A3. Assumptions”, “B1. Project Summary”, etc.

A Professional Modeler Would do Better than a RE Specialist

The final thing that probably surprised me the most in this project was the fact that a professional modeler would probably do a better real estate financial model than an RE specialist. Probably all of the models that I have reviewed were created by people that specialize in real estate. There were several financial models in my portfolio that I have created for clients in real estate, but I’m far from claiming expert level of industry experience. Most of my experience comes from modeling financials for the main street businesses (retail, manufacturing, wholesale, professional services, etc.).

However, putting together my overall financial modeling experience and the client’s advice on the industry specifics, allowed us to produce the final result that I am really proud of. Again, I can’t share the model as it is proprietary to the clients, but at least I could share some small tips that could make your models much better too!

The Outcome

Let me just quote the client’s feedback after he has made the first presentation using the new RE model.

“The presentation was fantastic. I felt the model was easy to follow and presented very well. This new model will help us tremendously.”