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.
|values||A range of cells that represent the series of cash flows|
|dates||A series of dates corresponding to the cash flows
|[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.
|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)|
|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 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:
- 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.