All-in-One (Ai1) Walkthrough #2 – Office, Retail, Industrial Rent Roll Tab

This walkthrough, our second in the series, will detail how to use the office/retail/industrial rent roll (see ORI-RR Tab). The rent roll tab is arguably the most important, and most complex, tab in the All-in-One model. Below we post a video of the walkthrough together with a brief description of the key components of this tab.

Video Walkthrough – Office/Retail/Industrial Rent Roll Tab (ORI-RR tab)

The following video walkthrough of the ORI-RR tab (Office/Retail/Industrial Rent Roll) has been updated as of v0.78. It replaces the earlier video recorded in 2017.

---

Key Components of the Office/Retail/Industrial Rent Roll Tab (ORI-RR Tab)

Quick Note About Format Convention

As always, blue font cells are input cells, black font cells are outputs. Green font cells are values calculated on other tabs and brown/orange font cells are default outputs that may be used as an input when the user deems it necessary.

Note on Limitations in the ORI Module

While the All-in-One is meant to handle more complex lease scenarios and therefore can do a lot of the things that people turn to non-Excel solutions for, it still has Excel-specific limitations that it’s important to be aware of.

First, the ORI module does not support reimbursement of management fees. The complexity of handling the circular nature of reimbursing management fees outweighs the benefit. As a workaround, we’ve included an Admin Fee that when modeled as approximately the same amount as the management fee gets you to approximately the same outcome.

Second, the ORI module is limited to three generations of leases. This is to avoid exceeding Excel’s memory capacity. Given that the max analysis period in the All-in-One is 10 years, and most long-term leases are 3+ years long with downtime between leases, this limitation shouldn’t affect most people. But if your scenario includes leases shorter than three years, you’ll likely need to use a different solution or shorten your analysis period.

Finally, the lease reimbursement module in the All-in-One has limitations. While NNN and Gross leases generally model well, when you get into gross modified/base year type leases, the reimbursement amounts are more approximations than an accurate forecast of the reimbursements. This again is due to the limitations of Excel. If you need an exact forecast of future reimbursement cash flows, you’ll need to use a non-Excel solution.

Unhide Operation Module Tabs

On the summary tab, set cell F34 to “Show” to reveal the Office/Retail/Industrial operation cash flow section. Note that this functionality is included to allow you to reduce tab clutter along the bottom of your model (i.e. hide those tabs when you’re not using them) plus allow for the inclusion or exclusion of this module once a second, multifamily, module is added.

Acquisition vs. Development Module

Whether the model uses the acquisition or development module is automatic, based on whether you include a development length in cell M10 of the Summary tab. Irrespective of whether the investment is acquisition or development, you will use the ORI-RR tab to enter office/retail/industrial leases.

# of Tenants

Before doing anything else on the ORI-RR tab, first set the number of tenants (i.e. number of rentable suites/spaces) in the building in cell D6 of the ORI-RR tab. The model will then reveal the rows that need to be completed.

Rent Calculation Method

In cell L6 of the ORI-RR tab, set whether you will be entering rent as total rent per year (‘Annual $’), per square foot rent per year (‘Annual PSF’), or total rent per month(‘Monthly $’). This will then adjust the rent inputs in columns to correspond with your selection. The default is Annual PSF.

Tenant/Space Description (Columns C:G)

Enter basic details about the tenant/space in columns C through G.

Suite: Enter the suite number (e.g. 100, 9201, B101, etc)

Lease/Spec: If a contract/LOI is in place for the space, set this cell to “Leased”. If the space is vacant, set this to ‘Spec’. The Leased SF vs. Vacant SF table in cells O6:Q8 will adjust accordingly to show the current occupancy.

Tenant: Enter the name of the tenant. If the space is vacant, enter ‘Vacant’.

Tenant Label: This cell currently has no use, but is included to allow for future analysis of the rent roll. The idea is to assign a label to each tenant (i.e. Law Firm, Financial Services Firm, etc), which can then be used to analyze the rent roll by tenant type (i.e. label). For instance, if you’re concerned about exposure to Oil & Gas firms, you could label each firm that is in the Oil and Gas industry and then calculate what percentage of the rent roll (SF or gross rent) comes from Oil & Gas tenants.

SF: Enter the net rentable area of each space

Lease Term Details (Columns I:AB)

Enter the terms of the 1st generation lease in columns I through AB

Start: Enter the lease start date. If the space is vacant, enter the projected lease start date.

End: Enter the lease end date.

