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

All-in-One (Ai1) Model for Underwriting Development and Acquisitions (Updated Jul 2024)

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.

In November 2019, after over 40 updates over two years, we officially removed the beta label from the model. This means that it has received sufficient community feedback and testing that it is largely stable. With that said, the model likely still contains errors and room for improvement. So if you spot something needing changed, please let us know.

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

Read about the limitations of Excel compared to the non-Excel based alternatives.

So, What’s in the Model?

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

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.

Compatibility

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

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

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.

Summary Tab

*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.

Quick Note: Not interested in DIY analysis? Consider working with A.CRE Consulting to handle your bespoke modeling project.

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.

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

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 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)

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.

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.

Third, the model simplifies calculation of leasing commissions and free rent. Leasing commissions are calculated on the base rent without bumps and free rent is charged at the beginning of the lease (rather than spread over the expected free rent period).

Lastly, 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.

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.

Guide to Getting Started – Video Introduction

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

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

Note that this video introduction was created using v0.75 of the model, released in November 2019. Other tutorials are b

 

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.

Check out the Support Forum

About the Author: Born and raised in the Northwest United States, Spencer Burton has over 20 years of residential and commercial real estate experience. Over his career, he has underwritten $30+ billion of commercial real estate at some of the largest institutional real estate firms in the world. He is currently President and member of the founding team at Stablewood. Spencer holds a BS in International Affairs from Florida State University and a Masters in Real Estate Finance from Cornell University.


Version Notes

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

Version 0.89 (Jul 27, 2024)

  • Updated label in H29 of the Summary tab to include additional project costs
  • Added note to H29 input (M29) to prompt user in Acquisition scenarios to include Other Project Costs in M29 when applicable
  • Added note in ORI-RR tab to prompt user to add Leasing Cost to M29 (or Project Budget) when a 1st Generation Lease starts after analysis start
  • Fixed Equity Multiple formulas on Summary tab; wasn’t properly accounting for impact of fees in periods where equity contributions are made
  • Misc. formatting and updates to placeholder values

Version 0.886 (Mar 25, 2024)

  • Fixed issue where in rare cases the ‘Rent Detail’ placeholder formulas return an error
  • Set ‘Rate Matrix’ tab to hidden by default
  • Updated Placeholder values

Version 0.885 (Jan 31, 2024)

  • Adjusted formula for gross property value, ground lease value, and net property value calculation on the IRR Matrix Tab (Row 7,8, and 9)
  • Adjusted formula for gross reversion value calculation on the Property CF tab (Row 35)

Version 0.884 (Jan 29, 2024)

  • Added Mixed-Use Functionality
    • Set ‘MF Tabs’ value on Summary tab to an optional ‘Input’ from an output; allowing users to test mixed use (i.e. MF + ORI)
    • functionality
    • Added ‘Mixed-Use’ property type to Property Type drop-down list (F7 on Summary tab)
    • Updated ORI and MF tab navigation logic to ‘Yes’ when Property Type is ‘Mixed-Use’
    • Added note to ‘Summary’ tab to clarify that Mixed-use funcionality is still in beta
  • Updated Placeholder values
  • Updated Version tab

Version 0.883 (Aug 22, 2023)

  • Added logic to the optional formula in cell Summary K17 to avoid erroring out in rare cases

Version 0.882 (Aug 14, 2023)

  • Fixed Perm. Debt E28 formula; had been referencing the wrong range
  • Misc updates to placeholder values

Version 0.881 (May 20, 2023)

  • Fixed issue where Acquisition Fee was not included in return calculation when Development Module was activated
  • Various Updates to Perm. Debt module
    • Updated default SOFR yield curve
    • Linked Yield curve for Junior/Mezz debt to Senior to eliminate need to update both
    • Added Variable Rate ‘Spread’ input in row H
    • Updated ‘Rate (Monthly)’ calculation to accommodate ‘Spread’ input
    • Updated Conditional Formatting for Senior Debt (Refinance) to include rows 29/30
    • Renamed ‘Variable Rate (Annual)’ input to ‘Benchmark Rate (e.g. SOFR)’
    • Merged Variable Rate / Fixed Rate input cells for improved user experience
    • Added link to forward yield curve data in cell G18
  • Misc formatting updates
  • Misc. placeholder value updates

