Public Function UnevenInternalRateOfReturn( _
ByVal vValues As Variant _
, ByVal vDates As Variant _
, Optional ByVal vGuess As Variant _
) As Variant
Calculate the Internal Rate of Return for a series of cash flows that may not be periodic. Example: What is the internal rate of return on an investment where $10,000 is invested on 4/1/1984, $6,000 is withdrawn on 1/1/1986, and $7,000 is withdrawn on 1/1/1987? Approximately 12.2094%.
Similar to XIRR, but accepts the arguments differently.
UnevenInternalRateOfReturn(Array(-10000, 6000, 7000), Array(#4/1/84#, #1/1/86#, #1/1/87#)) = 0.122093872492817See also:
XIRR Function (Microsoft Excel)Summary: The internal rate of return is the interest rate received for an investment consisting of payments and receipts that occur at specific dates. This function uses the date that corresponds to each cash flow to determine when the cash flows occurs.
vValues: One dimensional array of values representing the cash flows. The array must contain at least one payment (a negative value) and at least one receipt (a positive value). Every element of this array is examined and considered to be one cash flow, unlike some other Entisoft Tools functions which examine arrays beginning with element #1.
vDates: One dimensional array of values representing the dates on which the corresponding cash flow occurs.
Initial Cash Flow: The earliest cash flow does not necessarily need to be the first element within the vAmts and vDates array. The cash flows and their corresponding dates do not necessarily need to be in chronological order within these arrays.
vGuess: Optional guess of the Internal Rate of Return which is ignored by this function, but has been retained for compatibility with the Excel and Visual Basic versions of this function.
Return value: The return value will be a percentage between -100% (as represented by -1) and 1E12 (1,000,000,000,000). A rate of return of 17%, for example, would be returned as 0.17.
Algorithm: Function uses successive approximation to calculate the Internal Rate of Return. It will return Null if it cannot calculate the result to an accuracy of 2.22044604925031E-16 (D1MACH(4)) within 256 tries. In practice, this seldom happens.
v2.0 Addition: This function is new to this version of Entisoft Tools.
Copyright 1996-1999 Entisoft
Entisoft Tools is a trademark of Entisoft.