Annual PSF/Annual $/Monthly $: In column M, enter the rent for each tenant as of the analysis start date (Summary!M7). Whether you enter rent as an annual amount, annual per square foot, or monthly amount depends on your ‘Rent Calculation Method’ (ORI-RR!L6)

Inc. Method: Input the method you’d like to use to model the rent increases of the 1st generation lease. Options include:

Detail: Use ‘Detail’ if the lease’s contractual rent increases are not annual (e.g. every five years) or not the same each year (e.g. 1% in 2018, 2% in 2019). If you select ‘Detail’, be sure the Rent Detail section is unhidden (check box above column AC) and that all cells have values in non-greyed cells in columns R through AB. Set the rent per square foot as of the first month of each analysis year (see dates in cells S11:AB11).

Inc. %/Yr.: Increases rent by a specified percentage each year through the end of the 1st generation lease term

Inc. $/Yr/: Increases rent by a specified dollar per square foot each year through the end of the 1st generation lease term

Note about Precision of 1st generation rent: This is one area where the All-in-One model lacks the precision of the non-Excel real estate valuation tools. While this model allows for detailing rent, it does so on an annual basis rather than on a monthly (or even daily) basis. Thus, the modeled rent will be a close approximation but not exact.

Leasing Assumptions (Columns AC:AE)

In columns AC through AE, enter the outstanding (or projected) tenant improvement, leasing commission, and rent concessions for each 1st generation lease. If the tenant has already occupied the space, these cells will likely be set to zero (i.e. all TIs, LCs, and concessions have already been spent). However, if there are outstanding commitments to tenants and/or to-be-leased space, you will need to enter those values here.

Expense Reimbursement (Columns AF:AR)

Use this section to model the expense recovery income for each 1st generation tenant.

Detail?: Choose whether to detail the expense recovery percentage by year (Yes), or use the same percentage each year (No).

% of Pro Rata Reimbursable OpEx: This will be greyed out if ‘Detail?’ is set to ‘Yes’. Enter the percentage of pro rata (based on NRA) reimbursable operating expenses each tenant is likely to pay (or landlord is likely to recovery) based on the terms of the 1st generation lease. In other words, if you (the landlord) believe you’ll recover 100% (i.e. NNN lease) of reimbursable operating expenses, then set this cell to 100%. If you believe you’ll recover 0% (i.e. gross lease) then set the cell to 0%.

Detailing Recovery % (Columns (AI:AR): If ‘Detail?’ is set to yes, you’ll need to complete the cells in columns AI through AR. Enter the estimated recovery percentage for each year.

Note about Precision of 1st generation expense recovery: This is another area where the All-in-One model lacks the precision of the non-Excel real estate valuation tools. While this model allows for detailing expense recovery, it does not allow you to detail specifics  (ie. base year, recovery of only specific expenses, etc). I’ve opted to use the expense recovery percentage concept (a method I’ve used extensively in the industry) as an approximation rather than attempting to attain complete precision through modeling every possible scenario or lease clause.

Future Tenant Assumptions (Columns AS:BE)

Once you’ve completed the inputs (assumptions) for your 1st generation leases (Columns C:AR), you’ll need to enter assumptions for your future (2nd and 3rd generation) tenants.

New TI: Enter the current market rate per square foot of tenant improvements for new tenants (i.e. newly occupying tenants)

Renew TI: Enter the current market rate per square foot of tenant improvements paid when existing tenants renew their lease

New LC: Enter the current market leasing commission percentage (percentage of total rent paid over the lease term) for new tenants (i.e. newly occupying tenants)

Renew LC: Enter the current market leasing commission percentage (percentage of total rent paid over the lease term) when existing tenants renew their lease

Free Rent New: Enter the current months of free rent offered on leases to new tenants (i.e. newly occupying tenants)

Free Rent Renew: Enter the current months of free rent offered on leases to existing tenants when they renew their lease

Mkt. Rent PSF @ Analysis Start: Enter the current market rate for rent on a per square foot basis

Mkt. Rent Growth %: Enter projected annual market rent growth rate

Contract Rent Inc. %/Yr.: Enter the projected contractual annual rent increases for 2nd and 3rd generation leases

Reimb. % of Pro Rata Reimbursable OpEx: Enter the projected expense recovery rate for 2nd and 3rd generation leases (100% = NNN, 0% = gross)

Renewal Probability: Enter the percentage probability that tenants will renew their leases at the then market rent rate

Lease Length: Enter the estimated length, in months, of future leases

Downtime: Estimate the number of months the space will be vacant between leases