MIRR function

The MIRR function in Excel calculates the Modified Internal Rate of Return (MIRR) for a series of cash flows, considering both the cost of investment (finance rate) and the reinvestment rate for future cash flows. The MIRR provides a more accurate measure of an investment’s profitability than the traditional IRR because it accounts for different rates of return for both inflows (reinvestment) and outflows (cost of investment).

Syntax

MIRR(values, finance_rate, reinvest_rate)

Parameters

• values: An array or reference to cells that contain the series of cash flows. This must include at least one negative value (outflow) and one positive value (inflow).
• finance_rate: The interest rate used for financing the investment (i.e., the cost of borrowing or the cost of investment).
• reinvest_rate: The interest rate used to reinvest the positive cash flows (i.e., the return on reinvestment).

How It Works

• The MIRR function calculates the internal rate of return on an investment, but it adjusts for the fact that the reinvestment rate may differ from the rate at which the investment was financed.
• Unlike the traditional IRR function, which assumes that both inflows and outflows are reinvested at the same rate (the IRR), the MIRR function allows for a distinction between the cost of financing (the rate at which negative cash flows occur) and the reinvestment rate (the rate at which positive cash flows are reinvested).

The formula for MIRR is:

$$MIRR = \left( \frac{FV(\text{positive cash flows}, \text{reinvestment rate})}{PV(\text{negative cash flows}, \text{finance rate})} \right)^{1/n} – 1$$

Where:

• $FV$ is the future value of the inflows (reinvested at the reinvestment rate).
• $PV$ is the present value of the outflows (discounted at the finance rate).
• $n$ is the total number of periods.

Example

Let’s say you have the following cash flows and rates:

• Cash Flows: -$10,000 (initial investment), $3,000 (year 1), $4,000 (year 2), $5,000 (year 3)
• Finance Rate: 8% (cost of investment)
• Reinvestment Rate: 6% (return on reinvested cash flows)

To calculate the MIRR of this investment:

=MIRR(A1:A4, 8%, 6%)

Where:

• A1:A4 is the range that contains the cash flows.
• 8% is the finance rate (the cost of investment).
• 6% is the reinvestment rate (the return on reinvested cash flows).

Explanation of the Example:

• The cash flows range from an initial investment of -$10,000 to positive cash inflows of $3,000, $4,000, and $5,000 over the next three years.
• The finance rate is the rate at which the initial investment is financed (8%).
• The reinvestment rate is the rate at which the cash inflows are assumed to be reinvested (6%).

The MIRR function will return the Modified Internal Rate of Return, which takes into account the different rates for financing and reinvestment.

Important Notes

• The MIRR function assumes that the negative cash flows occur at the beginning of each period, and the positive cash flows are reinvested at the reinvestment rate.
• If the finance rate and reinvestment rate are the same, the MIRR will be identical to the IRR.
• MIRR is often preferred over IRR because it provides a more realistic measure of profitability, especially when there is a difference between the cost of investment and the return on reinvestment.

Summary

The MIRR function in Excel calculates the Modified Internal Rate of Return (MIRR) for a series of cash flows, accounting for both the cost of financing and the reinvestment rate for positive cash flows. This gives a more accurate and practical measure of an investment’s profitability, making it a useful tool in financial analysis and decision-making.