MIRRfunction 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
- Examples of using
MIRRformula not working?
- Similar formulas to
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.
ExamplesHere are a few example use cases that explain how to use the
MIRRformula 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.
MIRRnot working? Here are some common mistakes people make when using the
MIRRGoogle 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.
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.
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.
The following functions are similar to
MIRR or are often used with it in a formula:
NPVfunction 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.
IRRfunction 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.
XIRRfunction 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.
You can learn more about the
MIRR Google Sheets function on Google Support.