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.
In an effort to provide greater instruction on how to use our All-in-One Underwriting Tool for Real Estate Development and Acquisition, we’re developing a series of walkthrough videos and posts on the methodology behind the various components of the model. Our hope is that if you are empowered with the how, you’ll be more willing/able to provide feedback to improve the model.
If you haven’t already, you can download the model here. This walkthrough uses beta version 0.3.12 of the All-in-One Model.
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.
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