This post was inspired by a recent question on our real estate financial modeling Accelerator forum. Additionally, Spencer and I frequently get emails asking about this very issue, which is ‘why the IRR (internal rate of return) and XIRR (extended internal rate of return) functions provide different results on the same cash flow’.
Both functions aim to do the same thing in a given proforma, yet the results come out differently when utilizing one or the other. What is the reason for this? And which one should you be using as a more ‘correct’ representation of the return metric?
If you haven’t done so already, I highly recommend you check my previous post on IRR and XIRR, which fully explains these metrics in a very clear and easy to digest manner.
Overview of IRR vs. XIRR in Real Estate Financial Modeling
As a basic primer, both the IRR and XIRR functions aim to figure out the discount rate that should be inserted in the present value formula so that when you discount the future cash flows to the present and add them together, they equal the assumed purchase price in time period zero. The IRR function, however, can only be used on cash flows that are rolled up into annual periods and the XIRR function can be used when there are periods that are differing than annual periods.
In the video below, the aim is to answer both ‘why there is a difference’ and ‘which metric is more appropriate to use’. We do this using a very basic template to walk you through both a conceptual understanding as well as walk through the formulas for both. The template can be downloaded below the video and you can use that to follow along.
Video – Why Your IRR and XIRR are Different
In the following video, I describe the different results that come out of the IRR() function and the XIRR() function in Excel. Specifically I explain how you can have the same cash flow, but differing internal rate of return result depending on the timing of the cash flow and which function you use. Use the link at the bottom of this post to download the source files (template and completed) for this exercise.
Download the Source Files – IRR vs. XIRR Tutorial
To make these files accessible to everyone, they are 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 – similar real estate training exercises sell for $50+). 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.
- Initial release
About the Author: Michael Belasco has over ten 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.