, , , , , , , , , , , ,

All-in-One Underwriting Model for Real Estate Development and Acquisition (Updated 5.20.17)

As many of you recall, about a year and a half ago I set out to build an Excel alternative to the widely-used (and now discontinued) ARGUS DCF. With career moves and a lot of other A.CRE-related side projects, this undertaking has been slow going. Alas, today I’ve decided to release the first beta-version of the model – my All-in-One Underwriting Tool for Real Estate Development and Acquisition in Excel (catchy name, I know!).

Download the Model | Guide to Getting Started | Video Intro | Version Notes

First a Note About The Limitations of Microsoft Excel in Modeling Real Estate

I think its important to point out upfront, this underwriting model is not a surefire replacement for your non-Excel valuation/underwriting solutions. In fact, it’s likely you (or your employer) chose to use another tool in large part because of the limitations of Excel. And Excel certainly has its limitations!

Take for example modeling expense reimbursement. It would be virtually impossible to model in Excel (or in any other tool for that matter) the almost infinite variations of expense reimbursement arrangements in retail, office, and industrial leases. Other solutions likewise come up short in this area, but come a lot closer than any Excel model ever could. So in this Excel-based All-in-One Underwriting Tool, I use an annual expense recovery by tenant method to calculate expense reimbursement rather than attempting to model every possible scenario. Less precise? Sure. Good enough? In my experience, the buy/sell decision/outcome does not hinge on what the exact amount of reimbursement income a property might throwoff is – a figure, by the way, no software can predict with 100% certainty.

So it really comes down to this. Excel is inexpensive, widely used, and offers customization on the fly but lacks the super-precision of its non-Excel counterparts. It’s up to you to perform your own cost-benefit analysis to decide which tool best suits your purpose.

Get Notified When We Publish New Content

So, What’s in the Model?

Download the Model | Guide to Getting Started | Video Intro | Version Notes

With all that talk about limitations in Excel, you’d think I’m setting you up for just another back-of-the-envelope acquisition model. But fear not. This is like no model I’ve built before. I set out to build a model that does it all and right now, its pretty dang close! Acquisition, value-add/redevelopment, ground-up development, industrial, office, retail, multifamily, various partnership structures, advanced underwriting modules, tabs that automatically hide/unhide depending on the selections you make, and the list goes on.

Plus I’ll be adding additional modules as I find time. For example I recently added a robust multifamily module and in the future will added functionality for mixed-use valuations of any combination of retail, office, industrial, and multifamily uses that you could think of. I also plan to continue to add property-specific analysis sections for each property type using modules we’ve shared on A.CRE.

So let’s have a look at what is included in the model. First, download the model and open it up alongside your browser. Then, read through the ‘Getting Started‘ sections below to get a basic understanding of how the model works. Finally watch the ‘Video Introduction‘ at the end of this post. I’ll be following this post up with model updates and additional training videos and posts.

Friendly Request: If you find errors or have suggestions for how I can improve this model, please drop me a note in the box at the end of this post and I’ll work to include your suggestions in future releases of the model.

Download The 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 models sell for $100 - $300 each). 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.

Note (especially for Gmail users): The model is sent via email and occasionally is blocked by spam filters. If you don't see the email arrive within five minutes, check your spam folder.

We’re regularly updating the model (see version notes). Paid contributors to the model receive a new download link via email each time the model is updated.

A.CRE All-In-One Underwriting Model (Beta version v0.4.0)

  • The most comprehensive real estate Excel model on www.AdventuresinCRE.com
  • Model acquisition, value-add, redevelopment, or development scenarios
  • Currently built specifically for Office, Retail, Industrial, and Multifamily property types
  • Partnership return module with three-tier waterfall using either IRR or equity multiple hurdles
  • Robust development module with detailed cash flow forecasting using linear, s-curve, or manual input
 
   
By clicking Continue, I agree to the following Terms & Conditions.
This Excel file (the "software") is distributed as is, completely without warranty or service support. www.AdventuresinCRE.com and its authors are not liable for the condition or performance of the software.
www.AdventuresinCRE.com owns the copyright and grants users a perpetual, irrevocable, worldwide, non-exclusive, royalty-free license with respect to the software as set forth below.

www.AdventuresinCRE.com and its authors hereby disclaim all implied warranties. www.AdventuresinCRE.com grants the users the right to modify, copy, and redistribute the software and documentation, both within the user's organization and externally, subject to the following restrictions:

