MIRR
TheMIRR
function calculates the modified internal rate of return for a series of cash flows that occur at irregular intervals. It is commonly used in financial analysis to compare the profitability of different investments. The function takes three arguments: the cash flows, the financing rate, and the reinvestment rate.
 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:
 cashflow_amounts (required):
An array or range of cash flow amounts that occur at irregular intervals.  financing_rate (required):
The interest rate paid to finance the investment.  reinvestment_return_rate (required):
The rate of return earned on cash flows that are reinvested.
Examples
Here are a few example use cases that explain how to use theMIRR
formula in Google Sheets.
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
MIRR
not working? Here are some common mistakes people make when using the MIRR
Google Sheets Formula:
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.