MIRR in Google Sheets

What is MIRR in Google Sheets?

MIRR in Google Sheets evaluates an investment’s profitability by calculating the modified internal rate of return. The function considers both the cost of the investment, and the interest earned on reinvestment. MIRR stands for Modified Internal Rate of Return. MIRR is used in capital budgeting for investments. It allows for the identification of how viable a project may be. When the MIRR is higher than the expected return on the project, that indicates an attractive investment. Thus, the function is very helpful for comparing projects or financial plans with varying cash flows.

Let us consider an example. Here, an investment is done at an initial cost of $10,000 and the returns are $4,500, $5,000, and $6,500 over three years. If the financing rate is 10% and the reinvestment rate (rate earned on reinvested funds) is 12%, we apply the following formula to evaluate its profitability.

=MIRR(A2:A5, 10%, 12%)

Here, the cells A2:A5 contain the cash flows (-10000, 4500, 5000, 6500). It returns the modified internal rate of return. Here, we get 21% which indicates that, after accounting for the 10% financing cost and 12% reinvestment rate, the investment yields a favorable return, and the project is financially viable

Syntax

Now that we understand the basics, let’s explore the syntax of MIRR  in Google Sheets. This will help us apply it across different financial scenarios.

=MIRR(cashflow_range, finance_rate, reinvest_rate)

1. cashflow_range – A range of values representing the cash flow. The first value is usually negative, representing an outflow.
2. finance_rate – The interest rate paid on the money used in the investment.
3. reinvest_rate – The interest rate earned on cash flows as they are reinvested.

The function returns the modified internal rate of return, taking into account both borrowing costs and reinvestment gains. It assumes that outflows occur at the beginning and inflows occur thereafter at regular intervals.

How To Use MIRR Function in Google Sheets

The MIRR function can be used to calculate returns for investment projects, especially when we have the reinvestment and financing rates. Below is a practical step-by-step example to understand its application.

The function can be entered:

• Manually in a Sheet
• Through the Google menu

Manual Entry of MIRR in Google Sheets

A person has invested $8,000 on a particular day. Here, he received returns of $1,800, $2,500, $3,000, and $4,000 over the next four years. The financing rate is 8%, and he reinvests the returns at a rate of 10%.

Step 1: Enter the cash flow data in the cells as shown below.

Step 2:Click on the cell where you want the MIRR result to appear. Here, we choose A7.

Type the formula by following these steps:

Begin with an equal sign = . Type the function name MIRR and open the parentheses (.

Enter the arguments separated by commas. Close the parentheses.

The formula will look like:

=MIRR(A1:A5, 8%, 10%)

Step 3: Press Enter. The result shows the modified internal rate of return for the provided cash flow data.

Using the Menu Bar

If you prefer using the menu over typing formulas manually, follow these steps.

1. Choose a cell.
2. Go to Insert -> Function -> Financial -> MIRR.
3. A formula prompt like =MIRR() will appear.
4. Enter the arguments.
5. Press Enter to view the result.

Examples

Let us understand how the MIRR function works in real-world applications. We will explore a few practical scenarios. These examples show how MIRR can be used for financial decision-making and dynamic calculations across rows using ARRAYFORMULA.

Example #1 – Evaluate the Profitability of a Bond Investment Based on Cash Flows and Interest Rates

In this example, a person has invested in a bond that pays out structured annual returns. He wants to evaluate the modified internal rate of return (MIRR) to assess its profitability, after considering the cost of capital and the reinvestment interest rate.

Step 1: Enter the following values in the cells as shown below. We have entered them in the order with the initial investment of $6,000 followed by four annual returns.

Step 2: Click on cell B2, and enter the formula:

=MIRR(A1:A5, 7%, 10%)

• A1:A5 is the range of cash flows
• 7% is the financing rate
• 10% is the reinvestment rate

Step 3: Press Enter. The result will show the MIRR, helping you understand whether this bond investment is yielding a favorable return. Clearly in this case, the outcome doesn’t seem too favorable at 3%.

Example #2 – Using MIRR with IF Function

In this example, an analyst wishes to label investment projects based on whether they are profitable or not. They decide that if the MIRR is greater than 10%, the project is profitable. Else it is not. Here, 6% is the financing rate and 10% is the reinvestment rate

Step 1: Enter cash flow values for 3 projects into cells A2 to C6:

In cell A7, enter the following IF formula with MIRR:

=IF(MIRR(A2:A6, 6%, 9%) > 10%, “Profitable”, “Not Profitable”)

This checks if the MIRR is more than 10% and returns a custom message accordingly.

Step 3: Press Enter. You will see either “Profitable” or “Not Profitable” based on the result of the MIRR. You can drag the formula from A7 to C7 to check the viability of all three projects. This allows the categorization of projects using financial logic.

Example #3

Some investors want to understand if the investment in a manufacturing facility would boost up their operations or if it was simply not worth it. They determined that they have to spend $200 million to build the plant. The cost of capital is 9%. The plant would generate an additional $50 million in the first year it is in operation. IN the second year, they estimate it to be $100 million, and three times as much in the third year ($150 million.). The reinvestment rate is 11%.

Step 1: Set up the values as shown below.

Step 2: Enter the formula below in cell A3

=MIRR(B2:E2, 9%, 11%)).

Step 3: Press Enter. It will return the MIRR for the project. We can use this information to determine if the project may be better for the company’s goals by comparing it with similar projects.

Important Things to Note

1. MIRR requires at least one negative and one positive cash flow in the input range. If all values are either positive or negative, Google Sheets will return an error as the function cannot compute returns without an initial investment or income.
2. The order of cash flows must be in chronological order. It should start from the initial investment (negative) followed by returns.
3. MIRR uses consistent periodic intervals. It treats all cash flows as occurring at regular intervals.
4. When entering financing and reinvestment rates, you can use either decimal format (e.g., 0.06) or percentage format (e.g., 6%).

Frequently Asked Questions (FAQs)

Download Template

This article must help understand MIRR Function in Google Sheets with its formulas and examples. You can download the template here to use it instantly.

Recommended Articles

Guide to What is MIRR Function in Google Sheets. We learn how to use MIRR function in google sheets with its examples and syntax. You can learn more from the following articles. –