Deep Dive: The IRR and XIRR – Fully and Simply Explained (Part 2)
Part 2: The XIRR
(Click here for Part 1 where we cover the IRR)
This is Part 2 of our A.CRE Deep Dive – 2-part mini-series on the IRR and XIRR return metrics and functions in Excel. As stated in Part 1, 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? Click here to learn how return metrics such as the IRR are used, in practice, to arrive at an appropriate acquisition price. Not yet an Accelerator member? Consider becoming one.
Video – XIRR Fully and Simply Explained
Below is a loose transcription of the video narration altered for this post:
This is Part 2 of our A.CRE Deep Dive IRR and XIRR Series where in the first part we explain the basics of the IRR the formula and how it works. We are going to pick up where we left off and build from the first video and post to explain the XIRR function, so if you haven’t checked out Part 1, please do so now.
Review: The IRR Formula
Reminder from Part 1: 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 in Part 1, 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.
For a continued explanation of the exponent using Excel, how to use excel’s =XIRR() function, and how to check the XIRR function formulaically, please continue with the additional content in the video.
Frequently Asked Questions about the XIRR in Real Estate Financial Modeling
What is XIRR and how does it differ from IRR?
XIRR is a financial function in Excel that calculates the internal rate of return for a schedule of cash flows that are not necessarily periodic. Unlike IRR, which assumes evenly spaced cash flows (typically annually), XIRR accommodates irregular time periods between cash flows.
Why do we need to use XIRR instead of IRR in real estate models?
In real estate, cash flows often occur at irregular intervals. IRR assumes one-year periods between cash flows, which leads to inaccuracies. XIRR adjusts for actual dates, making it the appropriate tool when cash flow timing isn’t uniform.
How does XIRR work mathematically?
XIRR modifies the standard IRR formula by changing the exponent. It uses a fraction where the numerator is the number of days from the initial date to the cash flow date, and the denominator is 365 (days in a year), thus accurately discounting based on real time.
What is the exponent change in the XIRR formula?
Instead of using integer periods (1, 2, 3…), XIRR uses a time-based exponent:
(
Days from Start Date
365
)
(
365
Days from Start Date
)
This fractional exponent reflects the precise timing of each cash flow relative to the start date.
What is required to use the XIRR function in Excel?
To use XIRR in Excel, you need two arrays:
An array of cash flow amounts (including the initial investment as a negative value), and
A corresponding array of actual dates for each cash flow.
Example: =XIRR(values, dates)
Can XIRR handle cash flows spaced less than one year apart?
Yes. XIRR was designed specifically to handle cash flows spaced at irregular intervals, including periods shorter than a year, such as monthly or quarterly.
How accurate is the XIRR compared to manual calculations?
XIRR is highly accurate and generally preferred over manual IRR calculations when cash flows are irregular. As explained in the video and post, using the adjusted exponent with date-based timing produces a more precise return.
When should you avoid using XIRR?
XIRR is not necessary if all cash flows occur on an annual basis with evenly spaced timing. In such cases, the simpler IRR function is appropriate and easier to use.
