Description Computes the internal rate of return for an investment with flexible periods.
Syntax XIRR (values, dates [, guess])
| Argument | Description |
|---|---|
| values | A range reference or array constant containing a series of payments to the investment. The first value in the range or array constant is the cost at the beginning of the investment; it is usually negative or 0. Positive values represent payments. Empty cells are evaluated as 0. The function requires at least one negative and at least one positive value in values. You may use a series of positive and negative values to show payments and costs. |
| dates | A range reference or array constant containing the dates each of the payments in values was made. The first date in the range or array constant is the initial investment date. All the other dates must be later than the initial date, but need not be in chronological order. All dates are truncated to integers.There must be the same number of potential values in dates as in values. |
| [guess] | Optional. An estimate of the rate of return. If you omit this argument, 0.1 or 10% will be used. Use this argument when the function returns the #NUM! error (see below). |
Remarks XIRR calculates rate of return similarly to IRR, only XIRR allows you to enter the exact dates of the payments. This may be useful in cases where, for example, the institution offering the investment is writing a contract that sets down the exact payment schedule.
All computations are based on a 365-day year.
Unlike other investment functions, XIRR may have a negative rate result.
This is an iterative function, which means that Formula One for Java will attempt to calculate the result over and over to a finer and finer estimate until it reaches a stable value. If after 100 tries the function still has not found a stable value, it returns the #NUM! error. In this case, you can re-enter the function with an estimate in the guess argument that may help the iteration converge. The guess argument is especially helpful if the data in values alternates between positive and negative values.
Equation
...where di is the ith, or last, payment date, d0 is the 0th payment date, and Pi is the ith, or last, payment. The software iterates to a solution to this equation.
Example This function returns 0.024851, or just above 2%:
