MIRR Function in Google Sheets

Calculate the modified internal rate of return for a series of cash flows using the MIRR function.

MIRR Function

The MIRR function in Google Sheets calculates the modified internal rate of return for a series of cash flows, considering both the cost of investment and the interest earned on reinvestment.

Syntax

MIRR(cashflow_amounts, financing_rate, reinvestment_return_rate)
  • cashflow_amounts: An array or range of cells containing the series of cash flows. This is a required parameter.
  • financing_rate: The interest rate paid on the money used in the cash flows. This is a required parameter.
  • reinvestment_return_rate: The interest rate received on the cash flows as they are reinvested. This is a required parameter.

Examples

  1. Basic MIRR Calculation

Calculate the MIRR for a series of cash flows with a financing rate of 10% and a reinvestment return rate of 12%:

=MIRR({-1000, 200, 300, 400, 500}, 10%, 12%)

This will output approximately 12.59%.

Notes

  • MIRR considers both the cost of the investment and the interest earned on reinvestment, providing a more accurate reflection of an investment’s profitability.
  • IRR: Calculate the internal rate of return for a series of cash flows.
  • NPV: Calculate the net present value of a series of cash flows.