<-- Previous || Up || Next -->

XIRR Function
Math Financial Class

Public Function XIRR( _
    ByRef vAmtsAndDates() As Variant _
    ) As Variant

Calculate the Internal Rate Of Return For uneven cash flows.
Similar to UnevenInternalRateOfReturn, but accepts the arguments differently.

Example: What is the internal rate of return on an investment where $10,000 is invested on 4/1/1984, and $50,675 is withdrawn on 4/1/1994? 17.608828%
    Assuming
       Dim Flow() As Variant
       ReDim Flow(0 To 2, 0 To 2)
       Flow(1, 1) = -10000#
       Flow(2, 1) = #4/1/1984#
       Flow(1, 2) = 50674#
       Flow(2, 2) = #4/1/1994#
    for example
       XIRR(Flow()) = .17608828
See the XIRRSample Subroutine for more examples of this Function.
See also: 
    UnevenInternalRateOfReturn Function
    UnevenNetPresentValue Function
    InternalRateOfReturn Function
    ModifiedInternalRateOfReturn Function
    NetPresentValue Function
    XIRR Function (Microsoft Excel)
vAmtsAndDates: Two dimensional array of Variant values representing a table of cash flows along with the corresponding transaction date. Within this array, each cash flow amount is stored in element vAmtsAndDates(1, X) and the corresponding transaction date is stored in element vAmtsAndDates(2, X) where X represents the number of the cash flow. X must be between 1 (one) and UBound(vAmtsAndDates, 2) (the upper bound of the second dimension of vAmtsAndDates). The array must contain at least one payment (a negative value) and at least one receipt (a positive value).
Initial Cash Flow: This function uses the earliest date within vAmtsAndDates as the day on which to start its calculations. Because of this, the cash flows need not be in chronological order within vAmtsAndDates.
Return value: The return value will be a percentage between -100% (as represented by -1) and MaxDouble (around 1E300). A rate of return of 17%, for example, would be returned as .17, for example.
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 BugFix: This function has been corrected to not overflow when calculating Internal Rates of Return over long periods of time. Also, changed this function to work correctly when the first cash flow is positive and subsequent cash flows are negative (basically, when the cash flows have the opposite signs of those in the example above). Also, added error detection and correction to this function so that it will never generate an error within your program.

 

Copyright 1996-1999 Entisoft
Entisoft Tools is a trademark of Entisoft.