Function Math Financial Class

UnevenInternalRateOfReturn

Calculate the Internal Rate of Return for a series of cash flows that may not be periodic. Similar to XIRR , but accepts the arguments differently.

Signature (Visual Basic / VBA)

Public Function UnevenInternalRateOfReturn( _
      ByVal vValues As Variant _
    , ByVal vDates As Variant _
    , Optional ByVal vGuess As Variant _
    ) As Variant

Code Example

    UnevenInternalRateOfReturn(Array(-10000, 6000, 7000), Array(#4/1/84#, #1/1/86#, #1/1/87#)) = 0.122093872492817

Parameters

Parameter Description
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.
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.

Notes & Version History

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%.
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.
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.
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.