A.CRE Value-Add Apartment Acquisition Model (Updated Nov 2023)

We’ve shared a handful of apartment models over the years. Several of those are capable of analyzing apartment acquisitions but none was built for the express purpose of modeling value-add apartment deals. As a result, each has its limitations in value-add scenarios. So today, I’m excited to share a new apartment model; this one built specifically for value-add apartment investments.

Note: Since this model was released in 2020, it has undergone dozens of updates to add new features, revise original methodology, and fix various issues. You can see all of the changes that the model has undergone over that time by reading the version notes.

History of this Purpose-Built Value-Add Apartment Model

About a year ago, in response to numerous reader requests, I modified the aforementioned apartment valuation model to accommodate value-add situations. While it now technically can accommodate a buy, renovate, raise rents, and sell scenario, it lacks a lot of the features value-add investors need in an analysis tool. Plus, that model is also not compatible with Mac for Excel. So I knew I needed to roll out a true value-add apartment model at some point.

The challenge with getting this released has been time. With a full-time job and family, I’m limited to nights and weekends to take on new projects. And considering Michael and I have been consumed over the last year with creating and launching our signature A.CRE Accelerator real estate financial modeling training program, this model has been slow to come to fruition.

What really helped make this happen was our final capstone course in the Accelerator. In that course, the case study involves a hypothetical REPE technical interview in which the student must build a value-add model from scratch. While I was creating the Watch Me Build tutorial late last year for that course, I developed various apartment value-add calculation modules that I was able to incorporate into this model. That exercise was ultimately the catalyst for this model.

Are you an Accelerator member? Check out the value-add apartment model Watch Me Build tutorial. 

Basic Objectives – A.CRE Value-Add Apartment Acquisition Model

Given that the general layout and design of the Value-Add Apartment Acquisition model was inspired by my Apartment Development model, all of the criteria that drove the process remained the same. I wanted the model to be robust – meaning it had to be able to do everything an institutional-quality value-add model can do, while being simple and intuitive.

The result is a model where 100% of inputs are on one, easy to use tab (i.e. worksheet) – the Underwriting tab. Cash flows are shown immediately to the right of the inputs on the Underwriting tab, and the outcomes are reported on an attractive and printer-friendly Summary tab. The only other tabs are an optional detailed expense tab, an optional annual cash flow report tab, and a Versions tab, where changes to the model are listed.

Overview – A.CRE Value-Add Apartment Acquisition Model

The A.CRE Value-Add Apartment Model includes one primary inputs tab, one primary report tab, one optional inputs tab, one optional report tab, and a tab to track version changes to the model.

Version Tab (Visible by default)

The model opens initially to this tab so you can see what changes have been made in the most recent version of the model. On this tab you can also find links to model tutorials, guides, support, and other information.

Underwriting Tab (Visible by default)

The Underwriting tab is where all of your primary inputs are entered. The tab is broken up into six sections, built from top to bottom. The sections can be accessed either by scrolling down to each or using the buttons along the top of the screen. The six sections are ‘Investment Description’,  ‘Investment Cash Flows’, ‘Operating Cash Flows’, ‘Reversion Cash Flows’, ‘Property-Level Returns’, and ‘Partnership-Level Returns (Waterfall)’.

Summary Tab (Visible by default)

While property-level return metrics levered IRR and levered EMx are shown along the top of the Underwriting tab, the bulk of the risk and return metrics are shown/visualized on the Summary tab. The summary tab also includes two operating cash flows charts, a strengths/weaknesses section, a frame to include a picture/map, and a summary of the investment. The Summary tab is meant to be printed, and as such the view mode is set to Print Preview by default.

Annual Cash Flow Tab (Hidden by default)

In beta v0.3, we added an Annual Cash Flow report tab. The tab rolls up the monthly cash flows to annual periods, so you can view high-level cash flows on one page. The report is printable. You can access the report by pressing the ‘Show’ toggle on the Summary tab with the Property Cash Flow section.

Detail Expenses (Hidden by default)

On the Underwriting tab the user has the option to toggle a detailed operating expenses mode. Rather than entering per unit values for pre-renovation (i.e in-place) and post-revonation (i.e. stabilized) operating expense line items, when the user toggles the ‘Detailed’ operating expense mode on the Underwriting tab, a ‘Detail Expense’ tab becomes available where the user can detail out operating expenses. The detail then flows back to the Underwriting tab.

