Documentation/Calc Functions/MIRR

    From The Document Foundation Wiki
    Other languages:

    Function name:

    MIRR

    Category:

    Financial Analysis

    Summary:

    Calculates the modified internal rate of return for a series of periodic cash flows. Modified internal rate of return analysis is a financial technique that is often used to assess the likely profitability of a project or investment.

    MIRR assumes that cash flows occur regularly, in equal length time periods. Cash flow values may vary between periods and represent either income (positive) or expenditure (negative). At least one value must be negative and at least one value must be positive.

    MIRR is a modification to IRR and reflects the more realistic assumption that interim positive cash flows are likely to be reinvested with a rate of return different from that of the project or investment. In addition, while the IRR function can produce multiple solutions when the signs of the cash flows change more than once, MIRR calculates just one solution.

    Syntax:

    MIRR(Values; Investment; ReinvestRate)

    Returns:

    Returns a real number, which is the modified internal rate of return for the specified cash flows. The number returned is a percentage and, by default, is formatted as a percentage.

    Arguments:

    Values is a list of real numbers, which are the periodic cash flows. Values may be a specific cell range (for example, "$SourceData.A3:B12"), the name of a named or database range, or an inline array.

    The following points should be noted with regards to the supplied cash flows:

    • Each cash flow should be expressed in the same currency unit.
    • Each cash flow may be positive (income), zero, or negative (expenditure).
    • One cash flow should be supplied for each period during the predicted life of the project or investment. The value 0 should be included for any period with a zero cash flow – if this is omitted, then later cash flows will be discounted incorrectly.
    • MIRR expects cash flows to be supplied in chronological order. Range reference and array arguments are evaluated column-wise starting from the top-left.
    • The order of the cash flows passed to MIRR is important. For example, the formula =MIRR({-200, 200, 300}; 5%; 5%) returns 59.69%, while the formula =MIRR({300, 200, -200}; 5%; 5%) returns 72.65%.

    Investment is a real number (expressed as a percentage, such as 2.5%, or a decimal fraction, such as 0.025), or a reference to the cell containing that number, which represents the cost of borrowing to fund the project or investment. This is expressed as the interest rate per period that is paid to finance the monies represented in negative cash flows.

    ReinvestRate is a real number (expressed as a percentage, such as 2.5%, or a decimal fraction, such as 0.025), or a reference to the cell containing that number, which is the rate of interest earned by reinvesting monies from positive cash flows.

    • If either of the Investment or ReinvestRate arguments are non-numeric, then MIRR reports a #VALUE! error.
    • Cells within the Values argument that are empty or contain text are ignored.
    • If Values does not include at least one positive value and at least one negative value, then MIRR reports an invalid argument error (Err:502).

    Additional details:

    • At a conceptual level, the steps to calculate MIRR are:
    1. Discount all negative cash flows, using the rate given by the Investment argument, to give the present value of those costs at the start of the project or investment.
    2. Compound all positive cash flows, using the rate given by the ReinvestRate argument, to give the future value of those profits at the end of the project or investment.
    3. The difference between the two values calculated in the previous steps gives a total growth. The MIRR is then calculated as the periodic growth rate needed to achieve that total growth.
    • The above steps lead to the following equation:
    [math]\displaystyle{ \text{MIRR}~=~\left( \frac{FV_{profits}}{PV_{costs}} \right)^{\frac{1}{n-1}}~-~1 }[/math]
    where:
    • [math]\displaystyle{ FV_{profits} }[/math] is the sum of the future values (at the end of the project or investment) for all positive cash flows.
    • [math]\displaystyle{ PV_{costs} }[/math] is the sum of the present values (at the start of the project or investment) for all negative cash flows.
    • n is the number of periods for the cash flows.
    • A company would be looking for the MIRR to be greater than its cost of capital before approving a project or investment.

    Examples:

    The following table shows the data used in the first example below.

    Data for example of MIRR function
    A B C D E F G
    1 Year 1 Year 2 Year 3 Investment Rate 5%
    2 Q1 −2,000 250 450 Reinvestment Rate 5%
    3 Q2 100 300 500
    4 Q3 150 350 550
    5 Q4 200 400 600
    Formula Description Returns
    =MIRR(B2:D5; G1; G2) where cells B2:D5 and G1:G2 contain the values shown in the table above. Here the function calculates the modified internal rate of return for twelve quarterly cash flows and returns the value 7.92. Note that the formula =MIRR({-2000, 100, 150, 200, 250, 300, 350, 400, 450, 500, 550, 600}; 5%; 5%) returns the same value. 7.92%
    =MIRR({−5000, 1000, 2000, 3000}; 5%; 8%) Here the function calculates the modified internal rate of return for a series of cash flows. The initial flow is an outflow and this is followed by only cash inflows – such a series, with only one change in the cash flow direction, is sometimes known as a conventional cash flow. In this example, the investment and reinvestment rates appear to have different values; however, the initial negative cash flow needs no discounting and so the actual value of the Investment argument is of no significance. The function returns 8.16. 8.16%
    =MIRR({−10; 20; −10; 20; 20; −30}; 5%; 8%) Here the function calculates the modified internal rate of return for a series of cash flows. There are three outflows and three inflows – such a series, with multiple changes in the cash flow direction, is sometimes known as a non-conventional cash flow. In this example, the investment and reinvestment rates have different values. The function returns 11.12. 11.12%

    Related LibreOffice functions:

    IRR

    NPV

    RATE

    XIRR

    ODF standard:

    Section 6.12.27, part 2

    Related (or similar) Excel functions:

    MIRR