How to use MIRR function in Excel?

The MIRR function in Excel is used to calculate the Modified Internal Rate of Return for a series of cash flows, considering the cost of investment and the interest received on reinvestment. It improves on the traditional IRR by allowing for different financing and reinvestment rates. Here’s how to use the MIRR function:

Syntax:

MIRR(values, finance_rate, reinvest_rate)

values: An array or a range of cells that represent the series of cash flows. The series must contain at least one positive and one negative value. Typically, the initial investment will be a negative value, and subsequent cash inflows will be positive.
finance_rate: The interest rate you pay on the money used in the cash flows (also known as the cost of capital).
reinvest_rate: The interest rate you receive on the cash flows when they are reinvested.

Steps to Use the MIRR Function:

Enter Your Data: Input your series of cash flows into a column or a row in your Excel worksheet. Ensure that the data includes both positive and negative values.
Select a Cell: Click on the cell where you want the MIRR result to appear.
Enter the Function:

Start the formula with `=MIRR(`.
Select the range of cells containing the cash flows as the `values` argument.
Enter the `finance_rate`. For example, if the rate is 10%, enter `0.10`.
Enter the `reinvest_rate`. For instance, if the reinvestment rate is 12%, enter `0.12`.
Close the parenthesis and press Enter.

Example:

Suppose you have cash flows in cells A1:A5, a finance rate of 10%, and a reinvest rate of 12%. Your formula would look like this:

   =MIRR(A1:A5, 0.10, 0.12)

Interpret Results: The value returned by MIRR represents the annualized rate of return based on these assumptions.

Additional Tips:

Ensure your cash flow data is accurate, as incorrect data will lead to incorrect MIRR results.
It’s useful to know that the finance rate is typically the cost of the capital used in an investment, and the reinvest rate is the rate at which profits are reinvested.
If your result is not as expected, double-check your cash flow data and rates for accuracy.

By following these steps, you should be able to successfully calculate the MIRR in Excel, offering a more realistic assessment of an investment’s profitability compared to the traditional IRR.

facebooklinkedin