Version 0.88 (Nov 14, 2022)

  • Fixed issue where Non-reimbursesable expenses were being reimbursed (ORI-OpSt)
  • Updated Reimbursement module to provide a more approximate value
  • Added ‘Admin Fee Income’ line item to ORI-OpSt to allow owner to capture management fee costs
  • Added note to ‘Expense Reimbursement’ input cell to make clear that it is a percentage of adjusted EGI
  • Update placeholder values
  • Misc. formatting updates

Version 0.87 (May 14, 2022)

  • Various updates to placeholder values
  • Revamp of Perm. Debt tab to fix issues in various edge cases
    • Updated Payment (rows 11, 22, and 33 on Perm. Debt tab) logic to fix issue where in certain cases interest was under/over counted
    • Misc. formatting fixes
    • Updated placeholder Variable Rate to be more consistent with a typical forward rate curve

Version 0.86 (Oct 13, 2021)

  • Fixed issue with Construction Loan recalculate macro; calling the wrong object created an error upon running the macro

Version 0.85 (Sep 28, 2021)

  • Fixed issue where Construction Loan amount wasn’t including Operating Shortfall in payoff (Perm. Debt E9)
  • Updated Property CF tab to reflect adjusted Construction Loan calculation
  • Revised ALL macros to refer to worksheet index name, rather than actual name, to allow worksheet name changes
  • Renamed macro Modules for ease of navigation
  • Update to all Summary sheet code to refer to worksheet index names, to allow worksheet name changes

Version 0.84 (Jun 26, 2021)

  • Fixed misspelled word (Nomenclature) on MF-Settings and ORI-Settings tabs
  • Added a note to the ‘Residual Land’ tab to avoid unexpected behavior using the tool
  • Fixed issue with Equity CF tab where Equity Multiple hurdles were not calculated for Monthly Cash Flow
    • Updated Preferred Return formulas in monthly section of Equity CF tab
    • Updated Inputs in monthly section of Equity CF tab
  • Minor updates to placeholder values
  • Cleaned up deprecated Named Cells from Name Manager

Version 0.83 (Mar 13, 2021)

  • Fixed shading over ‘Detailed Reimbursement’ when toggled to ‘Yes” (ORI-RR)
  • Fixed ‘Management Fee’ line on ORI-OpSt tab; now autopopulates based on Management Fee % assumption (H33)
  • Updated link to All-in-One help section
  • Misc. formatting updates

Version 0.82 (Nov 5, 2020)

  • Misc. updates to default inputs
  • Update to Variable Rate module on Perm. Debt tab
  • Added custom ‘Variable Rate’ line to each tranche of permanent debt (Rows 18, 29, 40)
    • Updated Conditional Formatting to move variable rate inputs to custom line
    • Update rate logic to use variable rate custom line when Variable Interest Rate is selected
    • Solves issue where reverting between fixed and variable required re-building interest rate line
  • Fixed issue where Perm. Debt Fees were funding in month 1, rather in Perm. Debt funding month (row 86 of Property CF)
  • Various formatting updates/enhancements

Version 0.81 (July 29, 2020)

  • Fixed issue where Sponsor Fees were not calculating in the Monthly waterfall on the Equity CF tab
  • Fixed Print Mode on Summary tab; Hidden text was erroneously showing in Print Mode

Version 0.80 (July 4, 2020)

  • Removed Levered Present Value Calculations as they were unnecessary
  • Break out Preferred Return, Return of Capital, Excess Cash Flow, and Promote on Equity CF tab
    • Broken out for Annual and Monthly analysis
    • Broken out for both LP and GP
    • GP includes Promote calculation
  • Removed After-Tax analysis module (no longer supported)
  • Misc. formatting updates

Version 0.79 (Apr 26, 2020)

  • Fixed issue (MF-Calc AGR4–>) where 2nd Generation Free Rent was double-counting units in certain situations
  • Cleaned up unnecessary drop-down menus on MF-RR tab
  • Cleaned up unnecessary ‘Reimb. % as of:” formula on ORI-RR tab
  • Updated IRR Matrix to accommodate situations with mid-hold refinance