1. The users agree not to charge others for use of the software, regardless of whether users have altered and/or improved the software in anyway
2. In any use of the software, the users agree to acknowledge the www.AdventuresinCRE.com authors that developed the software.
3. The users agree to obey all government restrictions governing redistribution or export of the software.
4. The users agree to reproduce any copyright notice which appears on the software and documentation on any copy or modification of such made available to others.

Guide to Getting Started

Download the Model | Guide to Getting Started | Video Intro | Version Notes

Summary Tab

a screenshot of the summary page of the Adventures in CRE all-in-one underwriting model for real estate development and acquisition

The most comprehensive model shared on A.CRE to date.

*Quick tip: as always the blue font cells are input cells while all others are output cells. However, orange font cells are also used here which are optional input cells with default formulas*

When you open the model, you’ll begin at the ‘Summary’ page. In this tab, you’ll drop in the physical attributes of the property, choose whether to use or not use various modules, enter the project timing and analysis period, and input your valuation assumptions. The summary tab reports a few key risk and return metrics including annual rollover, debt coverage, debt yield, internal rate of return, equity multiple, average cash-on-cash return, among others. The summary page also includes a basic sources and uses table.

To see the rest of the tabs in the model, you’ll need to turn on every module. To do this, in the Summary tab first go to the ‘Include Modules?’ section and set Permanent Financing Module to ‘Yes’. Next, move to the ‘Navigation’ module and set each to ‘Show’. Finally, make the Construction Length under Timing greater than 0 months to turn on the development tab.

Beta version 0.3 includes a Retail Analysis module that can be seen by changing the property type to ‘Retail.’ In subsequent versions, I’ll be adding additional property-type specific modules that will hide/unhide those tabs depending on which property type you select.

In beta version 0.3.3 I added a Residual Land Value module, which can be turned on under the Include Modules? section. This module is only available if the investment is a development/redevelopment project (e.g. Development Length in cell M13 of the Summary tab is greater than 0). To learn more about this module, read and watch the Residual Land Value Calculation Module Walkthrough.

In beta version 0.3.4 I added a ‘Rate Matrix’ concept to determine the base cap rate and base discount rate used for valuation purposes. You can learn more about this module by reading and watching the Rate Matrix Module walkthrough.

Beta version 0.4.0 includes the newly released Multifamily module.

Perm. Debt tab (see Permanent Debt walkthrough)

Model up to two tiers of debt.

With the ‘Permanent Financing Module’ toggle on the Summary tab set to ‘Yes’, the Perm. Debt tab will be visible. In this tab, you’ll add assumptions for up to two tiers of permanent debt. If the Summary tab includes a construction period (Summary!M10>0 months), then the permanent debt will fund in the stabilization month (Summary!K14) and the construction debt module will be activated. Otherwise, permanent debt will fund in time 0 of the analysis.

All inputs for this tab are entered in column E of the Perm. Debt tab. Assumptions include loan amount, loan fees, fixed interest rate (if variable, enter the average expected rate over the term), amortization period in months, and the number of months the payment is interest-only. Upon entering the inputs, you can see the loan-to-value of both the senior piece (cell D11) and the combined senior plus secondary debt (cell D22) pieces.

In addition to loan-to-value, other outputs on this tab include a detailed amortization schedule for both the senior and secondary loans, loan-to-cost for the senior and the combined loans, amortization and interest-only monthly payments, loan payoff at maturity, among others.

Reports and Analysis Tabs

The number of Report and Analysis tabs will continue to grow as I find time to add more.

As of beta version 0.3, the model includes two report tabs (Equity CF and Property CF) plus one retail analysis tab (tenant rollover, retail sales, occupancy cost, % of NRA, etc). I fully expect to continue to add analysis tabs as I find time and per your suggestions.

The Equity CF tab includes a few minor inputs related to the partnership structure and promote. It is here that you’ll spell out the equity contribution percentage between the sponsor group and the investor group or limited partner(s). Use cell D7 to change between IRR and equity multiple hurdles and cells D18:F20 to detail out the promote. This tab is an adaptation of my equity waterfall model.

The Property CF tab includes two basic inputs: whether to cap net operating income or cash flow from operations in determining the residual (terminal) value and the selling costs percentage. Otherwise, the Property CF tab is purely to report the projected annual and monthly cash flow of the investment. The annual cash flow is shown at the top. The monthly cash flow is below the annual cash flow and is defaulted as hidden. To reveal the monthly cash flow, click the + sign located left of row 88.

Operation Section Tabs

The meat of your underwriting takes place in the Operation Section

