Watch Me Build – IRR Partitioning in Excel
In this post, I’d like to show you how to partition the internal rate of return of your real estate investment in Excel. I also throw in a quick equity multiple partition, to highlight how the time value of money affects your returns. I’ve recorded a short Watch Me Build video tutorial to illustrate the mechanics of doing this in Excel, and also link you to some great resources for learning the academics behind IRR partitioning.
In additional to the Watch Me Build video. I also share an Excel module, that can be added to your own real estate models, that once setup will automatically (i.e. dynamically) partition the unlevered IRR of your investments. This module is included as a separate tab in the Watch Me Build Excel file (find download link at the end of this post).
Are you an Accelerator member? See lesson 5, course 5 of the core curriculum for detailed instruction on how and why to partition and IRR and Equity Multiple. Not yet an Accelerator member? Consider joining the real estate financial modeling training program used by top real estate companies and elite universities to train the next generation of CRE professionals.
Return from Income + Return from Sale = Total Return
Why Partition the IRR?
First, why is this an important analysis tool? IRR partitioning is a good way to analyze your cash flow on a time value of money basis to determine where the returns, and associated risk for those returns, are coming from.
So for instance, you buy an empty building and get it at a discount because it’s vacant. You should expect a) your returns to be greater than if you bought a fully stabilized building at acquisition because you’re taking the lease-up risk, b) a greater proportion of your returns to come from appreciation since you bought the building at a discount and are expecting a value pop when the building stabilizes, and c) a lesser proportion of your returns to come from income because the building will not be cash flowing early on and you’re more likely to sell it upon stabilization.
The IRR partitioning technique allows you to confirm that your expectations for a given investment are in fact correct. That some proportion of the overall IRR comes from the appreciation of the property, and the other proportion from the operating income produced at the property over the hold period. Or put another way, partitioning the IRR involves dissecting the IRR to determine if a proposed investment matches your investment criteria.
Adding the IRR Partition Tool to your Own Real Estate Model
In addition to the Watch Me Build template and completed file, I’ve included a module in the Excel file (find download link below) that can be added to your own models. This module includes a tool to automatically partition both your monthly, and annual unlevered internal rate of return.
To use this tool, simply drag the worksheet into your own model. Once in your model, link the monthly unlevered total of your investment cash flow, operating cash flow, and reversion cash flow section to the relevant rows in the module. The module will then automatically rollup the monthly cash flows to annual cash flows, and partition both the monthly and annual IRRs.
You’ll note that I left the annual cash flow inputs as optional (i.e. orange font cell). This is to denote that the assumption is that you’ll be linking monthly cash flows, and those cash flows will automatically be rolled up to annual (i.e. the orange font cell formulas). Nevertheless, the cells are left as optional inputs in case you’re inserting the module into a model with annual periods only. In that case, link directly to the annual section and ignore the monthly portion.
Watch Me Build Video – Partitioning the IRR in Real Estate
In the following video, I share my screen as I go through the process of partitioning an unlevered IRR in Excel. I also share my IRR partitioning module with you so that you can add the module to your own models.
Download the Excel workbook used in the video at the end of this post. If you have any questions, please don’t hesitate to ask.
Download the IRR Partitioning Exercise File and Module
To make this tool 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 Excel modules sell for $100+ 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.
Frequently Asked Questions about IRR Partitioning in Real Estate
Version Notes
v2.0
- Added ‘IRR Partitioning Module’
- Annual and Monthly options
- Plugin-and-play compatible into existing models
- Monthly to annual rollover by default
- Updated How to – IRR Partitioning
- Renamed labels in cells B5:b7
- Removed USD $ to accommodate non-US users
- Updated date header
- Recorded ‘How to Perform IRR Partitioning in Real Estate Analysis’ video
- Revised Equity Multiple logic to match method taught in Accelerator
- Added Template and Completed worksheets
- Misc. formatting changes
v1.0
- Initial release
