Google Sheets MIRR Function: Syntax, Examples & Tips

cashflow_amounts:
The series of cash flows that occur at regular intervals, including both positive and negative amounts. This can be a range of cells or an array of values.

financing_rate:
The interest rate used to finance the cash flows, expressed as a decimal. This rate is applied to the negative cash flows.

reinvestment_return_rate:
The interest rate earned on reinvested cash flows, also expressed as a decimal. This rate is applied to the positive cash flows.

What is a MIRR?

The MIRR (Modified Internal Rate of Return) function in Google Sheets is a financial function used to calculate the profitability of an investment by considering the cost of financing and the reinvestment rate of cash flows. Unlike the traditional IRR function, MIRR provides a more accurate reflection of an investment's profitability by accounting for the cost of capital and the returns on reinvested cash flows.

In simpler terms, MIRR helps you evaluate the potential return on an investment by factoring in both the costs and the returns, making it a valuable tool for financial analysis.

A Practical Example

Imagine you are evaluating a project with the following cash flows over a 5-year period:

Cash Flows Table:

You also have the following rates:

• Finance Rate: 10%
• Reinvestment Rate: 8%

MIRR Formula

To calculate the MIRR for this project, you would use the MIRR function as follows:

=MIRR(A2:A7, 10%, 8%)

In this formula:

• A2:A7 is the range containing the cash flows.
• 10% is the finance rate, representing the cost of capital.
• 8% is the reinvestment rate, representing the rate at which cash flows are reinvested.

Result of the Formula

When you apply the MIRR formula to the cash flows, you would get a result that indicates the annualized return of the investment. For example, if the MIRR calculation returns 7.45%, it means that the project is expected to yield an annual return of 7.45% after accounting for the cost of financing and reinvestment.

Why Use MIRR?

MIRR is beneficial because it provides a more realistic assessment of an investment's profitability compared to traditional IRR. By incorporating both the cost of financing and the reinvestment rate, MIRR helps investors make more informed decisions. Additionally, it avoids some of the pitfalls of IRR, such as multiple rates of return for the same cash flow series.

Key Takeaways:

• MIRR: A financial function that calculates the modified internal rate of return, considering financing and reinvestment rates.
• Accurate Assessment: Provides a more realistic view of an investment's profitability compared to traditional IRR.
• Investment Decision-Making: Helps investors evaluate projects and make informed financial decisions.
• Common Use Cases: Ideal for capital budgeting, project evaluation, and any scenario where cash flow analysis is necessary.

MIRR is an essential function for anyone involved in financial analysis, providing a powerful way to assess the viability of investments and projects.

Happy investing!