XIRR Function in Excel: Explained
In this article, you will learn what the XIRR formula in Excel does and how to use it.
What does the XIRR formula in Excel do?
The XIRR function in Excel is used to calculate the internal rate of return (IRR) for a series of cash flows that occur at irregular intervals. It considers both the timing and the amount of cash flows to determine the rate of return. The XIRR function calculates the rate of return that makes the net present value (NPV) of the cash flows equal to zero.
How is the XIRR Function in Excel different from the IRR function?
The XIRR function and the IRR function in Excel are related in terms of the both functions calculate the rate of return that makes net present value of the expected cashflow equal to zero but serve different purposes and operate on different types of cash flow data.
The IRR (Internal Rate of Return) function in Excel is used to calculate the internal rate of return for a series of cash flows that occur at regular intervals. It assumes that the cash flows occur at regular intervals, such as annually or monthly, with equal time periods between them.
On the other hand, the XIRR (Extended Internal Rate of Return) function is used to calculate the internal rate of return for a series of cash flows that occur at irregular intervals. It accommodates cash flows that may not occur at regular intervals and allows for specific dates associated with each cash flow..
How to use the XIRR formula in Excel?
The syntax of the XIRR function is as follows:
values: An array or range of cash flows that occur at different dates.
dates: An array or range of dates corresponding to each cash flow.
guess: An optional argument that represents the estimated rate of return. It is used as a starting point for the calculation.
Case study: XIRR
Suppose your company is evaluating an investment project that involves an initial investment and several expected cash flows at irregular intervals. You want to calculate the internal rate of return (IRR) for this project to assess its potential profitability. Here's how you can utilize the XIRR function in Excel:
Step 1: Gather the cash flow information: Compile a list of cash flows associated with the investment project, including their respective amounts and dates.
Step 2: Set up the data in Excel: Create a table with two columns - "Date" and "Cash Flow." Enter the dates in the Date column and the corresponding cash flow amounts in the Cash Flow column.
Step 3: Use the XIRR formula as follows to calculate the internal rate of return.
Step 4: Interpret the internal rate of return: The resulting value from the XIRR formula represents the internal rate of return for the investment project. It indicates the rate of return that would make the net present value of the cash flows equal to zero.
A higher internal rate of return suggests a more profitable investment opportunity, while a lower internal rate of return indicates a less attractive investment (as long as the expected return rate is higher than a required capital cost). If the calculated internal rate of return is lower than a capital cost, the investment doesn’t make sense. Also, you need to think about other factors, such as other investment opportunities and the required period for the specific investment project. Anyway, by utilizing the XIRR function in Excel, Company XYZ can understand the internal rate of return for an investment project with cash flows occurring at irregular intervals. This helps them evaluate whether the project is profitable and make informed decisions about allocating their resources.
