XIRR ( values , dates [ , [ guess ] ] )
Description: Computes the internal rate of return for a schedule of cash flows that is not necessarily periodic. XIRR uses an iterative calculation technique that cycles through the calculation until the result is accurate within 0.000001 percent.
Mathematical Formula:
Using a changing rate (starting with guess), XIRR cycles through the calculation until the result is accurate within 0.000001 percent. The rate is changed until:
where:
di = the ith, or last, payment date.
d1 = the 0th payment date.
Pi = the ith, or last, payment.
Arguments:
|
Name |
Type |
Description |
|
array, reference |
A series of cash flows that corresponds to a schedule of payment dates specified 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 shall have a negative value. All succeeding payments are discounted based on a 365-day year. The series of values shall contain at least one positive and one negative value. | |
|
dates |
A schedule of payment dates that corresponds to the cash flow payments in values. The first payment date indicates the beginning of the schedule of payments. All other dates shall be later than this date, but they can occur in any order. Time information in the date arguments is ignored. | |
|
guess |
number |
An estimate of the result of XIRR. If omitted, it is assumed to be 0.1 (i.e., 10 percent). |
Return Type and Value: number – The internal rate of return for a schedule of cash flows that is not necessarily periodic.
However, if
• Any date in dates is out of range for the current date base value, #NUM! is returned.
• Any date in dates precedes the starting date, #NUM! is returned.
• values and dates contain different numbers of values, #NUM! is returned.
• The calculation has not converged after an implementation-defined number of tries, #NUM! is returned.
[Example: When the cells F2397:J2397contain the dates January 1, 2008; March 1,2008; October 30, 2008; February 15, 2009, and April 1, 2009:
XIRR({-10000,2750,4250,3250,2750},F2397:J2397,0.1) results in 37.34%
end example]