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.
