Retail Real Estate Development Model (Updated Apr 2024)
I’m excited to share my Retail Development Model in Excel. This pro forma analysis tool comes as I’ve continued to build and share specialty real estate models, tailor-made for specific investment scenarios and property types.
This model is an adaptation of my Office Development Model and Industrial Development Model. It’s specifically built to analyze both merchant-build and build-to-core, ground-up retail development opportunities. Like the Office and Industrial Development Models, this model includes an expandable budgeting and development cash flow module, robust rent roll and operating cash flow module, permanent operating cash flow module, the ability to model complex partnership waterfall structures, and much more.
As with all of our real estate models – and especially those that are newly released – the tool continues to be reviewed and updated regularly, so if you have a feature request or you spot a bug, please let me know and I’ll look to include it in a subsequent version of the model.
In version 2.0 (Feb 2024), I added a Detailed Sale Price/Valuation update to the Reversion Cash Flow module. This feature allows the user to set different cap rates and NOI for different portions of the property (e.g. pads vs inline vs anchor). While the feature doesn’t yet allow the user to choose different timing of the sale of those portions, it’s an important step toward making the model more flexible to various retail development scenarios.
Are you an Accelerator member? Review the Retail Income Statement course to get the most out of this model. Not yet an Accelerator member? Consider joining the real estate financial modeling training program used by top real estate companies and elite universities to train the next generation of CRE professionals.
Adapting my Office and Industrial Development Models for Retail Development
The foundation of this Retail Development Model is my Office Development and Industrial Development Models. In fact, 95% of the inputs, calculation modules, outputs, and features in this model come directly from those models. This is possible because the techniques that are used to model office and industrial development are largely the same with retail development.
Therefore, if you’ve used my Office or Industrial Development Models, you will immediately know how to use this model. No need to watch the tutorial video(s) nor read the written tutorial below. Simply download the model and get started!
If you’re new to A.CRE and/or haven’t used our Office or Industrial Development Models, below you’ll find written instructions for getting started as well as a video walkthrough of the Retail Development Model.
Layout of the Retail Development Model
So, allow me to walk you through the layout of the Retail Development Model. This tool is structured similar to the various models I’ve built over the past few years (e.g. Apartment Development, Self Storage Acquisition, Office Development, Industrial Development, etc). Namely, I use a single Underwriting tab, wherein the vast majority of the inputs are entered, and the cash flows are modeled. Outputs/reports are then generated, and displayed on a Summary tab and Annual Cash Flow tab.
While the model contains virtually everything you’d expect from an institutional-quality model, putting all inputs on one Underwriting tab greatly simplifies the user experience. Here are the various worksheets contained within the model:
This model likely still contains errors. If you spot an error, have a feature request, or would like to make a suggestion to improve the model, please let me know.
Version Tab (Visible by default)
The model opens initially to this tab so you can see what changes have been made in the most recent version of the model. On this tab you can also find links to model tutorials, guides, support, and other information.
Underwriting Tab (Visible by default)
The Underwriting tab is where all of your primary inputs are entered. The tab is broken up into six sections, built from top to bottom. The sections can be accessed either by scrolling down to each or using the buttons along the top of the screen. The six sections are ‘Description’, ‘Development’, ‘Operations’, ‘Reversion (Sale)’, ‘Returns’, and ‘Sensitivity’. Inputs are laid out along the left, while outputs/cash flows are calculated along the right.
The Description section includes retail-specific inputs such as a retail sub-type input (e.g. auto repair, auto parts, bank, convenience store, day care/nursery, QSR/fast food, restaurant/bar, neighborhood center, community center, regional mall, super-regional mall, fashion/specialty center, power center, outlet center).
Within the Operating Cash Flow section, you will also be able to specify the tenant type, including anchor, junior anchor, inline, outparcel, and other. These retail-specific inputs can be edited on the Data tab (see below).
In the Reversion (Sale) Cash Flows section, an input was added in v2.0 allowing the user to detail the sale price/valuation for different portions of the property (e.g. pad sales vs inline/anchor). To enable this feature, toggle ‘Yes’ in the drop-down menu in cell F154.
Summary Tab (Visible by default)
While the return metrics levered IRR, levered EMx, and Development Spread are shown along the top of the Underwriting tab, the bulk of the risk and return metrics are shown/visualized on the Summary tab. The summary tab also includes various charts, a strengths/weaknesses section, a frame to include a picture/map, and a summary of the investment. The Summary tab is meant to be printed, and as such the view mode is set to Print Preview by default.
Annual Cash Flows (Hidden by default)
The Annual Cash Flows tab rolls up the monthly cash flows to annual periods, so you can view high-level cash flows on one page. The report is printable. You can access the report by pressing the ‘Show’ toggle on the Summary tab within the Property Cash Flow section.
Data Tab (Hidden by default)
Some basic backend settings are housed in a Data tab. These settings are related to the s-curve development cash flow forecasting module, date and period headers, data validation lists, and may include other settings as the model evolves.
Walkthrough of the Retail Development Model
Below you will find a video for how to use the Retail Development Model. This walk through is based on the initial release version of the model. As I continue to improve/update the model, the version you download may vary slightly from the model in this video.
Compatibility
This version of the model is only compatible with Excel 2013, Excel 2016, and Excel 365.
Download the Retail Development 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 development 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’. If you have any questions about our “Pay What You’re Able” program or why we offer our models on this basis, please reach out to either Mike or Spencer.
We regularly update the model (see version notes). Paid contributors to the model receive a new download link via email each time the model is updated.
Version Notes
v2.11
- Fixed OFFSET() formula in I166:I182; the formula was erroneously starting one month prior to Reversion End Month
v2.1
- Built ‘Other Income’ module (in addition to reimbursements)
- Added ‘Other Income’ section starting in row 122 of the Underwriting tab
- Added ‘Other Income’ lines to Annual Cash Flow stab
- Added ‘Other Income’ to Reversion Cash Flow section
- Misc. formatting updates
v2.0 (Major update)
- Misc updates to the ‘Version’ tab
- Used ‘A.CRE Code Helper for Financial Models GPT’ to add code comments in all subroutines in the following modules to help users understand code:
- Budget
- General
- Operating
- PermDebt
- Returns
- Sensitivity
- Used ‘A.CRE Code Helper for Financial Models GPT’ to improve efficiency of code
- As hundreds of code changes were made, tested each macro individually
- Expanded Reversion Cash Flow module to allow selling/valuing portions of property at different times/rates (e.g. pad sells vs inline vs anchor)
- Added Input: Detailed Sale/Valuation? (cell F154)
- Added detailed NOI (Untrended and Sale) vs detailed Cap Rate (Untrended and Sale) section (starting in K154); becomes available when ‘Detailed Sale/Valuation’ is set to ‘Yes’
- Updated Stabilized Value and Cap Rate formulas (G181:I185) based on whether ‘Detailed Sale/Valuation?’ is toggled Yes or No
- Note that currently all ‘Portion’s are set to sell in the same month; this avoids excessive complexity in the Operating Cash Flow module
- Misc. formatting updates
v1.8
- Added ‘Tenant Name’ to EP93:EP104
- Fixed ‘Sgl Promote’ and ‘Dbl Promote’ buttons
- Deleted ‘CU?’ cell reference as it’s unnecessary
- Built a ‘Rent Step’ module to allow rent growth at a frequency other than Annual
- Added a backend ‘Rent’ calculation module starting at JZ92
- Added a ‘Rent Step’ frequency input starting in cell K92
- Renamed heading in cell G92 to ‘Rent Growth %’
- If the ‘Rent Growth %’ is set to 10% and the ‘Rent Steps’ is set to ‘Every 5 Years’, rent will increase 10% every 5 years for that tenant.
- Updated various placeholder values
- Misc. formatting updates
v1.71
- Fixed issue where Add/Delete OpEx line item logic was not adding new OpEx lines to Reversion CF section
v1.7
- Deleted ‘Leaseup_Method’ cell reference as it’s unnecessary
- Deleted Conditional Formatting in ‘Leased %’ row that is unnecessary
- Modified Add/Delete line item button formatting
- Created Add/Delete OpEx line item tool
- Includes two new buttons: Add OpEx and Delete OpEx
- Only adds/deletes new OpEx beyond the standard four (CAM, Management, Insurance, Property Tax)
- Wrote four new subroutines (i.e. Macros): Add_OpEx, Delete_OpEx, Add_OpEx_Annual and Delete_OpEx_Annual to perform add and delete
- Minor changes to the Annual Cash Flow tab to support adding the new lines on that worksheet as well
- Updated various placeholder values
- Misc. formatting updates
v1.6
- Created macro to update Pivot Table on Tenant Report
- Used OpenAI’s GPT-4 to write the macro
- Added the macro to the Recalculate macro
- Added button to the Tenant Report to run the macro
v1.5
- Created a Residual Land Value tool
- Used OpenAI’s GPT-3 to write the macro
- Created named cells for Development Spread and Land Cost
- Added button to the Summary tab to run the tool
- Added note to ‘Building Coverage’ label to clarify that it assumes all retail buildings are one-story
- Widened Summary section for improved formatting
- Updated ‘Best Practices’ link text on Version tab
- Updated various placeholder values
v1.4
- Removed the erroneous dropdown menu in cell I16 of the Underwriting tab
- Minor cleanup to Named Range list
- Added a Tenant Analysis Report
- Created a Macro to turn the report on and a Macro to turn the report off
- Added new Show/Hide buttons on the Summary tab (immediately below image)
- Built a basic tenant analysis summary report; will improve the report in future versions of the model
- Misc. updates to placeholder values
v1.3
- Added AUM Fee module added to ‘Partnership Cash Flow’ module
- New inputs added to row 290 of the Underwriting tab: AUM Fee type, AUM Fee %, and AUM Fee start month
- Renamed the ‘Developer Fee to GP’ label to ‘Developer Fee to GP + AUM Fee’ (ET213)
- Updated Levered Cash Flow line available to distribute to partners to reduce by the amount of AUM fee (starting at ET198 on the Underwriting tab)
- Updated the formula logic in row 213 (starting at ET213) to include the AUM fee to GP
v1.2
- Fixed heading on Equity/Debt column chart
v1.1
- Added ‘Exit Mo:’ output in top quick returns row
- Deleted error outputs on Summary tab (leftover from Office Development Model)
- Misc. updates to placeholder values
- Added ‘Project Cost’ output to Reversion Cash Flow section (F183) for quick comparison
- Fixed issue where Construction Debt, when PermDebt module is turned off, would pay off prior to analysis end
v1.0
- Adapted our Office Development Model to be used for Retail Development
- Initial release
