Function Math Financial Class

InternalRateOfReturn

Calculate the Internal Rate of Return for a series of periodic cash flows.

Signature (Visual Basic / VBA)

Public Function InternalRateOfReturn( _
      ByVal vAmts As Variant _
    , Optional ByVal vGuess As Variant _
    ) As Variant

Code Example

    InternalRateOfReturn(Array(-10000, 2000, 4000, 6000)) = 0.0820826354830341

Parameters

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

Notes & Version History

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%. See the InternalRateOfReturnVerify Subroutine for more examples of this function.
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.
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.