Excel MIRR Function: Syntax, Examples & Tips

values:
An array or range of cells that represent the cash flows for the investment, including both negative values (cash outflows) and positive values (cash inflows).

finance_rate:
The interest rate paid on the funds used in the investment, expressed as a decimal.

reinvest_rate:
The interest rate earned on reinvested cash flows, also expressed as a decimal.

What is a MIRR Formula in Excel?

The MIRR function in Excel is a financial tool that calculates the Modified Internal Rate of Return for a series of cash flows. Unlike the standard Internal Rate of Return (IRR), which assumes reinvestment of cash flows at the same rate, MIRR accounts for the cost of financing and the reinvestment rate, providing a more accurate reflection of an investment's profitability.

In simpler terms, the MIRR function helps you evaluate the profitability of an investment by considering both the cost of capital and the returns from reinvested cash flows.

A Practical Example

Imagine you are evaluating a project with the following cash flows over a five-year period:

Cash Flow Table:

You want to calculate the MIRR for this investment, assuming a finance rate of 10% and a reinvestment rate of 12%.

MIRR Formula

To calculate the MIRR for the cash flows, you would use the MIRR formula as follows:

=MIRR(B2:B7, 10%, 12%)

Breakdown of the Formula:

• B2:B7: This is the range of cash flows, including the initial investment (negative value) and subsequent returns.
• 10%: This is the finance rate, representing the cost of borrowing or the cost of capital.
• 12%: This is the reinvestment rate, representing the rate at which cash flows are reinvested.

Result of the Formula

When you apply the formula, it calculates the MIRR based on the provided cash flows, finance rate, and reinvestment rate. The output would be a percentage that represents the effective annual return of the investment.

Here, the MIRR indicates that the investment yields an effective annual return of approximately 11.36%.

Why Use MIRR?

The MIRR function is particularly useful for investors and financial analysts who want a more realistic measure of an investment's profitability. It allows for better decision-making by considering both the cost of financing and the potential returns from reinvested cash flows.

Key Takeaways:

• MIRR: Calculates the Modified Internal Rate of Return for a series of cash flows, accounting for financing and reinvestment rates.
• Realistic Investment Evaluation: Provides a more accurate measure of profitability compared to standard IRR.
• Common Use Cases: Ideal for capital budgeting, project evaluation, and investment analysis where cash flow timing and rates are critical.

Understanding how to use the MIRR function can significantly enhance your financial analysis capabilities in Excel, enabling you to make informed investment decisions.

Happy calculating!