What is the XIRR Function?
The XIRR Function in Excel returns the internal rate of return (IRR) for an irregular series of cash flows – i.e. received on non-periodic dates.
How to Use the XIRR Function in Excel
The XIRR function in Excel computes the internal rate of return (IRR), which refers to the compounded rate of return on a specific investment.
In other words, the internal rate of return (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:
- Range of Dates
- Range of Cash Inflows/Outflows
XIRR Function Formula
In Excel, the XIRR formula is as follows:
- Range of Cash Inflows / Outflows ➝ The range of cash inflows/outflows refers to the array of cash flow values
- Range of Dates ➝ 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: What is the Difference?
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.
For XIRR, the effective annual rate is returned with daily compounding, while the IRR function assumes a stream of equally spaced cash flows.