Syntax

MIRR(ValueArray(),FinanceRate,ReinvestRate)

Description

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

Comments

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.

The MIRR function requires the following parameters:

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).

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.

Example

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.

Const crlf = Chr$(13) + Chr$(10)

Sub Main()
  Dim valu#(12)
  valu(1) = -800                      'Initial investment
  msg1 = valu(1) & ", "
  For x = 2 To 5
    valu(x) = 100 + (x * 2)            'Incomes months 2-5
    msg1 = msg1 & valu(x) & ", "
  Next x
  For x = 6 To 12
    valu(x) = 100 + (x * 10)           'Incomes months 6-12
    msg1 = msg1 & valu(x) & ", "
  Next x
  retrn# = MIRR(valu,.1/12,.09/12)      'Note: normalized annual rates

  msg1 = "The values: " & crlf & msg1 & crlf & crlf
  MsgBox msg1 & "Modified rate: " & Format(retrn#,"Percent")
End Sub

See Also

Fv (function); IRR (function); Npv (function); Pv (function).

M