The MIRR (Modified Internal Rate of Return) function in Excel is a powerful tool that helps analysts evaluate the attractiveness of an investment by calculating the internal rate of return, factoring in different financing rates for cash inflows and outflows. This function is particularly useful for understanding the profitability of projects with varying cash flows, allowing for better investment decision-making.

Syntax

MIRR(values, finance_rate, reinvest_rate)

• values: An array or reference to cells containing the cash flows associated with the investment. The first cash flow is assumed to be an outflow (negative value).
• finance_rate: The interest rate you pay on the cash outflows (the cost of financing).
• reinvest_rate: The interest rate you earn on cash inflows (the return on reinvested cash flows).

Example #1

=MIRR(B1:B5, 0.05, 0.08)

This formula calculates the MIRR for the cash flows in cells B1 to B5, with a finance rate of 5% and a reinvestment rate of 8%. The result might be 6.75%, indicating the adjusted rate of return accounting for varying financing conditions.

Example #2

=MIRR(A2:A6, 0.04, 0.06)

This function evaluates the cash flows in A2 to A6, applying a finance rate of 4% and a reinvest rate of 6%. An example result could be 5.22%, showing the profitability of the investment after considering cash flow specifics.

Example #3

=MIRR(C1:C10, 0.03, 0.09)

Here, the MIRR is calculated for cash flows in C1 to C10, with a financing cost of 3% and an expected reinvestment gain of 9%. A possible outcome is 7.15%, reflecting the overall return despite variable cash flow handling.

Error handling

• NUM! This error occurs if the values provided do not lead to a valid result. Ensure the cash flow values are correct and consistent.
• VALUE! This indicates that one or more of the arguments are not numbers. Check that the finance rate and reinvest rate are in numeric format.
• DIV/0! Indicates division by zero, often due to insufficient cash flows leading to an undefined result. Verify that at least one positive cash flow exists.

Conclusion

The MIRR function is essential for investors and financial analysts looking to assess the viability of investment projects with complex cash flow scenarios. By accounting for differing financing and reinvestment rates, MIRR provides a more realistic metric for decision-making than traditional IRR calculations. Understanding how to properly utilize this function can significantly enhance financial analysis capabilities, contributing to more informed investment strategies.