Signature (Visual Basic / VBA)
Public Function ModifiedInternalRateOfReturn( _
ByVal vValues As Variant _
, ByVal vFinanceRate As Variant _
, ByVal vReinvestRate As Variant _
) As Variant
Code Examples
ModifiedInternalRateOfReturn(Array(-10000, 4000, 10000), .07, .05) = 0.191637528781298
ModifiedInternalRateOfReturn(Array(10000, -4000, -10000), .07, .05) = -0.0598244062629451
Parameters
| Parameter | Description |
|---|---|
| vValues | 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. |
| vFinanceRate | The interest rate paid to finance the investment. For example, 9% would be represented as 0.09. |
| vReinvestRate | The interest rate received on gains from reinvestment. For example, 11% would be represented as 0.11. |
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 today, $4,000 is withdrawn one year from today, and $10,000 is withdrawn two years from today, assuming that 7% is paid to finance the investment and 5% is received on gains from reinvestment? Approximately 19.1638%. |
| Example | What is the internal rate of return on an loan where $10,000 is borrowed today, $4,000 is repaid one year from today, and $10,000 is repaid two years from today, assuming that the 7% is paid to finance the investment and 5% is received on gains from reinvestment? Approximately -5.9824%. See the ModifiedInternalRateOfReturnVerify Subroutine for more examples of this function. |
| Summary | The modified internal rate of return is the internal rate of return when payments are financed at one rate and receipts are financed at a different rate. This function uses the order of the values within the array vValues 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. |