How to Create a Real Estate Investment Model in Excel
What is a Real Estate Investment Model?
A real estate investment model is used to analyze the cash flows and returns to stakeholders from a real estate asset or collection of real estate assets. Unlike corporations, which use three statement modeling (income statement, balance sheet, and cash flow statement), a real estate model is primarily a cash flow statement with several ancillary pages on the revenues, costs, financing, metrics, and returns. Several assets can be grouped into one model; however, in most cases the analysis is done on a property-by-property basis.
Key Learning Points
- Real estate models have several purposes, but are principally intended to bring efficiency to the buying, managing, and selling of real estate assets.
- Models are mainly cash flow-based with very little, if any, accrual-based accounting
- The key drivers of a real estate model are rental revenues, operating expenses, and capital expenditures. Net operating income, as well as levered and unlevered cash flows, are the key measurement figures.
- Leases analysis is the most detailed component of real estate modeling.
- Like most transactions in finance, models can be built quickly for back of the envelope analysis or in great detail for transaction support.
Importance of Real Estate Modeling
In real estate, a successful deal is defined by paying the right price, choosing the correct capital structure, managing the asset at the highest level of efficiency, and selling at the right time and price. In order to compile and present that data for analysis, a model is needed.
The primary stakeholders in a real estate investment are the debt and equity holders. The analysis is geared toward satisfying their risk/return profiles. Let’s look at the debt investors first. They are making a loan based on the value of property. This is called loan sizing. However, once the loan is made, there must be cash flows to service the loan — the interest and principal payments. A cash flow analysis of the property is necessary to determine if the rent receipts, net of any building expenses, are sufficient to service the debt. The cash flow that debt holders look at to determine debt service coverage is called the unlevered cash flow, as it has not yet been affected by the financial structure.
The example below shows a model from NOI to levered and unlevered cash flow.
From the equity perspective, the model needs to do more than that since equity investors have a greater cost of capital, or required return, than do debt holders. The real estate model will look at historical cash flows to assess the asset is in terms of generating current cash flow. Assumptions must then be made about the future cash flows. It’s necessary to forecast rental revenues and building operating expenses, as well as capital, or longer term, expenditures. Since the equity holders’ returns come after the debt holders, we need to look at the cash flows after debt service. This is called the levered cash flow. It is the residual cash flow that goes to equity investors. Equity holders measure their returns using metrics such as IRR (Internal rate of return), MoIC (multiple of invested capital), and cash on cash yield (which measures the regular dividend payments)
The following is an example of the kind of analysis performed for equity investors. Access the free download to see
Step-by-Step Process to Real Estate Financial Modeling
1) Net Rental Revenues
Rental revenues are the key drivers and they are heavily dependent on the leases that are in place. A very detailed real estate model will have lease assumptions for each tenant. Assumptions are also made for rent increases, as well as potential vacancies, and for credit losses (tenants not paying their rent). This analysis can be extremely complicated from a modeling perspective so it is typically only done once a buyer is serious about investing or by the asset manager who is responsible for keeping the property’s returns high. A modeler will also have to consider any ancillary revenues that are part of the property such as parking, concessions, vending, storage, etc.
Below is an example of detailed lease analysis modeling
2) Operating expenses
The cost of running the building day to day are the operating expenses. The main building expenses include insurance, property taxes, and common area maintenance fees (CAM). In addition, there are the costs of utilities for the rented spaces. Depending on the type of leases, these expenses are either fully paid by the lessee (renter), shared by the lessor and lessee, or are paid entirely by the lessor. This will have an impact on the model. In many buildings, the leases are consistent, so if one tenant is responsible for their share of the expenses, the other leases most likely work the same way. Expenses are shared by the pro rata share of the property’s rentable space.
The net of the rental revenues and the operating expenses is called the Net Operating Income, or NOI, and it is a very basic measure of the asset’s profitability.
3) Capital expenditures
There are expenses that are not considered part of the daily upkeep but instead, benefit the owners for the longer term. These are the significant upgrades to the property such as landscaping, new elevators, painting/refinishing, and structural elements such as roofs, parking decks, etc. These are slightly more discretionary than operating expenses so they are not included in the NOI.
In addition, there are a few other expenses that fall into the capital expenditure category that are related to how the owner manages the property and they are not considered day-to-day. When vacancies are rented or leases are renewed, a broker is paid a commission on the contract. That is considered long term since it benefits the life of the contract. Furthermore, to attract new tenants there are several concessions that are made that involve improving the space for the new tenant. These can be upgrades to the infrastructure or funds to be applied to the buildout of the space.
The net of the NOI and the capex will deliver the unlevered cash flow, which we can use to begin our analysis to the stakeholders. Due to the unique tax structure of real estate assets, corporate tax is not paid but rather personal taxes are applied to the pass- through earnings.
4) Debt Schedule
The terms of the loan or loans must now be modeled so that we can determine if there is enough cash flow or cushion to support the loan. We would also need to determine the principal and interest payments to determine the levered cash flows, or cash flows post-debt service. An example of a debt schedule is shown in the graphic below.
5) Equity returns
The levered cash flows will enable us to look at what cash is left over for quarterly or annual distribution to investors. With the levered cash flows modeled out over the life of the investment, we can also determine what the overall return is to the equity investor using an IRR analysis. An example of an equity returns analysis is shown in the graphic that appears earlier in the blog post.
Types of Real Estate Models
Depending on the timing and the information available, a real estate model can be very basic or extremely detailed. With all forecasting, there is an element of guesswork and assumption. With real estate, however, we have the ability to forecast according to the leases in the building. That means a very detailed model will often show monthly cash flows since that is how rent and operating expenses are handled. Debt holders will often require monthly service as well. Investors might not need to see monthly returns as their distributions are either quarterly or annually. But once we have a monthly model, it is easy to add quarterly or annual tallies.
For a transaction in the due diligence stage or an asset manager overseeing the efficiency of the property, a detailed lease-by-lease or tenant-by-tenant model is necessary. For any other purpose, a model can be built making broad assumptions about the revenues and costs according to both historical figures and market projections.
Conclusion
Real estate models are critical to the analysis necessary to buy, manage, and sell a property. These models are typically done on a monthly basis but can be quarterly or yearly. They can be extremely complex and model cash flows on a per-tenant or per-lease basis, or they can be higher level and forecast according to historical and general market trends. Either way, stakeholders in the deal will be able to see their potential returns and the risks that are inherent in the investment to determine if it is a good fit or if improvements can be made to enhance the returns. The Real Estate Analyst Course will teach you how to build a real estate investment model.