Video Tutorials for Using the Value-Add Apartment Acquisition Model

Since releasing this model, I’ve built out a comprehensive series of video tutorials for using the model. You can find those tutorials here:

---

Features Included in the Model

Here is a list of a few of the features included in this model:

• Dynamic analysis period up to 120 months (minimum 12 months)
• Model renovation costs on a straight-line, s-curve, or manually
• Up to four sources in your capital stack, two equity and two debt
• Fixed or variable rate debt
• Choose the order in which sources are deployed (e.g. GP equity first, LP equity second, Senior Loan 3rd, Junior Loan 4th)
• Option to either refinance or sell at stabilization
• Robust operating cash flow module, with differentiation between in-place and stabilized (i.e. fully renovated) income and expenses
• Compare In-Place and Stabilized pro formas and return metrics on a untrended (i.e. without income/expense growth) or trended basis
• 4-tier, European waterfall (i.e. LP return of capital first before GP is paid a promote) with annual compounding IRR hurdles
• Key assumptions and return metrics, strengths and weaknesses, and investment description on the Summary tab
• Fully dynamic charts on Summary tab
• Detailed expense module
• Operating history analysis
• Buttons to automatically add/delete budget line items
• GP Fees module
• Renovation schedule with option to link hard and soft costs to renovation schedule
• Downtime Vacancy due to vacancy module

Compatibility

This version of the model is only compatible with Excel 2013, Excel 2016, and Excel 365

The model likely still contains errors. If you spot an error, have a feature request, or would like to make a suggestion to improve the model, please let me know.

Download the A.CRE Value-Add Apartment Acquisition Model

To make this model accessible to everyone, it is offered on a “Pay What You’re Able” basis with no minimum (enter $0 if you’d like) or maximum (your support helps keep the content coming – typical real estate acquisition models sell for $200 – $500+ per license). Just enter a price together with an email address to send the download link to, and then click ‘Continue’. If you have any questions about our “Pay What You’re Able” program or why we offer our models on this basis, please reach out to either Mike or Spencer.

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

---

Version Notes

v0.962

• Fixed ‘Data Validation’ for IO input in Refi debt module
• Fixed formula in cell E98 to use loan payoff rather than initial loan balance
• Minor updates to placeholder values

v0.961

• Added ‘Assumption Notes’ section to Underwriting tab; open/close using the [+] / [-] button at the top of column O; use notes to support inputs on each row
  • Included various placeholder notes for reference
• Misc. placeholder values and formatting updates

v0.96

• Added link to A.CRE Best Practices in Real Estate Financial Modeling guide to the Versions tab
• Fixed Model Webpage link on the Verions tab
• Added note to Data Validation in Reserve Month input
• Added ‘Detailed’ Credit Loss calculation (toggle cell E132 to ‘Detailed’ and then see row 113)
• Misc. updates to placeholder values

v0.95

• Fixed an edge case that would break the GP AUM Fee calculation
• Changed labels in B228 and B229; replaced ‘Before GP Fees’ to ‘Property-Level Only’ to clear up possible confusion
• Added AUM Fee option to be calculated based on Total Project Cost (i.e. Gross Assets)
• Added link to Macro troubleshooting guide to Version tab
• Misc. updates to placeholder values

v0.941

• Modified to IRR calculations to account for certain edge cases
• Added line (row 232 – Underwriting) to track Total Equity Contributed by period (not equity outstanding)
• Modified Average Cash-on-Cash Return formula (D231 – Underwriting) to be weighted based on capital contributed

v0.94

• Updated yield curve (for variable rate loan option) to better reflect rapidly changing rate environment
• Renamed ‘SOFR’ to ’30-Day Term SOFR’
• Added Unit Mix pie chart to Summary tab
• Misc. updates to placeholder values

v0.93

• Added Net vs Gross Returns concept to the model
  • Note: Gross refers to returns BEFORE GP fees and promote; Net refers to returns AFTER GP fees and promote
  • Created Data Table on Underwriting tab to calculate Net Returns in all scenarios
  • Updated labels on Summary tab to refer to Gross vs Net returns
  • Updated labels on Underwriting tab to refer to Gross returns
