Related Function:

The 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, which are not necessarily periodic.

Syntax

=XIRR(values,dates,[est_irr])

Arguments

Argument Description
values A range of cells that represent the series of cash flows
dates A series of dates corresponding to the cash flows

  The first date is the start of the loan/investment period
  Subsequent dates refer to the income values
[est_irr] Optional. It is your guess at the internal rate of return. If this parameter is omitted, it assumes an [est_irr] of 0.1 or 10%

Note: The dates arguments should be input as either:

  • References to cells containing dates, or
  • Dates returned from formulas

If you attempt to input the date arguments as text, Excel may misinterpret them, due to different date systems, or date interpretation settings.

Warning: Although you can input the date arguments as date serial numbers, this is not recommended as date serial numbering does vary across different computer systems.

Examples

  A B C D
1 Values Dates    
2 -$15,000.00 01-Jan-2013 Initial investment  
3 $2,500.00 01-Feb-2013 Income (period 1)  
4 $3,000.00 03-Mar-2013 Income (period 2)  
5 $4,225.00 01-Apr-2013 Income (period 3)  
6 $2,600.00 01-Jun-2013 Income (period 4)  
7 $3,125.00 10-Oct-2013 Income (period 5)  
8        
9 Formula Result Notes
10 =XIRR(B2:B5,C2:C5) -89.1% The internal rate of return after 3rd period
11 =XIRR(B2:B7,C2:C7) 8.70% The internal rate of return after 5th period (0.087015077)

Common Function Error(s)

Problem What went wrong
#VALUE! Occurs if any of the supplied dates are not recognized as dates
#NUM! Occurs if either:

  the supplied values and dates arrays have different lengths
  the supplied values array doesn’t 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

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

This calculation uses a schedule of payments (including an initial investment, along with the 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:

    \[   \sum_{j=1}^N \frac{P_j}{(1 + rate)^\frac{(d_j - d_1)}{365} } = 0  \]

where:
  • Pj is the j’th payment
  • dj is the j’th payment date
  • d1 is the 0’th payment date

The difference between the IRR calculation and the XIRR calculation is that the IRR assumes that the cash flows are periodic, whereas the XIRR calculation is for a series of cash flows that are not necessarily periodic.