Public Function InternalRateOfReturn( _
ByVal vAmts As Variant _
, Optional ByVal vGuess As Variant _
) As Variant
Calculate the Internal Rate of Return for a series of periodic cash flows. Example: What is the Internal Rate of Return on an investment if $10,000 is invested today, $2,000 is withdrawn one year from today, $4,000 is withdrawn two years from today, and $6,000 is withdrawn three years from today? Approximately 8.208%.
InternalRateOfReturn(Array(-10000, 2000, 4000, 6000)) = 0.0820826354830341See the InternalRateOfReturnVerify Subroutine for more examples of this function.
IRR Function (Visual Basic)
IRR 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 regular intervals. This function uses the order of the values within the array vAmts as the order of payments and receipts.
vAmts: One dimensional array of values representing a table of periodic 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.
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% would be returned as 0.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 Addition: This function is new to this version of Entisoft Tools.
Copyright 1996-1999 Entisoft
Entisoft Tools is a trademark of Entisoft.