• Fixed issue where Vacancy and Credit Loss result on Summary tab only linked to the Vacancy value
• Renamed ‘Current Income’ and ‘Current Expenses’ back to ‘Pre-Renovation Income/Expenses’ since the change had created greater confusion than it had fixed
• Added a Note to the ‘Pre-Renovation Income’ and ‘Pre-Renovation Expenses’ label cells to provide further clarification
• Calculated the LP and GP annual average rate of return in cells G254:G255 on the Underwriting tab; ROI/Yr on the Summary tab is synonymous with Average Rate of Return
• Fixed issue where ROI/Yr calculation on the Summary tab was not annualized (was for the entire investment)

v0.92

• Added module to accommodate more than one unit and measurement type
  • Option to toggle between Metric (Meters) and Imperial (Feet) unit of measurement; see cell F7 on Underwriting tab
  • Option to toggle between Units, Beds, Rooms, and Doors for use with compatible property types (e.g. student housing, senior housing); see cell G7 on Underwriting tab
  • Numerous changes to labels throughout to accommodate multiple unit and measurement types
• Added ‘Backend’ section at bottom of Underwriting tab to house misc. backend calculations
• Changed label in cell B64 of the Underwriting tab to Total Equity (Before GP Fees)
• Misc. formatting updates

v0.91

• Deleted unnecessary GP Contributions lines from the backend waterfall calculations
• Minor changes to placeholder values

v0.90

• Fixed issue where Post-Reno property tax was being grown by the OpEx Growth assumption, not by the Property Tax Growth assumption
• Removed Management Fee line items from backend calculation to avoid confusion; the backend logic for Management Fee was not being used so this change has no impact on actual cash flows

v0.89

• Added logic to cells H254 and I254 to hand scenarios where the GP contributes 0% of equity
• Updated Data Validation for Loan Term to allow for a Loan Term beyond the Analysis Period length

v0.88

• Renamed the label ‘Pre-Renovation Rent’ to ‘Current Rent’
• Renamed all ‘Pre-Reno’ labels to ‘Current’
• Minor changes to placeholder values

v0.871

• Fixed issue where property tax growth was being pulled from OpEx growth input rather than property tax growth input

v0.87

• Updated ‘Add_Other_Income ()’ macro to insert an Other Income line item under the ‘Pro Forma (Trended)’ section of the Reversion Cash Flow section
• Updated ‘Delete_Other_Income ()’ macro to remove an Other Income line item under the ‘Pro Forma (Trended)’ section of the Reversion Cash Flow section
• Fixed placeholder values under ‘Weaknesses’ list on Summary tab

v0.86

• Added ‘Refinance Outcome’ section to Subsequent Financing section
  • Includes ‘Equity Returned’ amount and % of total
  • Includes ‘Equity Still Invested’ amount and % of total
• Changed cells J93:J94 (Subsequent Financing Funding month) to an output, given that the Funding month is driven by the initial loans’ payoff month
• Updated formula that calculates placeholder initial Loan Term (F76:F77) to be dynamic to Refi vs. Sell selection

v0.851

• Fixed issue where Other Income Add/Delete buttons were mapped to the wrong macros

v0.85

• Created ‘Add Unit Type’ and ‘Delete Unit Type’ functionality; Add unlimited unit types and delete all but two of the unit types
  • Wrote two new macros: Add_Unit_Type and Delete_Unit_Type
  • Added buttons above Unit Mix table to add or delete unit types
  • Created new named ranges (Rel_Unit_Type and Items_Unit_Type)
• Created ‘Add Other Income’ and ‘Delete Other Income’ functionality; Add unlimited Other Income line items and delete all but two of the Other Income line items
  • Wrote two new macros: Add_Other_Income and Delete_Other_Income
  • Added buttons above Other Income section to add or delete Other Income line items
  • Created new named ranges (Rel_Other_Income and Items_Other_Income)
• Added ‘Lender Reserve’ module
  • Inserted ‘Lender Reserve’ line to Investment Cash Flows (row 47); enter forecast method, amount, when the reserve will be released, and what % will be released
  • Inserted ‘Lender Reserve Release’ line to Property-level Cash Flow section (row 220); reserve is returned (i.e. released) to owner via this line
• Fixed an issue where Year 6 growth rates were not being used in analysis (year 5 were used for all remaining years)
• Misc. formatting updates

v0.841

• Fixed DSCR and Debt Yield formulas for secondary financing (cells D91:E92)

v0.84

• Fixed error check formula in cells M78:M80; formula wasn’t calculating correctly with debt Exit Fee was used
• Added ‘Investment Type’ output (Summary E6 and Underwriting K102) that calculates whether an investment is ‘Stabilized’ (i.e. no renovation planned) or ‘Value-Add’ (i.e. renovation planned)
• Misc. updates to placeholder assumptions and formatting

