XIRR Function

In This Article

• What is the purpose of the XIRR Excel function?
• In Excel, how is the XIRR function different from IRR?
• What are the inputs required in the XIRR formula?
• Why is XIRR recommended over the IRR function?

XIRR Excel Formula

The internal rate of return (IRR) refers to the compounded rate of return on a specific investment.

In other words, the IRR is the interest rate that an initial investment must have grown by to reach the value at exit – i.e. from the beginning value to the ending value.

The two required inputs are:

1. Range of Dates
2. Range of Cash Inflows/Outflows

In Excel, the XIRR formula is as follows:

XIRR Excel Formula

• XIRR Function: “=XIRR (Range of Cash Inflows/Outflows, Range of Dates)”

The range of cash inflows/outflows refers to the array of cash flow values, whereas the range of dates represents the matching dates (i.e. mm/dd/yy) of when the cash movement occurs.

In order for the formula to work properly, you must enter the cash inflows and outflows directly in line with the corresponding date – if not, the calculated IRR will be incorrect.

Additionally, the range of cash values must contain at least one positive and one negative number.

Under the context of investing, the initial investment should be entered as a negative figure since it represents an outflow of cash.

• Cash Outflows ➝ Negative Number
• Cash Inflows ➝ Positive Number

Inflows of cash could potentially include dividends received during the holding period and the sale proceeds on the date of exit.

XIRR vs IRR Excel Function

The XIRR function in Excel is more practical than the IRR function due to the increased flexibility of not being restricted to annual periods.

Unlike the IRR function, XIRR can handle irregular cash flows, which reflects reality more accurately.

The drawback to the IRR function is that Excel assumes each cell is separated by exactly twelve months, which is rarely the case.

IRR Excel Formula

• IRR Function: “=IRR (Range of Cash Inflows/Outflows)”

For XIRR, the effective annual rate is returned with daily compounding, while the IRR function assumes a stream of equally spaced cash flows.