Using an IRR Matrix to Determine Hold Period (Updated Dec 2023)
When building a real estate financial pro forma, it’s important to model for and include a summary of various return and risk metrics. Among the different return metrics, the internal rate of return (IRR) – both on an unlevered and levered basis – is one of the more important metrics to include in your analysis.
One issue common to most models (my own models included!), is that the internal rate of return is generally only be calculated for the entire hold period. The problem with doing this, is you’re not necessarily sure that the hold period you’ve selected (e.g. 10 years) maximizes the internal rate of return.
One solution to this problem, is to include an IRR matrix into your model that calculates and reports the internal rate of return at the end of each year of the hold period. This way, you can see the projected internal rate of return in any given year helping you to better determine an appropriate hold period.
In this post, I’ll show you how I build an IRR matrix and share an Excel Workbook with an IRR matrix included for you to use in your own real estate financial models. In December 2023, I added the Equity Multiple metric to this IRR matrix to provide a more holistic look at the returns in each year.
Are you an Accelerator member? A member asked in the original Accelerator forum how to create the column chart below in Excel. So, we created a lesson in the ‘Mastering MS Excel for Real Estate’ Accelerator endorsement (available to all Accelerator members). See course 4, lesson 5 of the ‘Mastering MS Excel for Real Estate’ endorsement. Not yet an Accelerator member? Considering joining today!
IRR by Year – Results of an IRR Matrix Analysis
Cash Flows Necessary to Build an IRR Matrix Module
In this example, I’ll build both an investment-level unlevered IRR Matrix (IRR before debt without taking into account partnership split) and an investment-level levered IRR Matrix (IRR after debt without taking into account partnership split). To do this, you’ll first need to model certain cash flows:
- Model Investment Cash Flows. These are the capital outflows such as acquisition costs, development costs, and value-add capital costs required to acquire a string of positive cash flows into the future. To calculate levered IRR, you will also need to model in loan fundings in this section.
- Model Operating Cash Flow. These are the operating cash flows acquired via the Investment Cash Flows above. This would include net operating income, less any capital expenses during operation. On a levered basis, it will also necessitate subtracting debt service to arrive at Cash Flow after Financing.
- Model Reversion Cash Flow. This is the value of the property at the end of the analysis period, as well as the estimated value at the end of each period. Additionally, the model provides for some reduction of the value due to selling costs. To model levered cash flow, it’s also necessary to include loan payoffs.
The file includes both a module for annual periods, as well as a second module for monthly periods. Use the module that fits the period length used in your model.
Once you’ve modeled the appropriate property-level cash flows, simply link those to the blue font cells in the module and the IRR Matrix will do the rest. The output will look something like this:
How to Build an IRR Matrix – Two Options
If you’re looking to build your own IRR Matrix, a few years ago I created a video tutorial on how to build an IRR Matrix for acquisition scenarios. The exercise assumes annual periods, and can only accommodate scenarios where 100% of the Investment Cash Flow (i.e. acquisition costs) occur in time zero.
As you’ll see, the key to building the IRR Matrix portion of the module is the proper use of absolute and relative cell references in the formula. This allows you to write just one formula and then copy over and down all at once. Below the video, find a link to the latest version of my IRR Matrix module.
In 2020, after making a significant update to this module, I created a follow up video. That video (shared below) both walks you through how to use the module in your own model, as well as how to build the matrix yourself. Check out either (or both) video walkthroughs/tutorials below.
Video Tutorial – Build an IRR Matrix (Using Version 1.0)
Note that the video tutorial is based on version 1.0 of this module. Version 2.0 (and later) uses updated logic, allows for investment cash flow in periods beyond time zero, and offers both an annual and monthly version.
Video Tutorial – How to Use Version 2.0 + How to Build the IRR Matrix
As mentioned above, this video walkthrough + tutorial uses version 2.0 of the module released in late 2020. Hope the combination of these two videos are helpful to your own real estate financial modeling!
Download the IRR + EMx Matrix Module (Annual and Monthly Periods)
To make this module 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 modules sell for $100+). 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.
Frequently Asked Questions about Using an IRR Matrix to Determine Hold Period
Version Notes
v2.2
- Added the Equity Multiple to the matrix
- Inserted column/calculation for EMx in both Annual and Monthly tabs
- Renamed the file
- Updated headings/labels to include EMx
- Updates to placeholder values
v2.1
- Updates to placeholder values
- Added link to Best Practices guide
- Misc. formatting updates
v2.0
- Major update to accommodate development and value-add scenarios
- Added ‘Investment Cash Flow’ section to bring in development (or value add capital) cash flows
- Added ‘Annual Reversion Cash Flow’ section to calculate reversion value by period
- Added Selling Costs assumption
- Re-built IRR Matrix section to simplify the logic
- Added Monthly IRR Matrix
- Updated logic to accommodate monthly, rather than annual, periods
- Misc. formatting updates
v1.0
- Initial release
