EXCEL XIRR FUNCTION

The XIRR function is classified as a financial function since it computes the Internal Rate of Return (IRR) for a sequence of cash flows that may or may not be periodic by assigning precise dates to each individual cash flow. The primary advantage of utilising the XIRR Excel function is that these irregularly timed cash flows may be correctly predicted. To understand more, see Why XIRR is Always Better Than IRR in Excel Modeling. The XIRR function in financial modelling is useful for assessing the worth of an investment or analysing the viability of a project without monthly cash flows. It aids us in understanding the rate of return on an investment.As a result, it is frequently utilised in finance, particularly when deciding between assets.

Syntax :

=XIRR (values, dates,[guess])

Parameters : 

1. Values (mandatory parameter) – The array of values representing the sequence of cash flows. It can be a reference to a range of cells containing values instead of an array.

2. Dates (mandatory parameter) – It is a list of dates that match to the values supplied. Because the first date is the commencement date and subsequent dates represent future dates of outgoing payments or revenue, subsequent dates should be later than the first date.

3. [guess] (optional argument) – This is an initial estimation of the IRR. If this field is left blank, Excel uses the default value of 10%. For computing XIRR, Excel use an iterative method. XIRR goes through the computation using a changing rate (beginning with [guess]) until the result is correct to 0.000001 percent.

Step By Step Guide of XIRR Function

Example : Assume a project began on January 1, 2018. The project generates cash flows in the middle of the first year, after 6 months, then at the end of 1.5 years, the second year, the third year, and yearly after that. The following information is provided:

XIRR Function

1. Date numbers are reduced to integers.

2. XNPV and XIRR are related terms. The interest rate equivalent to XNPV = 0 is the rate of return determined by XIRR.

3. Enter dates as references to cells holding dates or values returned by Excel formulae.

4. #NUM! error – Occurs if any of following conditions is met:

1. The values and dates arrays are of different lengths;

2. The given arrays lack at least one negative and one positive value;

3. Any of the given dates before the first date provided; or

4. The computation fails to converge after 100 iterations.

5. #VALUE! error – Occurs when any of the dates provided is not recognised as a valid date by Excel.

Excel XIRR Function Template/Exercise

Related Functions

HLOOKUP Function

HLOOKUP Function

Explore Now

VLOOKUP Function

VLOOKUP Function

Explore Now

INDEX Function

INDEX Function

Explore Now

MATCH Function

MATCH Function

Explore Now

DATE Function

DATE Function

Explore Now

IF Function

IF Function

Explore Now

OR Function

OR Function

Explore Now

AND Function

AND Function

Explore Now

RANK Function

RANK Function

Explore Now

SUMIF Function

SUMIF Function

Explore Now

XIRR Function

XIRR Function

Explore Now

WORKDAYS Function

WORKDAYS Function

Explore Now

Share this Content

Excel Functions

Excel Formulas

Excel Data Analytics