MIRR

• How to use MIRR formula?
• Examples of using MIRR formula
• MIRR formula not working?
• Similar formulas to MIRR

Usage

Use the MIRR formula with the syntax shown below, it has 3 required parameters:

=MIRR(cashflow_amounts, financing_rate, reinvestment_return_rate)

1. cashflow_amounts (required):
   An array or range of cash flow amounts that occur at irregular intervals.
2. financing_rate (required):
   The interest rate paid to finance the investment.
3. reinvestment_return_rate (required):
   The rate of return earned on cash flows that are reinvested.

Examples

Comparing Investment Opportunities

Suppose you are considering two investment opportunities, each with a different cash flow pattern. You can use the MIRR function to calculate the modified internal rate of return for each investment and compare them to determine which is more profitable.

Analyzing Investment Performance

Suppose you have already made an investment and want to analyze its performance. You can use the MIRR function to calculate the modified internal rate of return and compare it to the cost of capital to determine whether the investment is performing well or poorly.

Evaluating Capital Projects

Suppose you are evaluating a capital project that will require an initial investment and generate cash flows over several years. You can use the MIRR function to calculate the modified internal rate of return and determine whether the project is financially viable.

Common Mistakes

Incorrect range for cashflow_amounts

One common mistake is providing an incorrect range or array for the cashflow_amounts parameter. Ensure that the range or array includes all cash inflows and outflows.

Incorrect financing_rate

Another common mistake is providing an incorrect rate for the financing_rate parameter. Ensure that the rate is in decimal form and represents the cost of financing.

Incorrect reinvestment_return_rate

A mistake users make is providing an incorrect rate for the reinvestment_return_rate parameter. Ensure that the rate is in decimal form and represents the expected rate of return on reinvested cashflows.

No cashflow_amounts provided

One mistake is not providing any cashflow_amounts for the cashflow_amounts parameter. Ensure that there are cash inflows and outflows to calculate the modified internal rate of return.

Negative or zero financing_rate or reinvestment_return_rate

A common mistake is providing a negative or zero rate for the financing_rate or reinvestment_return_rate parameter. Ensure that the rates are positive and represent the cost of financing and expected rate of return on reinvested cashflows.

Related Formulas

The following functions are similar to MIRR or are often used with it in a formula:

• NPV

  The NPV function calculates the net present value of a series of cash flows, discounted by a specified rate. It is commonly used to determine the present value of an investment, where the cash flows represent incoming and outgoing payments. The function takes a discount rate and one or more cash flow values as input.

• IRR

  The IRR function is used to calculate the Internal Rate of Return for a series of cash flows. The IRR is the interest rate at which the Net Present Value of the cash flows equals zero. It is commonly used to evaluate potential investments or projects.

• XIRR

  The XIRR function in Google Sheets calculates the internal rate of return (IRR) for a series of cash flows that occur at irregular intervals. It returns the annualized percentage rate earned by an investment, taking into account the dates on which each payment was made. The function is most commonly used in financial analysis to compare the returns on different investment opportunities.

Learn More

You can learn more about the MIRR Google Sheets function on Google Support.