FinMIRR

Syntax

FinMIRR(ValueArray(),FinanceRate,ReinvestRate)

Description

Returns a Double representing the modified internal rate of return for a series of periodic payments and receipts.

The modified internal rate of return is the equivalent rate of return on an investment in which payments and receipts are financed at different rates. The interest cost of investment and the rate of interest received on the returns on investment are both factors in the calculations.

FinanceRate and ReinvestRate should be expressed as percentages. For example, 11 percent should be expressed as 0.11.

To return the correct value, be sure to order your payments and receipts in the correct sequence.

Parameter	Description
ValueArray()	Array of Double numbers representing the payments and receipts. Positive values are payments (invested capital), and negative values are receipts (returns on investment). There must be at least one positive (investment) value and one negative (return) value.
FinanceRate	Double representing the interest rate paid on invested monies (paid out).
ReinvestRate	Double representing the rate of interest received on incomes from the investment (receipts).

See Also

FinFv, FinIRR, FinNpv, FinPv.

Example

	
Sub Main
'This example illustrates the purchase of a 
'lemonade stand for $800 financed with money 
'borrowed at 10%. The returns are estimated to
'accelerate as the stand gains popularity. The 
'proceeds are placed in a bank at 9 percent 
'interest. The incomes are estimated (generated)
'over 12 months. This program first generates the 
'income stream array in two For...Next loops, and 
'then the modified internal rate of return is
'calculated and displayed. Notice that the annual 
'rates are normalized to monthly rates by dividing 
'them by 12.
 
    Dim valu#(12)
    valu(1) = -800 'Initial investment
    msg = valu(1) & ", "
    For x = 2 To 5
        valu(x) = 100 + (x * 2)'Incomes months 2-5
        msg = msg & valu(x) & ", "
    Next x
    For x = 6 To 12
        valu(x) = 100 + (x * 10'Incomes months 6-12
        msg = msg & valu(x) & ", "
    Next x
    retrn# = MIRR(valu,.1/12,.09/12) 
           'Note: normalized annual rates 
 
    msg = "The values: " & vbCrLf & msg & vbCrLf
    MsgBox msg & "Modified rate: " & _
			Format(retrn#,"Percent")
End Sub