As many of you recall, in 2015 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, in 2016 I released the first beta-version of the model – my All-in-One (Ai1) Underwriting Tool for Real Estate Development and Acquisition in Excel (catchy name, I know!).
Since that release, I’ve continued to update the model regularly, correcting errors and adding features you, the user, are asking for. The model has been downloaded thousands of times and hundreds of you are actively contributing to continuous and substantive improvements. As I find time and as your suggestions come in, I’ll continue to update the model with the hope that one day, it might have its permanent place in commercial real estate underwriting.
Get Notified When We Publish New Content
So, What’s in the Model?
With all the 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, apartment, student housing, seniors housing, 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 (contribute your ideas in the model’s support forum). For example I recently added a robust multifamily module capable of modeling apartment, student housing, and seniors housing deals. 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, have suggestions for how I can improve this model, or have a question about the model, please drop me a note in the All-in-One’s Support Forum and I’ll get back to you as soon as possible.
This version of the All-in-One is only compatible with Excel 2013, Excel 2016, and Excel 365 (see version compatibility).
Download the Ai1 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 – similar real estate DCF models sell for $300 – $1,000+ 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.
Guide to Getting Started
Excel Version Compatibility
- Excel 365 (Compatible)
- Excel 2016 for PC (Compatible)
- Excel 2013 for PC (Compatible)
- Excel 2010 for PC (?)
- Excel 2007 for PC (Incompatible)
- Excel for MAC (?)
If you attempt to open the file with an incompatible version of Excel, an error box will appear stating the file is corrupt.
*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 (apartment, student housing, seniors housing). Toggle on or off the multifamily module in the Include Modules? section by setting the ORI (Office/Retail/Industrial) Module on or off. When the ORI Module is on, the MF Module by default is off and vice versa. Both modules can not be on at the same time.
In beta version 0.4.3, I added a Ground Lease Valuation module. This module gives the user the option to place the ground lease payments below NOI, and reduce the stabilized and residual value of the investment by the present value of the ground lease.
Beta version 0.5.0 adds additional functionality for modeling Student Housing and Seniors Housing investments. No changes were made to the base methodology of the model in modeling these property types, however nomenclature specific to these property types and labels were updated. Also, in the case of Student Housing, ‘Units’ are changed to ‘Beds’ to reflect that leases/rents are per bed, not per unit with this property type.
Beta version 0.5.2 adds a Tenancy Analysis Report for office, retail, and industrial underwriting. The report uses a fully dynamic column chart, an auto-updating PivotTable, and various other sections to help the user analyze the strengths and weaknesses of the rent roll.
In beta version 0.5.3, a drop-down menu on the Summary tab (see lower right-hand corner) was added to turn on/off ‘Print Mode’. Print mode turns all non-black font black to improve the visual appeal of printed reports. An IRR Matrix Report was also added to beta v0.5.3.
In beta version 0.5.4, an After-Tax (Unlevered) Analysis tab was added to the Report section. This tool can be turned on by toggling the drop-down labeled ‘After-Tax Analysis’ under the Include Modules? section of the Summary tab. The After-Tax Analysis module is a duplicate of the After-Tax Cash Flow Model version 1.12 that I built last year. Read how the module is built and watch a video of how to use the After-Tax Analysis Module including what assumptions to model.
Perm. Debt tab (see Permanent Debt walkthrough)
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.
In beta version 0.5.6, an estimated ‘Lender Reserve’ calculation was added to the Perm. Debt tab. Using this tool, the user can estimate lender reserves for debt service, capital expenditures, insurance, and property taxes. The reserve is based on a user-entered number of months (cell D21) and underwritten values for the reserve items.
In beta version 0.7, we added the option to refinance the initial Senior Loan mid-hold. The model now models ‘Senior Debt (Initial)’ as well as a ‘Senior Debt (Refinance)’ permanent loans. If the ‘Payoff Month’ for the ‘Senior Debt (Initial)’ loan (Perm. Debt tab cell E17) is less than the length of the hold period in months (Summary tab cell K6), the assumptions for the ‘Senior Debt (Refinance)’ appear. The second senior loan funds in the same month the initial senior loan pays off, and then is assumed to pay off at the analysis end date.
In beta version 0.71, we added the option to use fixed or variable interest rate debt. Use the drop-down menu for Fixed Interest Rate or Variable Interest Rate under each debt section, and then edit the blue ‘Interest Rate’ row along the right-hand to forecast the interest rate by month.
Reports and Analysis Tabs
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 Equity CF tab models the waterfall both on an annual basis as well as on a monthly basis.
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.
The optional Sponsor CF tab (activate on the Summary tab under ‘Include Modules?’) allows the user to model a second waterfall (double promote) for situations where the real estate investor raises both LP equity and sponsor equity. In this scenario, the LP would promote the sponsor, and then the equity investors in the sponsor would promote the GP for hitting certain IRR or equity multiple hurdles. Assumptions on the Sponsor CF tab are similar to that of the Equity CF tab. The Sponsor CF tab models the 2nd waterfall both on an annual basis as well as on a monthly basis.
Other report tabs include the Retail Analysis Report, Tenancy Analysis Report, and the IRR Matrix Report.
Operation Section Tabs (MF-OpSt and ORI-OpSt)
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.
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.
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.
Note that this video introduction is based off of an earlier version of the model – many changes have since been made. I’ll get an updated video up in the near future.
Provide Feedback, Get Your Questions Answered
We’ve launched a tool exclusively dedicated to answering questions and taking requests related to this model. Rather than doing so one-on-one via email, we felt it would better serve the entire A.CRE community if questions and requests related to this model were handled for all to read and learn from. So if you have a question, request, or comment, please head over to the Support Forum and drop us a line. We’ll work to get back to you as soon as our schedules’ permit.
About the Author: Born and raised in the Northwest United States, Spencer Burton has over 15 years of real estate investment and development experience. In his current position, Spencer assesses new acquisition, development, and debt opportunities for a $45bn real estate fund. He resides in Dallas, TX.
Version 0.7.4 (Sep 2, 2019)
- Minor fix to header on 1st Generation Rent in ORI-Calc tab
- Minor fix to Concessions and Leasing Commissions sections of ORI-Calc tab
- Cleanup minor code on ORI-Calc
- Updated version tab
- Misc. formatting improvements
Version 0.7.3 (May 17, 2019)
- Fixed issue where ‘Roll to Market’ feature on the MF-RR tab wasn’t working properly
- Fixed error in Senior Refinance Debt and Junior Debt payoff amount calculation
- Removed option on ORI tab to use Annual $ or Monthly $ rent inputs
- Added rent total to ORI-RR tab
- Added option to toggle between square feet and square meters (see cell F5 of the Summary tab)
- Added drop-down menu to the Summary tab to choose between square feet (SF) and square meters (M2)
- Updated SF labels throughout to read M2 when using meters rather than SF
- Updated various logic statements as necessary
- Updated conditional formatting related to SF reference
- Misc. formatting enhancements and fixes
Version 0.7.2 (Mar 26, 2019)
- Added Sponsor Fees assumption (Equity CF F43) in time zero to accommodate fees paid to Sponsor at venture closing
- Fixed error with unlevered IRR calc where construction interest was being included in net unlevered cash flow
- Updated ‘Error Check’ formula on Property CF tab to round to nearest whole number (i.e. ignore for rounding errors)
Version 0.7.1 (Feb 24, 2019)
- Added option for variable rate on permanent debt (see Perm. Debt tab cells B14, B31, B42)
- Added option for variable rate on construction debt; (see S&U cells B17 and Z27:CG27)
- Fixed NRA calculation in cell Q5 of Property CF tab
- Added GBA (gross building area) input to cell F13 of the Summary tab
- Moved parking ratio output from cell F13 to cell D13 of the Summary tab
- Fixed issue where ORI 1st gen. contract rent wasn’t bumping in correct month
- Added ability to model contract options to renew (up to two) in ORI module (no option to vacate yet)
- Added assumption (see columns K, L of ORI-RR tab) for lease extension months (assumes tenant exercises option to renew)
- Modeling leasing costs for contract renewal options (ORI-Calc tab)
- Misc. formatting fixes and enhancements
Version 0.701 (Jan 9, 2019)
- Fixed issue where first line of hard construction costs on Budget tab were incorrectly calculating
- Fixed same issue as above, only related to construction interest
Version 0.7.0 (Dec 30, 2018)
- Added option to refinance Senior debt mid-hold period (Perm. Debt Tab)
- Changed name of ‘Senior Debt’ to ‘Senior Debt (Initial)’
- Added ‘Funding Month’ and ‘Payoff Month’ input to Senior Debt assumptions
- Added inputs for ‘Senior Debt (Refinance)’ that match ‘Senior Debt (Initial)
- Modeled ‘Senior Debt (Refinance)’ on Perm. Debt tab
- Adjusted Total Permanent Financing rollup on Perm. Debt tab to include Refinance component
- Adjusted ‘Permanent Financing Fees’ line on Property CF tab to account for fees from refinance
- Fixed issue where ‘Junior/Mezz Debt’ fees were not properly flowing into Property CF
- Fixed issue where reimbursement for Future Leasing Assumptions was not calculating correctly
- Improved precision of expense reimbursement: transition from 1st gen. to future gen. happens immediately (rather than in following year)
- Misc. formatting updates and improvements
- Updated compatibility list
Version 0.6.61 (Dec 10, 2018)
- Removed Volatile formula in ‘Distribution to Sponsor’ lines of Equity CF and Sponsor CF tabs
- Cleaned up ‘Promote Structure’ notes to help make modeling sponsor promote it more clear
Version 0.6.6 (Dec 5, 2018)
- Cleaned up various outdated formulas on the ORI-Calc tab
- Cleaned up various outdated formulas on the ORI-Rent Roll tab
- Added Self Storage as Property Type option
- Added Self Storage to Rate Matrix and
- Added Self Storage to Property Type drop-down list on Summary tab
- Added width x length input on MR-RR for Self Storage
- Updated Operating Statement with Self Storage inputs
- Added link on Version tab for guide to ‘Best Practices’ to help when using real estate financial model templates such as this one
- Misc. formatting changes
- Added “Junior/Mezz Loan Funding Month” input on ‘Perm. Debt’ tab; allow Junior/Mezz loan to fund in different month than Perm loan
Version 0.6.5 (September 29th, 2018)
- Updated print ranges
- Fixed issue where construction loan remaining wasn’t displaying correctly on the Dev-Interest Calc tab (row 24)
- Improved Run Const Calc button on the S&U tab
- Combined Interest Reserve and Const Loan before Interest Reserve into one ‘Construction Loan’ line item under Sources on the S&U tab
- Added option (S&U cell C18) to use only a portion of Lease-Up Income to cover operating shortfall and construction interest
- Added Alert on Summary tab when Const. Loan Macro needs to be run
- Inserted a new column (V) into the S&U tab that calculates what % that item is relative to the total
- Fixed issue where on the ORI-OpSt tab, the stabilized year was using inflated expense recovery income but uninflated operating expenses
- Misc. formatting changes
Version 0.6.4 (August 25th, 2018)
- Added Reno-Sparks MSA to Rate Matrix Tab
- Removed CapEx from operating shortfall; avoids double counting unpaid TIs and LCs added to ORI-RR tab when development module is on
- Added a default debt amount in cell E12 of the Perm. Debt tab
- Added optional input to ORI-RR to allow user to set each tenant’s pro rata share of operating expenses
- Changed heading in ORI-RR AI10 to ‘Recovery % (of Pro Rata Share of Opex)’
- Modeled ORI rent bumps to occur on the anniversary of the lease start date for 1st, 2nd, and 3rd generation tenants
- Misc. formatting fixes
- Created new tutorial: Understanding Treatment of Time 0
Version 0.6.32 (June 24, 2018)
- Certain construction financing costs were still flowing through to the Property CF, even when Construction Module was off; fixed this issue
- Fix issue where construction loan fees were not properly accounted for on S&U tab
- Updated monthly Debt Yield and DSCR formula on Property CF tab to return “N/A” when loan amount is 0
- Updated month/date header on the Equity CF tab
- Cleaned up Version list
Version 0.6.3 (June 15, 2018)
- Fixed an issue where development related financing costs were flowing through to DCF, even when Development Module was turned off
- Begun 25 deal in-depth testing period (no new changes to be added during testing period)
- Fixed error in construction loan macro
- Fixed error where waterfall was no longer dynamic to analysis period length
Version 0.6.22 (May 24, 2018)
- Fixed issue with construction loan macro
Version 0.6.21 (May 23, 2018)
- Added ‘Waterfall Tabs’ as Navigation option; toggle now hides/unhides Waterfall tabs (Equity CF and Sponsor CF)
- Made changes to Property CF tab to allow exit prior to stabilization
- Added Sponsor CF (Double Promote) summary metrics to the Summary tab
- Renamed Stabilized Cost to Stabilized Value on Summary tab
- Changed Development Cost in YOC calculation to be Total Costs net of lease-up income
- Set Vacancy assumption in Residual Pro Forma to an optional input (orange font)
- Changed logic for how MF roll-to-market functions for improved accuracy when Operation Begin < Construction End
- Added Lender/Financing Fees below Construction Interest – modeled straight-line; removed construction fee assumption on S&U tab
- Now charging interest on accrued interest during construction period
- Added option to include Sponsor Asset Management Fee to waterfall (see Equity CF tab, rows 43 and 136)
- Revised construction loan amount macro (credit to Lewis Hedley) to improve accuracy and correct an error
- Renamed Growth Begin Date to Growth Begin Month; set the default Growth Begin Month to month 13
Version 0.6.1 (February 14, 2018)
- Fixed error where MF renewal probability was calculated as the inverse for downtime purposes
- Expense and income growth now held flat prior to growth begin date
- MF-OpSt – Set OpEx and Other Income growth to commence at ‘Growth Begin Date’, not at Operation Begin Date
- Added ‘Growth Year’ (row 52) to track growth based on ‘Growth Begin Date’
- Updated formulas in rows 61-93 to refer to ‘Growth Year’ row for increases
- ORI-OpSt – Set OpEx and Other Income growth to commence at ‘Growth Begin Date’, not at Operation Begin Date
- Added ‘Growth Year’ (row 51) to track growth based on ‘Growth Begin Date’
- Updated formulas in rows 64-92 to refer to ‘Growth Year’ row for increases
- Moved ‘After-Tax’ analysis tab to be together with other report tabs
- Added option to view Untrended Pro Forma on OpSt tabs
- Use drop-down in cell J5 of the MF-OpSt and ORI-OpSt tabs to toggle between Untrended and Trended
- Toggling to ‘Untrended’ changes ‘Growth Begin Month’ to a month beyond the analysis year, thus removing growth from analysis
- Updated ORI-OpSt Reversion Year Pro Forma optional input formulas to consider ‘Untrended’ vs ‘Trended’
- Fixed error in Expense Recovery formula (ORI-OpSt U9) in reversion year of ORI-OpSt tab
Version 0.5.9 (December 9, 2017)
- Fixed error where renewal income was only modeling for period between 1st and 2nd generation leases, not between 2nd and 3rd
- Removed reference to Sponsor’s capital account on Equity CF and Sponsor CF tabs
- Converted currency amount from $ to none on ORI-RR tab to accommodate non-USD currencies
- Fixed error where ORI market rent was based on 1st generation base rent, not on Future Leasing Assumption Market Rent
Version 0.5.8 (November 23, 2017)
- Fixed an issue where the stabilization date formulas were not dynamic to analysis period length, causing the entire model to error out
- Fixed issue where ‘Initial Investment’ table was not displaying Sources and Uses correctly when Dev. Module=On & Perm. Fin module=off
- Corrected NRA calculation for student housing
Version 0.5.701 (November 8, 2017)
- Fixed an issue where, 1st generation free rent was modeling one month of free rent regardless of the user’s input
- Corrected a minor conditional formatting issue on the Summary tab where portion of development returns were hidden when the waterfall model was turned off
Version 0.5.7 (November 7, 2017)
- Fixed issue where totals on MF-RR were not always calculating correctly
- Changed name of ‘Sources and Uses’ tab to ‘S&U’ to reduce tab size
- Changed name of ‘Development—>’ tab to ‘Dev—>’ to reduce tab size
- Added detailed ‘Sources’ and ‘Uses’ by period on the S&U tab for illustration purposes (these previously were only found on a Calc tab)
- Changed Sponsor ‘Distribution as %’ on Equity CF tab from black font (calculation) to orange font (optional input) for alternative promotes
- Fixed error in Add Budget Item macro where additional ‘7. Other’ budget line items were not adding correctly
- Formula in cell J34 (Management Fee) of the MF-OpSt tab had been erased; re-wrote the formula
- Added Development Cost vs Stabilized Cost comparison to the Summary tab (cells O25:U26)
- Changed ‘Valuation Details’ heading on Summary tab to ‘Valuation Assumptions’ to more accurately define the section
- Relabeled ‘Interest Reserve’ in Sources graph on S&U tab to ‘Interest Reserve + ‘Lease-up Income’
- Fixed error in MF growth calculation where if Growth Begin date was later than 35 months, the model would error out
- MF 1st generation concessions were not calculating correctly; revamped MF concessions calculation
- Fixed issue there downtime was not being charged to first year of operations
Version 0.5.6 (November 4, 2017)
- Updated ‘Development Cost’ formula on Perm. Debt tab to include construction interest
- IO Payments calculation is hidden when Perm. Debt IO period is set to 0
- Management Fee on MF Module is now modeled as a % of EGI, rather than a hard-coded value
- Corrected color scheme on both Sources and Uses tab and Gantt tab to match cost categories to colors
- Added % Vacant label to OpSt tabs to make Vacancy assumption more clear
- Changed color of Perm. Debt tab to light tan
- Revised Construction Loan calculation macro to be more precise (now runs goal seek calculation 4 times rather than 1)
- Added Data Validation to MF-RR tab, Summary tab, and MF-OpSt tab
- Added ‘Lender Reserves’ calculation on Perm. Debt tab
- See B21:E23 of Perm. Debt tab
- Select which items to reserve for (debt service, capital expenditures, insurance, and/or taxes)
- Select how many months to reserve for
- Cell E21 of Perm. Debt tab estimates the total reserve based off the inputs in the Lender Reserve calc section
Version 0.5.5 (October 21, 2017)
- Clarified waterfall sequence language
- Moved placement of Development tabs to be immediately to the right of report tabs
- Modeled ‘Operating Cash Flow Shortfall’ calculation on Dev-Interest Calc tab
- Added optional input line item – Operating Shortfall – to ‘Other’ category in Development budget
- Modeled stabilization date for MF and ORI properties (see ORI-CF row 56 and MF-CF row 58)
- Changed Summary tab ‘Stabilization Date’ input to be optional; value now pulls from stabilization calculation
- Added various data validation requirements on the Summary tab to minimize user error
Version 0.5.4 (September 17, 2017)
- Added After-Tax (Unlevered) Analysis Module
- Turn on After-Tax report via the ‘Includes Modules?” section of the Summary tab
- Updated Print Mode to include After-Tax tab
- Added data validation to various inputs to reduce user input errors
- Added a per unit label to Budget tab to better understand cost per unit or SF(or M2)
- Updated Add Budget Line Item macros to account for new column on Budget tab
- Working on adding functionality for Ai1 to work with metric system (UNDER CONSTRUCTION)
- Added square meters toggle to Summary tab
- Added capability to Summary tab, report tabs, Perm-Debt tab
- Added capability to Construction Module tabs (Sources and Uses, Budget, and Gantt)
Version 0.5.3 (August 27, 2017)
- Fixed an issue where ORI physical occupancy wasn’t calculating correctly for reporting purposes
- Added option to Navigation section of Summary tab to hide Development tabs, even when Development module is turned on
- Perm. Debt tab now only shows when the ORI or MF tabs are set to show in Navigation
- Fixed error on MF-RR where the units leased vs units vacant table (O6:Q8) was displaying leased units incorrectly
- Added “Print Mode” – use drop-down menu (Summary U45) to set all printable font to black for improved presentation
- Added a fully dynamic IRR Matrix report, which estimates unlevered and levered IRR at the end of each year
- Relabeled cell M10 of MF-RR to clarify that In-Place rent in development mode, is rent of 1st generation tenants
- Removed erroneous data validation lists in column G of the Summary tab
Version 0.5.2 (August 12, 2017)
- O11:U11 on the Summary tab was mislabeled when Development module is on, issue has been fixed
- Fixed error on ORI-CALC tab, formulas in columns AHF:AMR were modeling one too many renewal income months
- Added a “Tenancy Analysis” tab with rollover analysis for Office/Retail/Industrial underwriting (Tenancy Analysis Report)
- Updated the Summary tab worksheet code to auto hide/show the Tenancy Analysis Report
- Changed font of ORI Module drop-down on Summary tab from blue to orange (from required input to optional input)
Version 0.5.1 (July 29, 2017)
- Changed font color of monthly ‘Other CapEx’ cash flows on MF-RR tab to denote an optional input
- Added Capital Expenditure, ‘Other CapEx’, to ORI module and set font for monthly cash flows to orange to denote an optional input
- Added four ‘Other Land Costs’ lines to the Residual Land Value module to analyze land value exclusive of other land costs
- Modeled double promote for scenarios with (Sponsor + LP) and (Sponsor with GP + Investors)
- Misc. formatting and tab placement cleanups
- Replaced rollover table on Summary tab with NOI and valuation graph; necessary now with MF module
- Fixed issue where graphs on ‘Sources and Uses’ tab were no longer dynamic to construction length
Version 0.5.0 (July 22, 2017)
- Linked equity contribution table (Equity CF rows 10-13) to Annual/Monthly toggle on Summary tab cell S27
- Removed redundant macro running on Sources and Uses tab
- Removed data validation from budget timing, as it conflicted with add rows macro
- Created Student Housing and Seniors Housing Module
- Updated Rate Matrix
- Added Student Housing and Seniors Housing to property type drop-down on Summary tab
- Expanded MF-Settings tab to include nomenclature for Student Housing and Seniors Housing
- Updated labels on MF-RR and MF-OpSt tabs to account for Student Housing tracking ‘Beds’ instead of ‘Units’
- Added calculation on MF-RR tab H8/F8 (Column AT) for Units vs. Beds for Student Housing
- Misc. Formatting Updates
Important: The Student Housing and Seniors Housing modules are functional but have not yet been fully tested for errors
Version 0.4.6 (July 14, 2017)
- Linked labels on Uses graph to labels on Budget tab to make the graphs dynamic
- Fixed Forecast Method and S-Curve Rate drop-down menus; data validation had broken with recent changes to the Budget tab
- Added a link on the Version tab to the new support forum for asking questions and making suggestions related to this model
Version 0.4.5 (July 8, 2017)
- Simplified Gantt chart such that only categories are shown, logic changed to only track categories with budget >0
- Moved Dev-SCurve Calc from its own tab to Budget tab to accommodate adding addt’l budget items
- Coded macros to add additional budget line items to Budget tab (to delete, simply delete the item row)
- Added drop-down menu and ‘Add Item’ button to execute ‘Add Budget Item’ macros
- Misc. formatting cleanups
Version 0.4.4 (June 24, 2017)
- Modeled monthly equity waterfall cash flows on Equity CF tab
- Added toggle to select between Monthly or Annual partnership return calculation (Summary tab cell S27)
- Removed most uses of ‘$’ (USD) to accommodate non-US based users
- Made Sources/Uses table labels on Summary tab dynamic to property type (i.e. MF = /Unit, otherwise PSF or /M)
- Added Quick Start Guide to Summary tab for first-time users
- Added Rate Matrix Setup toggle to Navigation section of Summary tab
- Protected Summary tab without password, to unprotect go to Review>Unprotect Sheet
- Wrote Macro that sizes construction loan amount to LTC input on Sources and Uses tab, eliminating need for Goal Seek
- Misc. formatting fixes, sorted MSA list by A-Z
Important: The Ground Lease Valuation module is functional but has not yet been fully tested for errors
Version 0.4.3 (June 17, 2017)
- Added ground lease valuation module
- Subtracting value of ground lease from reversion value in each month and year
- Added ground lease payments below NOI; figured into DSCR, DY, CoC, IRR, and EMx calculations
- Summary tab, changed purchase price input to orange (optional)
- Summary tab adjacent to purchase price input, added drop-down to set purchase price based on Direct Cap or DCF valuation
- Added print areas to make model printer-friendly
Important: The Ground Lease Valuation module is functional but has not yet been fully tested for errors
Version 0.4.21 (June 10, 2017) – Minor Update
- Corrected F12 (NRA) on Summary tab to display multifamily NRA when applicable
- Fixed data validation error in column P of the MF-RR tab
Version 0.4.2 (June 3, 2017)
- Fixed error where 2nd generation concessions were not calculating correctly
- Eliminated Downtime Vacancy / Loss-to-Lease line from ORI-OpSt; downtime vacancy now included in Gross Potential Rent
- Discount rate assumption now hidden when in Development mode
- Fixed formatting error; Property Value on Property CF was showing an error in post-analysis end years when analysis period was less than 10 years
- Formatting change; Removed USD signs from values on Budget tab
- Corrected error in MF Loss-to-Lease where it was not calculating correctly during lease-up period
Version 0.4.1 (May 29, 2017)
- Added data validation and an error check to the MF-RR to avoid situation where Units Leased exceeds total units
- Removed ‘# Tenants’ and ‘Largest Tenant (% of NRA)’ outputs from Summary tab
- Changed year 1 cap rate on Property CF tab to be equal to stabilized cap rate on Summary tab
- Fixed error where ORI cash flows were not appearing on ORI-OpSt tab
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 functional 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