The Excel XIRR Function

Related 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, along with the net income payments), to calculate the compounded return, assuming the Net Present Value of the investment is zero.

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.

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.

Basic Description

The Excel XIRR function returns the Internal Rate of Return for a supplied series of cash flows (ie. a set of values, which includes an initial investment value and a series of net income values, which are not necessarily periodic).

The format of the function is :

XIRR( values, dates, [guess] )

Where the arguments are as follows :

values -

A reference to a range of cells containing the series of cash flows (investment and net income values)

(must contain at least one negative and at least one positive value)
dates -

A series of dates, corresponding to the cash flows

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

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

(This is only a value for Excel to start off working with - Excel then uses an iterative procedure to converge to the XIRR)

Note that the dates should be input as either:

or

Warning: If you attempt to input the dates in text format, there is a chance that Excel may misinterpret them, due to different date systems, or date interpretation settings.

Also, be aware that it is not recommended to input Excel dates as serial numbers, as the serial numbers may vary across different computer systems.


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 in column B.

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. The formulas for the equations are shown in the spreadsheet on the left and the results are shown in the spreadsheet on the right.

 Formulas:
Example of use of the Excel XIRR Function
 Results:
Excel XIRR Function Results

Further information and examples of the Excel Xirr function can be found on 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
or
- the supplied values array doesn't contain at least one negative and at least one positive value
or
- any of the supplied dates precedes the first supplied date
or
- the calculation fails to converge after 100 iterations
#VALUE! - Occurs if any of the supplied dates can't be recognised as dates in Excel
Return to the Excel Financial Functions Page

Return to the List of All Built-In Excel Functions

Valid XHTML 1.0 Transitional Valid CSS!
Disclaimer   Privacy Policy
Copyright © 2008-2013 ExcelFunctions.net