Sections are distinguished by their unique color; the first tab in each new section is an empty tab colored slightly darker than its counterparts in the same section and labeled with —> at the end, such as the ‘ORI—>’ tab. The Operation section is the first section after the Reports and Analysis tabs as you move along the tab ribbon from left to right and includes an ORI (Office/Retail/Industrial) section and an MF (Multifamily) section. To reveal the operation section you intend to work with, turn on the Operation Module (either ORI or MF) in cells F29 or F30 of the Summary tab then set cell F36 or F37 to ‘Show’.

The Operation section contains the meat of the underwriting. This section currently is only compatible with industrial, office, retail, and multifamily property types but will be expanded to include other property types such as student housing, senior housing, self-storage, and manufactured housing.

This section includes the ‘ORI-Settings’ and ‘MF-Settings’ tabs where the user can change property type specific settings and nomenclature; the ‘ORI-RR’ and ‘MF-RR’ tabs where the user enters tenant/unit specific inputs; and the ‘ORI-OpSt’ and ‘MF-OpSt’ tabs where the historical operating statements, stabilized pro forma, and residual pro forma are modeled.

The ‘RR’ and the ‘OpSt’ tabs are essential to properly using this model and it is here where you’ll spend the bulk of your time. I’ll follow up this post with videos specific to properly completing these tabs, as there’s not time to go too deep into specifics here. Check out tutorials and walkthroughs of the model.

One wrinkle introduced on the ‘OpSt’ tabs is the orange font cell. These are cells that have default calculations but require the user to review the calculation and adjust the cell accordingly. For instance, cell I7 of the ‘ORI-OpSt’ outputs the”Stabilized Year” and then is used by the model to calculate the stabilized value. There may be instances when you’ll want to change the stabilized year to some other value, in which case you’d manually change cell I7 (an orange font cell) to the year of your choosing.

Development Section Tabs

The Development Section immediately follows the Operation section as you move along the tab ribbon from left to right. To turn this section/module on, be sure the Construction Length cell on the summary tab (Summary!M10) is greater than zero. This development section includes a Sources and Uses tab, a Budget tab, and a Gantt chart tab. Only the Sources and Uses and Budget tabs have inputs, with the majority of the inputs coming on the Budget tab.

The development section is a variation of my Construction Draw and Interest Calculation Model. In building that model, I recorded a video tutorial that is likewise relevant here:

Tip: If the development module is turned on, no ‘Acquisition Price’ is set and therefore the sum of the items in the ‘Budget’ tab will be the total cost of acquiring the investment. So, if your project is a value-add investment, you will need to include the cost of purchasing the property in the budget tab plus include the cost of renovation and lease-up. Also note, that the construction loan assumes an interest-only loan through to stabilization after which the parameters set in the Perm. Debt tab are used.

Calculation Tabs

Grey colored tabs are calculation tabs, and are hidden by default unless set to show (cell F35 in the Summary tab). It is in these tabs that the more complex calculations are undertaken. Especially of note is the ‘O-Calc’ tab. It is here that lease cash flows are computed such as future leasing assumptions, expense reimbursement, rollover and leasing costs, contractual and market rent increases, etc.

If you’re to truly become proficient at using this model for your own purposes, I’d suggest digging into each of the calculation tabs to better understand the methodology behind the model.

Video Introduction

Download the Model | Guide to Getting Started | Video Intro | Version Notes

As a companion to the above ‘Getting Started’, I thought I’d record a video introduction of the model. This will stand as the first in a coming series of instructional videos on how-to use the All-in-One Underwriting Model.

Provide Feedback

Alert us to an Error or Make a Suggestion

6 + 2 = ?

About the Author: Born and raised in the Northwest United States, Spencer Burton has over 14 years of real estate investment and development experience. In his current position, Spencer assesses new investments for a $40bn real estate fund. He resides in Dallas, TX.


Version Notes

Download the Model | Guide to Getting Started | Video Intro | Version Notes

Version 0.4.0 (May 20, 2017) – Fully functional MF module!

  • Fixed error in how Downtime Vacancy / Loss to Market was calculated on the ORI-OpSt tab
  • Added an “Total Actual Income” section on the ORI-Calc tab to sum Contract/Spec Income and Renewal Income totals
  • Multifamily Module Progress:
    • Changed logic for turning on ORI and MF modules such that if ORI Module = On, then MF Module = Off and vice versa
    • Added MF property-level cash flows to Property CF report tab
    • MF cash flows now flowing through to return tabs (Equity CF, Summary)
    • Multifamily Module is fully functional, although the ORI and MF modules can not be on at the same time

Important: The MF module is functioning but has not yet been fully tested for errors

