Public Function ModifiedInternalRateOfReturn( _
ByVal vValues As Variant _
, ByVal vFinanceRate As Variant _
, ByVal vReinvestRate As Variant _
) As Variant
Calculate the Internal Rate of Return for a series of periodic cash flows, when the payments and receipts are financed at different rates. 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%.
ModifiedInternalRateOfReturn(Array(-10000, 4000, 10000), .07, .05) = 0.191637528781298Example: 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%.
ModifiedInternalRateOfReturn(Array(10000, -4000, -10000), .07, .05) = -0.0598244062629451See the ModifiedInternalRateOfReturnVerify Subroutine for more examples of this function.
MIRR Function (Visual Basic)
MIRR Function (Microsoft Excel)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.
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.
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.