Home » Excel-Built-In-Functions » Excel-Financial-Functions » Excel-Xirr-Function

The Excel XIRR Function

Related Functions:
XNPV Function
IRR Function
Internal Rate of Return

The Internal Rate of Return indicates the profitability of an investment and therefore is commonly used in business, when choosing between investmentments.

This calculation uses a schedule of payments (including an initial investment and a series of net income payments), to calculate the compounded return, assuming the Net Present Value of the investment is zero.

The value of the XIRR is calculated as the value of rate that satisfies the following equation:

Xirr Equation

where Pj is the j'th payment, dj is the j'th payment date and d1 is the 0'th payment date.

Function Description

The Excel XIRR function returns the Internal Rate of Return for a supplied series of cash flows (i.e. a set of values, which includes an initial investment value and a series of net income values) occurring at a series of supplied dates.

Unlike the Excel IRR function, the series of cashflows for the XIRR calculation do not necessarily have to be periodic.

The syntax of the function is:

XIRR( values, dates, [guess] )

Where the arguments are as follows:

values -

An array of values (or a reference to a range of cells containing values) representing the series of cash flows.

These must include at least one negative value (representing outgoing payment) and at least one positive value (representing income).
dates -

A series of dates, corresponding to the supplied values.

The first date is the start of the loan/investment period and the subsequent dates refer to the dates of further outgoing payments or income. Therefore, subsequent dates must be later than the first date.
[guess] -

An initial guess at what the IRR might be. This is an optional argument, which, if omitted, takes on the default value of 0.1 (=10%).

(Note: the [guess] is only a value for Excel to start off working with - Excel then uses an iterative procedure to converge to the XIRR).

Note that Microsoft advises that dates entered into functions should be input as either:

or

Warning: If you attempt to input dates in text format, there is a risk that Excel may misinterpret them, depending on the date system, or date interpretation settings on your computer.


XIRR Function Example

In the spreadsheet below, the cashflow for an investment is shown in cells B2-B7. The initial investment of $100 is shown in cell B2 and the net income over 5 periods is shown in cells B3-B7. Cells C2-C7 show the dates for the cashflows.

The XIRR function in cell D2 shows the calculation of the Internal Rate of Return after 3 periods and the function in cell D4 shows the Internal Rate of Return after 5 periods.

 Formulas:
  A B C D
1   Values Dates XIRR after Period 3:
2 Initial Investment: -$100.00 Jan-01-2016 =XIRR( B2:B5, C2:C5 )
3 Period 1 Income: $20.00 Apr-01-2016 XIRR after Period 5:
4 Period 2 Income: $40.00 Oct-01-2016 =XIRR( B2:B7, C2:C7 )
5 Period 3 Income: $25.00 Feb-01-2017  
6 Period 4 Income: $8.00 Mar-01-2017  
7 Period 5 Income: $15.00 Jun-01-2017  
 Results:
  A B C D
1   Values Dates XIRR after Period 3:
2 Initial Investment: -$100.00 Jan-01-2016 -19.67%
3 Period 1 Income: $20.00 Apr-01-2016 XIRR after Period 5:
4 Period 2 Income: $40.00 Oct-01-2016 9.44%
5 Period 3 Income: $25.00 Feb-01-2017  
6 Period 4 Income: $8.00 Mar-01-2017  
7 Period 5 Income: $15.00 Jun-01-2017  

Note that, in the above example, the initial investment is a negative value (as this is an outgoing payment), and the income payments are represented by positive values.

For further information and examples of the Excel Xirr function, see the Microsoft Office website.


XIRR Function Errors

If you get an error from the Excel Xirr function this is likely to be one of the following:

Common Errors
#NUM! -

Occurs if either:

  • The supplied values and dates arrays have different lengths;
  • The supplied values array does not contain at least one negative and at least one positive value;
  • Any of the supplied dates precedes the first supplied date;
  • The calculation fails to converge after 100 iterations.
#VALUE! - Occurs if any of the supplied dates can't be recognised as valid Excel dates.
Return to the Excel Financial Functions Page
Return to the List of All Built-In Excel Functions