Real Estate Portfolio Valuation Model (Updated Aug 2022)
I built, and would like to share, a real estate portfolio model for valuing a portfolio of up to 30 properties. This is a “roll-up” model, meaning you will need to model your unlevered (before financing) property-level cash flows in a separate model (e.g. such as ARGUS, your own Excel model, or one of our Excel acquisition DCF models). However, once you have property-level cash flows ready, this is a quick and efficient way to see how the properties perform when modeled together as a whole.
The model was initially built in 2015. Since then, it has been updated various times. The most significant updates include the addition of a waterfall model with IRR and Equity Multiple hurdles, and the inclusion of a fully dynamic Property Level Returns tab to view cash flow and return details by property. See the version notes for more details.
About the Real Estate Portfolio Valuation (Acquisition) Model
The model is fully dynamic, calculates all of the basic portfolio and property-level return and risk metrics, handles your financing assumptions, and allows for multiple scenarios. it includes the following tabs:
Version. A summary of changes made to the model, as well as links to helpful resources.
Portfolio Summary. Seven portfolio-level assumptions, plus a summary of portfolio metrics, financing assumptions, and investor-level returns.
Property Assumptions. Physical, valuation, and financing assumptions by property.
Cash Flow. Assumptions for Effective Gross Revenue (EGR), Net Operating Income (NOI), and Cash Flow from Operations (CFO) for each property by year.
Investor Level Returns. Assumptions for partnership structure and waterfall calculations at the portfolio level.
Portfolio Level Returns. Roll-up of property-level operating cash flows, and calculation of portfolio-level risk and return metrics.
Property Level Returns. Detailed cash flow, risk metrics, and return metrics by property. Use the drop-down menu (cells E3:F30) to toggle between properties.
Debt. Summary of debt service and loan balance per year by property.
Insert Cash Flow drop —>. A divider to separate the main tabs (left) from any property-level cash flow tabs added by the user.
If you need a tool to forecast your property-level cash flows, you might check out our real estate acquisition DCF models. You can also browse our entire collection of Excel real estate models. Finally, if you’re still using ARGUS DCF, you might find our free ARGUS DCF training tips and resources helpful.
How to Use the Real Estate Portfolio Valuation Model
To help you get familiarized with using the model, I’ve recorded two tutorial videos. The first is a basic walk-through I recorded back in 2015 when I originally released the model. Given the age of the video, you’ll note that the model has changed some since that recording. Nevertheless, the core functionality of the model remains.
I recorded the second video in 2018. It shows you how to take your property-level cash flows (completed in separate models) and drop them into this portfolio tool. Once you’ve dropped the property-level cash flows into the model, you can then model your portfolio and investor-level cash flows. The video also gives provides a basic walk-through of the Investor Level Returns tab added since the original release.
Real Estate Portfolio Valuation Model – Basic Walk through
Note that various changes have been made to the model since this video was recorded. Nevertheless, the core functionality remains the same.
Real Estate Portfolio Valuation Model – How to Insert Property-Level Cash Flows
A few important notes about your property-level modeling in order to properly roll-up to the portfolio level:
- The analysis start date for each property must be the same
- The analysis length must be the same for each property
- Each the model for each property must include annual cash flows
- At minimum, effective gross revenue, net operating income, and capital expenditures must be modeled for each property
Download the Real Estate Portfolio Valuation 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 Excel models sell for $100 – $300+ per license). Just enter a price together with an email address to send the download link to, and then click ‘Continue’. If you have any questions about our “Pay What You’re Able” program or why we offer our models on this basis, please reach out to either Mike or Spencer.
We regularly update the model (see version notes). Paid contributors to the model receive a new download link via email each time the model is updated.
Version Notes
v3.4
- Removed requirement that Analysis Start date must start on the first day of the month
- Added new module that allows for using either SF or M2
- New dropdown menu for SF or M2 input on Portfolio Summary tab (cell J2)
- Replaced all SF labels with dynamic formula to use either SF or M2
- Various updates to placeholder values
v3.3
- Removed leftover cell link that was causing an ‘automatic link’ error
- Fixed issue where lender fees were being included in the unlevered cash flow
- Added breakout of partnership cash flow
- Preferred return to LP and GP (D21 and D31 of Investor-Level Returns tab)
- Return of Capital to LP and GP (D22 and D32 of Investor-Level Returns tab)
- Promote to GP (D33 of Investor-Level Returns tab)
- Excess Cash Flow to LP and GP (D23 and D34 of Investor-Level Returns tab)
- Formatting updates
v3.2
- Added GP Catch-Up Module to Investor-Level Returns tab
- Updated instructions on Investor-Level Returns tab
- Added ‘Levered IRR by Property’ graph to Portfolio Summary tab
- Created new named range called ‘Range_Property_LIRR’ to find levered IRR by property
- Misc. formatting enhancements and fixes
v3.1
- Cleaned up Promote Structure language on the Investor Level Returns tab, to make it more clear who is being promoted and how
- Renamed ‘Unlevered Cash Flow’ to ‘Cash Flow Before Debt Service’ for naming consistency (Property and Portfolio Level Returns tabs)
- Renamed ‘Levered Cash Flow’ to ‘Cash Flow After Financing’ for naming consistency (Property and Portfolio Level Returns tabs)
- Added Portfolio-Level IRR and Equity Multiple calculations with and without Sponsor fees to Portfolio Summary tab
- Updated Sponsor Equity Multiple calculation to include Sponsor fees
- Set Print Areas for Portfolio Summary and Portfolio Level Returns tabs
- Updated Version tab
- Misc. formatting changes
v3.0
- Added Direct cap value to Portfolio Summary page
- Set annual cap rate on Portfolio Level Returns tab (row 11) to optional input (orange font cell)
- Added option to use Equity Multiple hurdles in partnership waterfall
- Added option to include sponsor fees (e.g. asset management fees, acquisition fees, disposition fees, etc) in investor-level returns
- Removed $ USD signs to accommodate non-USD investments
- Changed Exit Cap Rate Year (Reversion Year) to output, not assumption
- Exit Cap Rate Year set to one year following analysis end year, with max of year 15
- Fixed issue where ‘Price’ assumption was appearing on Property Assumptions tab when it shouldn’t
- Fixed error where lender fees were not included in All-in-Basis
- Removed ‘Purchase Price Method’ assumption from the Portfolio Summary tab to avoid confusion
- Renamed ARGUS Cash Flow –> tab to Insert Cash Flow Drop –>, to accommodate other operating cash flow projection options
- Made headings, formatting, background colors and fonts more consistent from one tab to the next
v2.3
- Added an “Investor Level Returns” tab to the model, which uses my Real Estate Equity Waterfall Model
- Fixed some errors in conditional formatting
- Added an “Investor Returns” box to the Portfolio Summary tab, summarizing the results of the Investor Level Returns tab
- Fixed an error on the Portfolio Summary tab, that had referenced the wrong cell when outputting the Levered IRR
- Fixed an error on the Debt tab, where the individual loan balances were not appearing
- Additional formatting to improve the look of the model
v1.0
- Initial release