v0.83

• Added a note that Pre-Renovation Operating Expenses are calculated as an Annual Amount/Unit, but then broken out monthly (Underwriting cell ER127)
• Fixed Error Check formula starting in Underwriting cell EX219
• Renamed GP Fee in Soft Costs Const. Mgmt to denote that the fee does not flow to partnership waterfall
• Added note next to Equity + GP Fee section linking to cash flow calculation
• Misc. formatting enhancements

v0.82

• Fixed issued with Loan Balance caused by update to ‘Variable Rate Module’ in v0.80

v0.81

• Fixed issued with Debt Service payment when interest rate is Fixed

v0.8

• Set default analysis period to 120 when ‘REFI’ option selected
• Added ‘Analysis Start’ and ‘Analysis Period’ outputs in frozen rows at top
• Moved default header to below frozen rows
• Fixed blue header on Partnership Cash Flow section to match other sections
• Added labels next to Reversion Cash Flows (rows 199:203)
• Added option to model debt ‘Exit Fee’ in the initial financing
  • Now includes ‘Exit Fee’ assumption in cells E65 and E66 of Underwriting tab
  • Formula in rows 78 and 79 of Underwriting tab modified to include Exit Fee
• Enhanced Variable Rate Module
  • Added Forward Curve input
  • Added Spread input
  • Created ‘Annual Rate’ logic, which takes Forward Curve + Spread to arrive at rate by period
  • Inserted link to Forward Curve data
• Misc. formatting enhancements

v0.72

• Renamed cell B175 to ‘General Vacancy’
• Added ‘Stabilization Month’ output to Investment Description section of Underwriting tab
• Moved ‘Sale Month’ output to row 19 of Underwriting tab
• Fixed Cash-on-cash Return calculation (J26:K26 on the Summary tab) to account for scenarios with cash-out refinance
• Misc. formatting updates

v0.71

• Fixed issue where setting all units to renovated caused the model to error out
• Fixed issue where setting renovation period to one month caused the model to error out

v0.7

• New Renovation Schedule Hard and Soft Cost forecasting module
  • Added ‘Schedule’ drop-down option under forecast method for Hard and Soft Cost line items (G30:G42)
  • Updated ‘Add_Item_Hard_Costs’ and ‘Add_Item_Soft_Costs’ macros to default forecast method to ‘Schedule’
• New Downtime-Due-to-Renovation module
  • Added ‘Downtime Vacancy (Renovation)’ line that takes one month vacancy on units under renovation in that period
  • Added ‘% of Month’ assumption to ‘Downtime Vacancy’ line; 100% = one month vacancy/renovation
• Removed ‘Error Check’ of OpSt line items, as the checks were throwing off false negatives and are no longer necessary
• Fixed error where ‘Bad Debt’ line item had not been appearing in the ‘Reversion (Sale) Cash Flow’ section
• Added header labels to valuation matrix on Summary tab
• Misc. formatting updates and enhancements

v0.6

• Removed beta label
• Added year built and year renovated to Investment Description Section
• Inserted new rows on the Underwriting tab (200 and 201), which calculate value on a /unit and /SF basis
• Created ‘GP Fees’ module
  • Includes more robust Asset Management fee calculation
  • Ability to turn module off or on
  • Acquisition fee option added
  • Disposition fee option added
  • Recorded short video showing GP Fee module capabilities
• Moved Sale Date (i.e Analysis End Date) assumption from ‘Investment Description’ section (J18) to ‘Reversion Cash Flow’ section (D159)

beta v0.5

• Fixed issue where contributions from equity was not properly covering operating shortfall in waterfall calculation
• Added option to model Asset Management Fees as either ‘% of EGR’ or as ‘% of LP Equity’ (cell F221 on Underwriting tab)
• Fixed debt yield calculation in ‘Subsequent Financing’ section
• Created ‘Fixed Roll to Market %’ metric for each unit type to fix issue related to lease-up rent not calculating properly
• Minor update to how ‘Non-Revenue Model Units’ Pre-Reno amount is calculated in In-Place pro forma
• Misc. formatting improvements

beta v0.4

• Added option to automatically add/delete project budget line items
  • Now includes Add/Delete buttons above Acquisition, Hard, and Soft cost sections of budget
  • Wrote VBA macros to add and delete line items
  • Budget sections must contain at least two items (three for Soft costs)
