What is the MIRR Function?
The MIRR function is categorized under Financial functions. The function will R, which is the initial investment value and a series of net income values. MIRR uses a schedule of payments, including an initial investment and a series of net income payments, to calculate the compounded return, assuming the Net Present Value (NPV) of the investment is zero.
The function is very helpful in financial analysis for an investment as it helps to calculate the return an investment will earn based on a series of mixed cash flows.
We can also use MIRR in financial modeling, although the common practice is to use IRR as transactions are studied in isolation. MIRR is different from IRR as it helps set a different reinvestment rate for cash flows received. Thus, the MIRR function considers the initial cost of the investment and also the interest received on the reinvestment of cash, whereas the IRR function does not.
Formula
=MIRR(values, finance_rate, reinvest_rate)
The MIRR function uses the following arguments:
- Values (required argument) – It is an array or a reference to cells that contain numbers. The numbers are series of payments and income that incur negative values. Payments are represented by negative values and income by positive values.
- Finance_rate (required argument) – It is the interest rate that we pay on the money that was used in the cash flows.
- Reinvest_rate (required argument) – It is the interest that we receive on the cash flows as we reinvest them.
Notes on the arguments:
- The argument value should contain at least one positive and one negative value to calculate MIRR.
- The payment and income values should be in the sequence we want and with the correct signs, which is positive values for cash received and negative values for cash paid.
- If n is the number of cash flows in values, frate is the finance_rate, and rrate is the reinvest_rate, the formula for MIRR is:
How to use the MIRR Function in Excel?
To understand the uses of the MIRR function, let’s consider a few examples:
Example 1
Let’s assume we need to choose one project from two given projects. The initial investment of both the projects is the same. Year-wise, the cash flows are as follows:
In the screenshot above, C5 and D5 are the initial investment amounts in Project I and Project II. Cells C6:C10 and D6:D10 show the cash inflow for Project I and Project II, respectively.
The finance rate and the reinvestment rate of the project is 6% and 5%, respectively. The formulas to be used are as follows:
Remember that the initial investment needs to be in negative form, else the formula will return an error. The initial investment is a negative value, as it is an outgoing payment, and the income payments are represented by positive values.
We get the result below:
As we can see above, Project I is more preferable.
Things to remember about the MIRR Function
- #DIV/0! error – Occurs when the given value array does not include at least one positive value and one negative value.
- #VALUE! error – Occurs when any of the given arguments is non-numeric.
Click here to download the sample Excel file
Additional resources
Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your financial analysis. To learn more, check out these additional resources: