MIRR

The MIRR function in Excel calculates the modified internal rate of return for a series of cash flows that may have different reinvestment and finance rates. It is a useful tool in financial analysis for evaluating investment returns under varying conditions.

Syntax

=MIRR(values, finance_rate, reinvest_rate)

Arguments

Argument Required Description
values Yes An array or reference to a range of cells containing cash flows.
finance_rate Yes The interest rate paid on money used in financing the investments.
reinvest_rate Yes The interest rate received on the reinvestment of cash flows.

About

Delve into the world of investment analysis with the MIRR function in Excel. When dealing with cash flows over time, especially where reinvestment rates differ from financing rates, MIRR stands out as a key tool for determining the modified internal rate of return. This metric aids in assessing the profitability of investments considering both incoming and outgoing cash flows, along with reinvestment conditions and financial costs incurred along the way. Essentially, MIRR provides a comprehensive view of the true return on an investment, factoring in varying rates pertinent to reinvestment and financing. By utilizing the MIRR function, you gain insights into the overall performance of your investments, allowing for more informed decision-making in financial matters.

Examples

Suppose you have a series of cash flows from an investment project: -$100, $20, $30, $50, and $40 over five periods. The finance rate is 6%, and the reinvestment rate is 8%. To calculate the MIRR for these cash flows, the formula would be: =MIRR(-100, 20, 30, 50, 40, 0.06, 0.08)

Consider a scenario where an initial investment of $500 generates future cash flows of $100, $150, and $200 at the end of three periods. The finance rate is 5%, and the reinvestment rate is 7%. To find the MIRR for this investment, use the formula: =MIRR(-500, 100, 150, 200, 0.05, 0.07)

Tips & notes

Ensure that the cash flows and rates are appropriately organized when using the MIRR function. The values should be input as an array or reference to a range of cells. Additionally, keep in mind that the finance_rate and reinvest_rate must be consistent with the time periods of the cash flows for accurate calculations.

Common questions

How does the MIRR function differ from the IRR function in Excel?

While both MIRR and IRR are used to evaluate the potential profitability of investments, MIRR considers different reinvestment and finance rates. IRR assumes that all cash flows are reinvested at the same rate, which may not always be realistic in practical financial scenarios.

What does a higher MIRR value indicate?

A higher MIRR value indicates a more favorable return on an investment, considering both reinvestment and finance rates. It suggests that the investment may be more profitable, factoring in the costs and benefits associated with cash flows and reinvestments.

Can the MIRR function handle irregular cash flow patterns?

Yes, the MIRR function in Excel can handle irregular cash flow patterns by accurately calculating the modified internal rate of return based on the provided cash flows, finance rate, and reinvestment rate. It offers flexibility in analyzing investments with varying cash flow timings.