Version 0.78 (Mar 16, 2020)

  • Added ‘Valuation Purpose’ assumption to ‘Valuation Assumptions’ section on Summary tab
    • Choose Valuation or Acquisition in cell M27
    • Labels change depending on whether Valuation or Acquisition
    • Acquisition Price now a blue font cell, whereas Gross Valuation is an Optional Input cell
    • Fixed drop-down list in cell G47 of ORI-OpSt tab
  • Fixed issue where ORI-OpSt cell G25 was showing M2 when SF was selected
  • Changed ‘RENT ROLL’ label on ORI-RR tab (B9) to ‘RENT ROLL (1ST GEN TENANT)’ for clarification purposes
  • Updated dummy Rent Roll dates on ORI-OpSt tab
  • Misc. formatting enhancements

Version 0.77 (Jan 26, 2020)

  • Fixed error in Lender Reserves formula that was causing it to error on when ORI module is active
  • Updated Equity Multiple formula on Equity CF tab to more accurately account for Sponsor Fee income
  • Updated Equity CF tab to allow for situations where Sponsor contributions 0% but still participates in promote
  • Include option for Loan Assumption
    • Revised ‘Payoff at Loan Maturity” formulas on Perm. Debt tab to better accommodate assumptions
    • Updated ‘Payoff at Loan Maturity’ formula
    • Added drop-down menu to ‘Perm. Debt’ tab to allow user to select New Loan, or Loan Assumption option
    • Updated Amort. and IO payments to become inputs when Initial Perm Debt is set to ‘Loan Assumption’
  • Fixed ‘Loan Payoff Month’ conditional formatting error
  • Added formula in ‘Annual’ vs. ‘Monthly’ input on Summary tab S27; defaults to Monthly when using development module
  • IRR Matrix hidden when Development module on

Version 0.762 (Jan 18, 2020)

  • Updated construction interest reserve calculation to include interest on operating shortfall reserve

Version 0.761 (Jan 13, 2020)

  • Fixed issue caused by 1.4.2020 change, where Acquisition Debt Funding was occurring in month 1 rather than month 0

Version 0.76 (Jan 4, 2020)

  • Revamp of Ground Lease Module
    • Moved Ground Lease Payment to below Cash Flow from Operations
    • Updated Unlevered and Net Cash Flow line to account for change in Ground Lease payment placement
    • Revised Ground Lease Payment formulas’ INDEX() function to only run when GL module is on; to save memory resources
  • Set Senior Permanent Debt ‘Funding Month’ (Perm.Debt D6) to an optional input cell (orange); previously not an input
  • Changed Stabilization Date optional input on Summary tab to an analysis ‘Month’ input rather than a ‘Date’ input
  • Changed Operations Begin Date input on Summary tab to an analysis ‘Month’ input rather than a ‘Date’ input
  • Major revamp of Construction Cash Flow module to accommodate dynamic Operating Shortfall calculation
    • Added ‘Total before Operating Shortfall’ line to Budget tab
    • Revised Header row on Budget tab to include “Month” label
    • Changed number formatting to ‘accounting’ on Budget tab
    • Added Operating Shortfall line to Dev-Interest Calc and S&U tabs
    • Removed redundant error message in cell F8 of Budget tab
    • Updated charts on S&U to include operating shortfall Source and Use
    • Various updates to Dev-Interest Calc tab
  • Fixed error in Net Book Value formula on After-Tax tab
  • Revised Equity CF and Sponsor CF Required Return (Monthly) to calculate on an Actual/365 basis
  • Misc formatting fixes and enhancements

Version 0.75 (Nov 9, 2019)

  • Removed beta label from version name, since model has been through over 40 updates and is largely stable
  • Added print date, page number, and “confidential” mark to the print footer of all printable pages
  • Expanded Sponsor Fee module on Equity CF tab
    • Acquisition fee with optional acquisition amount and percentage assumptions
    • Disposition fee with optional disposition amount and percentage assumptions
    • Moved Sponsor Fee assumptions to above the return summary
    • Created table for modeling sponsor fees with %, amount, and frequency
  • Added ‘Calculation Method’ toggle to Equity CF to choose between XIRR and IRR*12 IRR monthly hurdle rate calc
  • Hiding waterfall calculations by default on the Equity CF tab to make seeing Annual vs. Monthly results easier
  • Misc. formatting updates

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)
  • 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

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