Back-of-the-Envelope Office, Retail, Industrial Acquisition Model (Updated 10.1.2018)

I’ve built a new acquisition model that I am excited to share with our readers. This is a back-of-the-envelope (BOE) valuation model that can be used for retail, office, and industrial properties. The goal was to create a sleek and clean look to the model that would enable a third party to easily understand what is going on and be able to get all the relevant information needed quickly and efficiently.

The model is almost all on one sheet, except for the loan amortization schedule, and can be read from left to right in the following order – Inputs, Return Metrics, the DCF Model, and the Sensitivity Analysis. This model should be fairly intuitive as you go through it, but I will walk the reader through the more nuanced and unique features in each section.

Some of the features this model includes are a sensitivity analysis, a way to add capital improvements at a later date and have it financed by the loan, plus a few other interesting features. This will all be explained in this post.

This is somewhat of a simplified model due to the fact that this does not allow for the user to plug in individual leases. Additionally, I have not broken the returns down for individual investors through a waterfall model, although one of our waterfall models could easily be incorporated into this model.

It will be beneficial for you to download the model now. The remainder of this post is a guide for the model. As always, feel free to contact me at any time with questions.

Download the Back-of-the-Envelope Office, Retail, Industrial Acquisition Model

To make this model 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 real estate Excel models sell for $100 – $300+ per license). Just enter a price together with an email address to send the download link to, and then click ‘Continue’.

---

BOE Acquisition Model – Explanations of the Features

Part 1 – Inputs

The Hold Period

One of the unique features to check out is by changing the holding period (cell D15). When you do so, notice what happens in the DCF model. For the years that exceed the holding period, the numbers disappear and the columns become gray. However, the following year’s cash flow will still remain until the NOI row. This is because the following year’s NOI is used along with the exit cap rate to establish the sale price. For example, if you have a 5 year hold, you will see the year 6 cash flow until NOI in the model. Play around with cell D15 to get a better understanding.

GPI

GPI (cell D22) is calculated in year 1 by adding the base rent, reimbursable expenses, and miscellaneous income in year 1 that is manually put in by the user in cells D19 through D21. This will grow by an inflation rate that is set by the user in the cell to the left of the GPI column (cell I10).

CAM

Although not the best way to project CAM, for the purposes of this model, I decided to make it a percent of the purchase price and grow it at an inflation rate set by the user in the DCF Model.

Capital Improvements (Cells D26-D29)

One of the things I like about this model is the functionality it has for capital improvements. In cell D26, you can put in the cost of the major capital improvement. Cell D27 allows you to pick which year you want the capital improvement to happen. However, there is a built in safety that will turn the cell red if you have placed the capital improvement cost after the holding period. I did this by using conditional formatting. Additionally, it will not show up in the cash flow projections.

Cells D28 and D29 will allow you to model out the financing for the major capital improvement if the original lender will allow you take out additional funding on the same loan. If so, the loan schedule will automatically recalculate the new payment and reamortize the loan from the year the capital improvement is financed. To do this, in cell D28, simply answer ‘yes’ to the question about whether the capital improvement will be financed. When you do so, text will appear in D29 and you can type in the percent of the capital improvement the loan will fund. If you click ‘no’ to the financing, cell D29 will be blank and be excluded from the calculations.

The Rest of the inputs I believe are fairly self-explanatory.

Part 2 – Return Metrics

The return metrics are automatically calculated and the user should not have to do anything for them

Part 3 – DCF Model

Additional Vacancy, Leasing Commissions, and Tenant Improvements (rows 12, 19, and 20)

These rows in the unlevered portion of the DCF are designed for the user to simply hard code any costs they project in any year. You don’t have to worry about doing the same thing for the levered portion of the model because it will automatically filter down. This was a simple way to compensate for not being able to aggregate these costs from individual lease inputs that would be included in ARGUS or a much more in depth excel model.

Part 4 – Sensitivity Analysis

There are two sensitivity analyses built into this model. One shows different IRRs when using different purchase prices and gross income and the other shows all the return metrics at different holding periods.

Sensitivity Analysis 1 – Gross Income and Purchase Price Sensitivity

In the top box starting in cell Z7, you can pick the required unlevered and levered returns (cells AD7 and AD8). Once you do so, the model will automatically turn the cells red in the data table that meet or beat your required returns.

In cell AD9, you can pick the incremental increase and decrease of the PGI column (Column AE) for the data tables and in AD10 you can pick the incremental increase and decrease for the purchase price row (row 18).

Without adjusting the inputs at all you can use cells AG15 and AG29 in the data tables to play around with the purchase price for the unlevered and levered IRRs, respectively. You can also use cells AA21 and AA35 to adjust PGI.

Sensitivity Analysis 2 – Holding Period Sensitivity Analysis (Starting in Cell Z44)

The holding period sensitivity analysis shows the different returns metrics for different years. There is no adjustment to be made within this section.

Final Thoughts

I believe I have explained all the essential components to the model that will enable you to use it without a problem. However, don’t hesitate to reach out if you have any questions or recommendations, or if you would like more of an understanding of how the model works.