• Fixed issue where Management Fee assumptions disappeared when Vacancy was set to ‘Detailed’
• Added Detailed Operating Expense module
  • Created toggle buttons in row 125 to switch between Basic and Detailed operating expense underwriting
  • Wrote macro so that ‘Detail Expense’ tab appears when toggle is set to ‘Detailed’
  • Built ‘Detail Expense’ tab with Pre-Renovation and Post-Renovation assumptions
  • Wrote macro to link assumptions on Underwriting tab to assumptions on Detail Expense tab when ‘Detailed’ is toggled
  • Added ‘Operating History Vs. Underwriting’ analysis section to Detail Expenses
• Misc. formatting fixes and enhancements

beta v0.32

• Revised growth calculation formulas such that growth year 1 begins in month 1 rather than month 13
• Forcing growth assumptions to start year 2, such that no growth is assumed in year 1 (i.e. set Growth Year 1 to black font cells)
• Added note below growth assumptions on ‘Underwriting’ tab clarify that growth begins month 13
• Fixed issue where Post-Renovation operating expenses were not growing per the correct assumptions

beta v0.31

• Changed ROI output on Summary tab to ROI/Yr
• Added Mill Rate output under Reversion section of Underwriting tab

beta v0.3

• Added Rent/SF and Rent/Unit for Total Effective Rental Income on Underwriting tab
• Added new ‘Annual Cash Flow’ report tab
  • Incudes toggle on ‘Summary’ tab in row 34
  • Includes two new macros that hide/show the ‘Annual Cash Flow’ tab and toggle the Hide/Show buttons
  • Includes summary cash flow report, with monthly cash flows rolled up to annual periods, on the ‘Annual Cash Flow’ tab
  • All return metrics are still calculated on a monthly, not annual, basis
• Updated header in ‘Returns’ section to make dates/months dynamic to hold period

beta v0.22

• Fixed error where OpEx and CapEx were growing off of Other Income growth rate assumptions
• Removed data validation lists from K73 and K74 on the Underwriting tab
• Fixed issue where Initial Fund months were not displaying correctly (J73 and J74 of Underwriting tab)
• Removed data validation lists from K73 and K74 on the Underwriting tab
• Broke out Credit Loss (i.e. Bad Debt) from General Vacancy

beta v0.21

• Fixed error where GP and LP ROI calculations on Summary tab were reversed
• Changed ‘Asset Management Fee – Max’ language to make it easier to understand; now asks “Fee Accrues” with response Yes or No
• Simplified AM Fee/Mo formula

beta v0.2

• Added ‘Term’ to Subsequent Financing; note that Subsequent Financing Term is NOT an assumption but rather tied to Analysis End month
• Updated various input cells to include data validation
• Added option to model GP IRR Catch Up
  • Created drop-down menu in cell F222 to toggle GP Catch Up on or off (i.e. GP Catch Up toggle)
  • Added conditional formatting where GP Catch Up toggle becomes available when GP distribution % in pref tier is less than GP contribution %
  • Updated waterfall module to include GP Catch Up calculation
• Added option to model GP asset management fee
  • Included assumption in cell F217 to enter annual asset management fee %; paid out monthly per cell G217
  • Added assumption (Max): ‘None’ means AM fee shortfall accrues to GP, ‘Available’ means GP is paid lesser of AM fee or available CF
  • Updated waterfall module to include GP AM fees
• Added misc. labels to help better navigate model

beta v0.13

• Added Cash-on-Cash return metric to Underwriting and Summary tabs
• Fixed issue where two GP ownership assumptions existed
• Added option to perform GP-only analysis (i.e. exclude LP)
• Updated GP and LP equity multiple formula to match logic used for property-level equity multiple formula
• Removed property-level ROI formula

beta v0.12

• Fixed issue where Market Cap Rate in Development Spread calculation was not dynamic to ‘Untrended’ vs ‘Trended’
• Fixed issue where the ‘As of mo.’ output in the Reversion section was not displaying the month correctly for the ‘Stabilized’ pro forma

beta v0.11

• Added navigation links at top of Underwriting tab
• Added ‘Refinance vs Sell’ toggle
• Repositioned debt cash flows to accommodate hiding Subsequent financing rows when model is set to ‘Sell’ mode
• Misc. logic fixes
• Misc. formatting improvements

beta v0.1

• Initial release of the Value-Add Apartment Acquisition Model