Part 1: The IRR

For my next two posts I thought I would bring it back to some fundamentals and review the IRR and XIRR return metrics and functions in Excel. These are some of the most commonly used functions and return metrics in commercial real estate and they can be a bit complex to understand for those of us just starting out in real estate finance. The below video and slightly altered video transcription I hope will be a helpful guide and/or refresher.

Are you an Accelerator member? Check out the IRR vs. XIRR discussion, especially as it relates to LP and GP considerations in a monthly equity waterfall here and here. Not yet an Accelerator member? Learn about becoming one.

Video – IRR Fully and Simply Explained

Below is a loose transcription of the video narration altered for this post:

This is the first video and post of a two part series where we dive into the IRR and XIRR functions in Excel. In this first part we will review IRR as part of our A.CRE 101 Series and in the next video we will review the XIRR function as part of our Deep Dive Series. [CORRECTION: This post will actually be within our A.CRE Deep Dive Series as understanding the IRR is a bit more complicated than the basics in our 101 series.]

So to give a high level overview, The IRR and XIRR are important functions used frequently in real estate financial modeling and it’s used to calculate the Internal Rate of Return over an investment hold period. We can think of the Internal Rate of Return as the project’s self-derived discount rate.


Note: If the term discount rate is unfamiliar to you, then I’d suggest to first read my post and watch the video on ‘Using the DCF Method to Value Real Estate’.


When to Use IRR and When to Use XIRR

The traditional IRR function does the job for calculating the internal rate of return over yearly periods, but often times we find ourselves modeling over monthly periods and even quarterly periods to which using the IRR function would be incorrect and therefore we need to utilize XIRR, which again we will cover in Part 2 of this series.

The Internal Rate of Return Formula

Before we get to the excel sheet, let’s do a quick review of the internal rate of return formula, which is really just the present value formula but rather than deriving a present value with a known discount rate, the present value is known and the discount rate, or IRR, in the formula is what needs to be solved for.

 

IRR Formula

Excel will iterate through this formula numerous times until the internal rate of return is solved for.


Using the IRR function in Excel and Verifying Formulaically

Please see the video.

(Click here for Part 2 where we cover the XIRR)

 

 

About the Author: Michael Belasco has over nine years of real estate and construction experience. He currently works for a global real estate investment, development, and asset management firm in San Francisco managing large scale development projects in the city. Michael has both an MBA and Master in Real Estate with a concentration in Real Estate Finance from Cornell University.