XIRR vs IRR: What’s the difference?

XIRR and IRR are used to find the IRR of a project. But what is the difference between them?

The XIRR and IRR functions in Excel are nearly identical besides one critical difference: timing assumptions.

I’ll explain the differences here and which one you should be using in your underwriting model.

IRR

Internal Rate of Return is the discount rate that sets the Net Present Value (NPV) of all future cash flows of an investment to zero.

If the NPV of an investment is zero, that doesn’t mean it’s a good or bad investment, it just means you will earn the IRR (discount rate) as your rate of return.

The Excel formula assumes there are equal time periods of a year between the cash flows of a project.

XIRR

XIRR is an Excel formula that calculates the Internal Rate of Return (IRR) for a series of cash flows that is not in annual intervals.

You will need two inputs for the XIRR: the cash flows and the dates for each cash flow.

Below you can see the differences between the IRR and XIRR calculations:

Conclusion

The IRR formula assumes there are equal time periods of a year between the cash flows of a projectThe IRR formula only requires you to select the series of cash flows to calculate the IRR.

The XIRR formula is much more flexible and is based on the dates of the cash flows for the project. It is based on the actual number of days in a period over 365 days.

Your underwriting model should have a monthly pro forma. Your annual proforma is nothing but a sum of the monthly view. Therefore, we always look at cash flows on a month to month basis.

Therefore, I recommend using the XIRR formula since it is more accurate and accounts for exact timing of cash flow. Very rarely do cashflows occur on an annual basis so the IRR formula is not as accurate in a real world scenario.

We use the XIRR calculation in our Next Level Value Add Model. This is the most accurate way to calculate IRR in commercial real estate.