The Excel XIRR function is a financial function that returns the internal rate of return (IRR) for a series of cash flows that occur at irregular intervals.
Syntax:= XIRR (values, dates, [guess])
The XIRR function syntax has the following arguments:
- Values Required. A series of cash flows that corresponds to a schedule of payments in dates. The first payment is optional and corresponds to a cost or payment that occurs at the beginning of the investment. If the first value is a cost or payment, it must be a negative value. All succeeding payments are discounted based on a 365-day year. The series of values must contain at least one positive and one negative value.
- Dates Required. A schedule of payment dates that corresponds to the cash flow payments. Dates may occur in any order. Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text. .
Guess Optional. A number that you guess is close to the result of XIRR.
Example: Let’s look at some Excel XIRR function examples and explore how to use the XIRR function as a worksheet function in Microsoft Excel:
In the spreadsheet below, the cashflow for an investment is shown in cells B2-B7. The initial investment of $2,000,000 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.
Based on the Excel spreadsheet above, the following XIRR examples would return:
- Numbers in dates are truncated to integers.
- XNPV and XIRR are closely related. The rate of return calculated by XIRR is the interest rate corresponding to XNPV = 0.
- Dates should be entered as references to cells containing dates or values returned from Excel formulas.
- #NUM! error – Occurs if either:
- The values and dates arrays are of different lengths;
- The given arrays do not contain at least one negative and at least one positive value;
- Any of the given dates precede the first date provided;
- The calculation fails to converge after 100 iterations.
- #VALUE! error – Occurs when either of the dates given cannot be recognized by Excel as valid dates.