In the dynamic realm of financial analysis within Excel, the XIRR function stands out as a versatile tool for calculating the internal rate of return (IRR) of non-periodic cash flows. Unlike its counterpart, the IRR function, XIRR does not demand a regular payment schedule, making it an ideal choice for scenarios with irregular intervals. In this comprehensive guide, we will unravel the intricacies of using the XIRR function in Excel, delving into syntax, examples, and valuable tips for seamless application.

Supported versions

All Excel versions

Syntax

XIRR(values, dates, [guess])

Arguments

values	A series of cash flows. Cash flows must contain at least one positive value and one negative value. Payments are expressed as negative values and income as positive.
dates	Date series of cash flows. The first payment date indicates the beginning of the schedule of payments. All other dates must be later than this date, but they may occur in any order.
[guess]	Optional. Your estimation for expected internal rate of return. Default is 0.1 (10%).

Examples

Note that we've used named ranges in this example to make the formulas easier to read. This is not required.

Example 1

=XIRR(cash_flow,time_periods)

formula demonstrates a use case for the XIRR function. It calculates an investment's internal rate of return after four irregular intervals. Subsequent payments are discounted based on a 365-day calendar year. Payments are expressed as negative values and income as positive.

When the [guess] argument is omitted, it will be defaulted as 0.1 and will start the iterations from this value. The XIRR calculates iterations until it reaches a result within 0.00001% accuracy.

Example 2

=XIRR(cash_flow,time_periods,guess)

formula is an example of using the [guess] argument in the XIRR function. You can see the result is equal to the sample that omits the [guess] parameter. In most cases you do not need to enter a [guess] argument. Use different values if you get a #NUM! error.

Download Workbook

Tips

The XIRR function can work with cash flows occurring at irregular intervals. Use the IRR function for periodic cash flows.
The rate of return is calculated by XIRR is the interest rate corresponding to a 0 (zero) net present value (NPV). Using the return of the XIRR in the XNPV function returns 1.79E-09, which is equal to 0 (zero) within the accuracy of the XIRR function. Sample formula:

=XNPV(XIRR(cash_flow,time_periods,guess),cash_flow,time_periods)
The function ignores text, logical values, or empty cells.

Issues

#NUM!

If the function cannot find any results within 0.00001% accuracy, or in 100 iterations, you will get a #NUM! error. Try changing the [guess] parameter.
Any date value can entered that is before than the starting date.
References of values and date series may have different sizes.
Cash flows must contain at least one positive value and one negative value.

#VALUE!

If an argument is non-numeric, you will get a #VALUE! error.
If you entered invalid dates, you will get a #VALUE! error.

Mastering the XIRR function in Excel empowers financial analysts and enthusiasts to navigate complex investment scenarios with ease. By providing a flexible solution for calculating internal rates of return amidst non-standard cash flow intervals, Excel's XIRR function becomes an indispensable asset. As demonstrated in the examples, understanding the syntax, incorporating optional arguments like [guess], and addressing potential issues such as #NUM! and #VALUE! errors ensures accurate and efficient financial analysis. Elevate your proficiency in Excel and unlock the full potential of financial modeling with the powerful XIRR function.