Version 0.3.7 (May 13, 2017)

  • Added MF cap rate and discount rate; Rate Matrix ONLY applicable to Office, Retail, and Industrial property type (for now)
  • Fixed issue where Variable Income/Expenses were not calculating currently in unstabilized periods
  • Multifamily Module Progress:
    • Modeled Loss-to-Lease on MF-Calc tab
    • Modeled untrended Make Ready costs on MF-Calc tab
    • Added Free Rent on 2nd Generation Leases input to allow for stabilized free rent (MF-RR tab)
    • Modeled lease-up free rent and stabilized free rent on MF-Calc tab
    • Modeled stabilized downtime
    • Linked cash flows from MF-Calc to MF-OpSt tab
    • Completed MF-OpSt tab
    • Completed MF Operation Section (MF Property-Level Cash Flows)

Important: The MF module is still under construction. Multifamily property-level cash flows are completed and can be seen in the MF-OpSt tab. However, MF cash flows do not flow up to the model level. This means you will not see MF cash flows reflected on the Summary tab or in the Report tabs.

Version 0.3.6 (April 29, 2017)

  • Corrected error in Sources and Uses table on Summary tab
  • Added conditional formatting where, if only one partner exists, Equity CF and Partnership Returns sections are turned off
  • Dev. calc tabs no longer appear when Calculation Tabs are set to “Show” unless development module is on
  • Multifamily Module Progress:
    • Completed MF-RR
    • Modeled market rent on MF-Calc tab
    • Modeled lease-up (“Units Occupied”) on MF-Calc tab
    • Modeled Actual Rent per Unit and Actual Rent om MF-Calc tab

Important: The MF module is still under construction. No multifamily calculations are made in this version.

Version 0.3.5 (April 15, 2017)

  • Added fallback cap rate and discount rate in the event Rate Matrix is not properly modeled/setup
  • Fixed a minor calculation error in row 20 of the Dev-Interest Calc tab
  • Improved Rate Matrix table to allow adding and deleting locations and property types
  • Added MF—>, MF-Settings, MF-RR, and MF-OpSt tabs to facilitate the creation of the Multifamily module
  • Added MF Module and MF tabs lines to ‘Include Modules?’ and ‘Navigation’ sections of Summary tab
  • Renamed Operation tabs to ORI (Office/Retail/Industrial) so as to be used for office, retail and industrial properties only
  • Began modeling MF calculations

Important: The MF module is still under construction. No multifamily calculations are made in this version.

Version 0.3.4 (April 8, 2017)

  • Added functionality to make Other Income and Operating Expenses variable to occupancy; see column I on O-OpSt tab
  • Added MSA input on Summary tab for use in developing cap rate and discount rate matrix
  • Added Rate Matrix tab, linked property type, MSA, Base Cap Rate, Base Discount Rate to Rate Matrix tab
  • Important: the rates used in the template ARE NOT accurate; set your own rate assumptions on the ‘Rate Matrix’ tab

Version 0.3.3 (April 1, 2017)

  • Changed label for 1st generation leasing costs from ‘Leasing Assumption’ to ‘Unpaid Leasing Cost (1st Gen. Lease)’
    Corrected Analysis Period heading
    Added residual land value analysis (see Summary tab) – only available in development mode

Version 0.3.2 (March 25 2017)

  • Changed monthly header date function on S-Curve Calc tab from EDATE() to EOMONTH()
  • Set Development Budget Start Date (Budget tab) to Analysis Start Date
  • Fixed error in development module where residual value wasn’t calculating correctly
  • Converted residual pro forma on O-OpSt tab to blue font (input cells), default equal to residual year
  • Revised timing section, added residual year, changed analysis period assumption from months to years
  • Extended calculation out to 11 years to allow for year 11 residual calculation
  • Added multi-year market rent growth assumption (see Future Leasing Section of O-RR tab for input)
  • Added development yield/returns section to summary tab (cells O22:U23)
  • Fixed issue where cap rate inputs on Summary tab weren’t available in development module

Version 0.3.12 (March 17, 2017)

  • Fixed an error where 1st generation detailed rent wasn’t calculating correctly in last year of lease
  • Removed drop-down menu in cell Summary!F30
  • Changed the return of capital “Error” message on the Equity CF to “Insufficient Cash Flow”

Version 0.3.1 (March 11, 2017)

  • Added version notes section
  • Misc. formatting fixes
  • Added link to model documentation online
  • Added data validation to construction length on Budget tab
  • Changed label: Construction to Development in Timing on Summary tab
  • Made Development start date (Summary M11) equal to Analysis start date
  • Fixed error where 1st generation lease income wasn’t calculating correctly in development mode
  • Added Development Yield to return metrics