Deep Dive: The IRR and XIRR – Fully and Simply Explained
For my next post, 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? 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.
Video – IRR Fully and Simply Explained
Loose transcription of the IRR Fully and Simply Explained Video
In this video, we dive into the IRR and XIRR functions in Excel. 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 valuing real estate using a discounted cash flow model
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 later in this post.
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.
Excel will iterate through this formula numerous times until the internal rate of return is solved for.
Using the XIRR Function in Real Estate
As previously stated, IRR and XIRR 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 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.
Video – XIRR Fully and Simply Explained
Follow along as I explain the functionality of the XIRR() function in Excel.
Review: The IRR Formula
Reminder: The IRR Formula is the Present Value Formula only instead of solving for the present value, we know the present value and we are backsolving for the discount rate (the IRR).
As we said previously, the Present Value Formula can only be used if periods are in one-year increments, so using it for any other periodic payout or earning schedules would be incorrect. So we need to alter this formula to account for payment or earning schedules that are different than one year periods.
Transforming The IRR Formula to The XIRR Formula
So the question is, how do we alter the formula so that when there are payouts at different timing intervals other than a year it can properly solve for the internal rate of return? The answer is that we alter the exponent, which in the normal IRR formula is always commensurate with the year in the referenced period and dictates how the cash is being discounted back.
We alter the exponent to a fraction with the numerator being the total days between the start date, or time period 0, and the current date. Then, for the denominator we use 365, or a year represented in days.
By changing the unit of measure in the denominator of the exponent to a year represented in days allows us to now analyze any type of earnings or payout periods that are irregular as long as they are not split into anything smaller than a day.
Frequently Asked Questions about IRR and XIRR in Real Estate Financial Modeling
What is the IRR and how is it used in real estate?
The IRR (Internal Rate of Return) is used to calculate the return on investment over a hold period in real estate. It is described as “the project’s self-derived discount rate” and is calculated by backsolving the present value formula for the discount rate rather than the present value.
How is the IRR formula derived?
The IRR formula is based on the present value formula. However, instead of solving for present value, the present value is known and Excel iterates to solve for the discount rate that equates future cash flows to that present value.
When should I use IRR versus XIRR in Excel?
Use IRR when cash flows occur at regular, annual intervals. Use XIRR when cash flows occur at irregular intervals (e.g., monthly or quarterly), which is often the case in real estate models.
Why is IRR incorrect for irregular cash flow schedules?
The IRR function assumes that all cash flows are spaced exactly one year apart. When used on irregular schedules, this assumption leads to inaccuracies in the calculated return, making XIRR the more appropriate function.
How does the XIRR function correct the IRR formula?
XIRR adjusts the exponent in the IRR formula. Instead of using whole-year increments, it uses a fractional exponent based on the number of days between cash flow dates and time period 0, divided by 365, allowing accurate discounting for irregular timing.
What are the input requirements for the XIRR function in Excel?
You must provide two arrays: one with the cash flows and another with the corresponding dates. For example: =XIRR(cash_flows, dates). The first cash flow is typically a negative value representing the investment.
Can XIRR handle cash flows spaced less than one year apart?
Yes. XIRR is specifically designed to handle irregularly timed cash flows, including those spaced monthly, quarterly, or on any other irregular basis, as long as they are not split into intervals smaller than a day.
What happens behind the scenes when Excel calculates IRR or XIRR?
Excel performs iterative calculations, running the IRR/XIRR formula many times to find the rate at which the net present value of cash flows equals zero. This iterative solving is automated in Excel.
Why is understanding IRR and XIRR important for real estate professionals?
IRR and XIRR are fundamental to evaluating investment performance and acquisition pricing in commercial real estate. Understanding these metrics allows professionals to accurately assess project returns and communicate results with investors.
