XIRR vs. IRR - What's the Difference?

XIRR vs. IRR

Both XIRR and IRR are Excel functions used to find the IRR of a project. But what is the difference between them? First, we need to understand what the Internal Rate of Return (IRR) is and what it is used for. The Internal Rate of Return is a discount rate that makes the net present value (NPV) of a project zero. The IRR is used in analyzing investments that involve multiple cash investments and distributions over the life of a project. The IRR provides an annualized rate of return.

IRR Calculator Excel

So what is the difference between the XIRR formula and the IRR formula in Excel? The IRR formula assumes there are equal time periods of a year between the cash flows of a project. The IRR formula only requires you to select the series of cash flows to calculate the IRR. On the other hand, the Excel XIRR formula is much more flexible and is based on the dates of the cash flows for the project. The XIRR calculates based on the actual number of days in a period over 365 days. The XIRR formula requires two inputs: the cash flows and the dates for each cash flow.  

IRR Formula

=IRR (values, [guess])

Values: The Values parameter is required and is the cash flows invested and distributed for the investment. Investments are displayed as negative numbers, while distributions are displayed as positive numbers.

Guess: The guess parameter is optional and usually is not needed. By default, the guess is .1 or 10% if not entered, and the calculation will run up to 20 iterative calculation cycles until an answer is reached. If you have a large negative result, you may need to change the guess to -0.1. Also, sometimes there can be multiple correct IRR answers so the guess helps Excel find the most correct result.

XIRR Formula

=XIRR (values, dates, [guess])

Values: The Values parameter is required and is the cashflows invested and distributed for the investment. Investments are displayed as negative numbers, while distributions are displayed as positive numbers.

Dates: The Dates parameter is required and are the dates of each of the respective cash flows. This input needs to be in one of the date formats in Excel.

Guess: The guess parameter is optional and usually is not needed. By default, the guess is .1 or 10% if not entered, and the calculation will run up to 100 iterative calculation cycles until an answer is reached. Similar to the IRR formula, if you have a large negative result, you may need to change the guess to -0.1. Also, sometimes there can be multiple correct IRR answers so the guess helps Excel find the most correct result.

IRR vs. XIRR in Waterfall Calculations

The decision between using the IRR versus the XIRR function can be very significant in waterfall calculations. You should check your JV Agreements and other legal documents to see if the XIRR or IRR function is specifically required. If it is not specified, then the XIRR function is preferred, especially if you need to calculate preferred returns to a specific day. Mistakenly using the wrong function can result in vastly different distributions of cash flows than intended.

IRR vs. XIRR

So what formula should you use? We prefer to use the XIRR formula and use this formula in all our Top Shelf Models. This formula is more flexible, and we look at cash flows on a month to month basis. To learn more about the XIRR formula, click here. Since the Internal Rate of Return is a calculation that factors in the time value of money, we recommend using this formula since it is more accurate and accounts for exact timing. Very rarely do cashflows occur on an annual basis so the IRR formula is not as flexible in a practical setting.

---

About the Analyst

Brittany Martin is TSM’s Vice President who has developed real estate financial models for an extensive range of property types. She specializes in hotel, land, and storage models. Please reach out to her if you have any questions on the XIRR vs. IRR formula or if she can help you with your modeling needs.