The MIRR function in Google Sheets is a financial tool that allows users to calculate the Modified Internal Rate of Return for an investment. This function takes into account both the cash inflows and outflows over time while adjusting for the differences in financing and reinvestment rates, providing a comprehensive picture of an investment’s profitability.

Syntax

=MIRR(cashflow, finance_rate, reinvest_rate)

• cashflow: An array or range representing the series of cash flows from the investment, including both inflows and outflows.
• finance_rate: The interest rate paid on borrowed funds used for the investment.
• reinvest_rate: The rate of return received on reinvested income generated from the investment.

Example #1

=MIRR(A1:A5, 0.05, 0.10)

This calculates the MIRR for cash flows listed in cells A1 through A5, considering a financing rate of 5% and a reinvestment rate of 10%. The result might be, for example, 7.2%.

Example #2

=MIRR(B1:B6, 0.07, 0.12)

This example evaluates cash flows in cells B1 to B6 with a financing rate of 7% and reinvestment at 12%, yielding a result of 8.5%.

Example #3

=MIRR(C1:C10, 0.04, 0.08)

In this case, the function is applied to cash flows in C1 through C10, using a 4% financing rate and an 8% reinvestment rate, producing a MIRR of 5.6%.

Error handling

• NUM! This error indicates that the MIRR cannot be calculated due to an invalid input, typically because the cash flow series does not contain at least one positive and one negative value.
• VALUE! This occurs when there is a non-numeric input in the cash flow range, finance rate, or reinvestment rate, necessitating correction of the input values.
• DIV/0! This error arises if the finance rate is zero, which prevents computation of the MIRR, so a non-zero financing rate must be provided.

Conclusion

Overall, the MIRR function is invaluable for investors and analysts seeking to evaluate the profitability of an investment by considering multiple rates of return and cash flow components. This nuanced approach to internal rate calculations provides a clearer understanding of an investment’s potential performance, aiding in more informed decision-making.