Excel XIRR Function: Syntax, Examples & Tips

values:
The array or range of cash flows that correspond to the dates. This includes both positive and negative values, where negative values represent cash outflows and positive values represent cash inflows.

dates:
The array or range of dates that correspond to the cash flows in values. Each date must match the cash flow it relates to, and the dates must be in chronological order.

guess:
An optional numeric value that represents your guess for what the internal rate of return might be. If omitted, Excel uses 0.1 (or 10%) as the default guess.

What is an XIRR Formula in Excel?

The XIRR function in Excel is a financial tool that calculates the internal rate of return for a series of cash flows that occur at irregular intervals. Unlike the standard IRR function, which assumes that cash flows are spaced evenly, XIRR allows for more flexibility by accommodating cash flows that happen at different times.

In simpler terms, the XIRR function helps you determine the profitability of an investment or project by providing the rate of return based on the timing and amount of cash flows.

A Practical Example

Imagine you are evaluating an investment project with the following cash flows and their corresponding dates:

Cash Flow Data Table:

You want to find the internal rate of return for this investment over the specified period.

XIRR Formula

To calculate the internal rate of return for the investment, you would use the XIRR formula as follows:

=XIRR(B2:B6, A2:A6)

Breakdown of the Formula:

• B2:B6: This is the range of cash flows, where negative values represent investments and positive values represent returns.
• A2:A6: This is the range of dates corresponding to each cash flow.

Result of the Formula

When you apply the formula, it calculates the internal rate of return based on the provided cash flows and their respective dates. The output would be a percentage representing the rate of return for the investment.

Here, the internal rate of return is calculated as approximately 12.34%, indicating the profitability of the investment over the specified period.

Why Use XIRR?

The XIRR function is particularly useful for evaluating investments with cash flows that do not occur at regular intervals. It provides a more accurate measure of return, allowing investors and analysts to make informed decisions based on the timing of cash flows.

Key Takeaways:

• XIRR: Calculates the internal rate of return for cash flows occurring at irregular intervals.
• Flexible Cash Flow Analysis: Accommodates varying cash flow timings, providing a more accurate assessment of investment profitability.
• Common Use Cases: Ideal for investment analysis, project evaluation, and any scenario where cash flows do not follow a regular schedule.

Understanding how to use the XIRR function can significantly enhance your financial analysis capabilities in Excel, enabling you to evaluate investments more effectively.

Happy calculating!