MIRR Function

What is the MIRR Function?

MIRR function calculates modified internal rate of return, adjusting for reinvestment rate and finance rate, providing accurate investment evaluation.

When we invest our money into something, we invest it with the hope of getting a profitable return. In other words, we want our investment to be good and positive. The last thing we would want to happen is to choose to invest our money into something that fails or that will become a bad investment.

Businesses and people make investment decisions every day. It can be on someone else's business, idea, funds, or even their own business. There are a lot of choices where you can invest your money.

Although when businesses want to invest or are choosing from different investment options, they don’t just decide out of hope. Businesses have to choose carefully and analyze all their opportunities. They also have to forecast what their returns could be like.

Consideration of whether to invest or not does not always have to be based on new investments. Businesses have the option to choose whether they want to reinvest in something they have already invested in or not.

If the initial or second investment went well, this does not guarantee that the third or fourth reinvestment will be significant as well. There would be a reinvestment risk even if there were positive results before.

This brings us to using the MIRR function. This function stands for Modified Internal Rate of Return, which can help you make your educated business decisions on excel.

The function can make it easier for businesses to rate or rank the investment options they may want to take. It can also be used to rate the investments they already have. It is not limited to just investments and can be applied to current and future projects as well.

When using the modified internal rate of return, two things will be taken into consideration:

1. The cost of the investment or project, 
2. The interest received on the reinvestment of cash. 

When picking between two projects, you will pick the one with the highest MIRR.

MIRR Function Formula

When working with excel, you want to make sure you know what the formulas need as arguments, which argument from the formula is required, and which one is optional.

In Excel, the formula for the modified internal rate of return is the following:

MIRR(values, finance_rate, reinvest_rate)

Depending on your preference, there are 2 ways of entering the formula:

1. You can enter your formula manually as we did, 
2. Or you can find it under the financial section. 

In order to find the finance section, you need to go to the formulas tab and find a green book that is labeled “Financial.”

Once you select it, a lot of formulas will show up, and you will scroll to find the MIRR formula.

Now that we know where and how to enter the formula let's start by breaking it down.

The formula contains the following arguments:

• Values
• Finance_rate 
• Reinvest rate

When working with your formula, it is important that you select the right data to plug into it so that you can get the most accurate results. Therefore, it is key to know what each argument in the formula means.

1. Values
   This can include the payments that have been made and the income that transpires in regular periods. If they are payments, they will be in negative form, -9,000, for instance, and if they are incomes, they are more than likely to be in a positive format.
2. Finance_rate 
   For this entry, you will enter the interest rate that was paid on the money that is being used.
3. Reinvest rate 
   The interest rate you get on the money as you reinvest it.

All of these are required entries. Therefore if you skip one or decide to leave it blank, the formula will not work and will result in an error. 

There are some things to take into consideration when working with the formula, and if you do not, you may run into a few errors when working with it.

When you are working with the value argument, it needs to contain two things: one negative and one positive value, at least. If it does not include them, it will result in the error message #DIV/0!

The formula for the modified internal rate of return takes into consideration the order in which you have your data in.

Therefore, it is essential to enter your payments and income in the order you want them calculated. Along with that information, do not forget to distinguish the payments from the income.

If any of your values have a typo, such as something that is not numeric, it will cause an error in the form of the error message #Value!

How to use the MIRR Function in Excel?

Now let's put our formula to the test in Excel.

For this example, let's say we have a current project going on, and we want to find its modified internal rate of return. We have the following information as shown.

As we can see, the initial cost of the project is $140,000. The values afterward are the money or, in other words, cashflows that are coming in. As seen, the first one was the return from the first year, the following one from the second, and so on.

At the bottom, we have the interest rate of the initial loan and the rate for the reinvested profits. Recall that the reinvested interest rate is the interest rate we get as we choose to reinvest our money.

Now that we have all the data needed in Excel, let's proceed to enter the formula. We will begin by selecting a cell in which we want our result to be calculated and begin by entering the formula.

For our first entry, we selected all the cells we wanted to be counted as our values. You can choose to enter every single one of them, or you can choose by selecting certain cells. In the example, the values that are being entered are the ones in cells A2 through A7. 

After you input the values in the formula, you will proceed by entering a comma. This is necessary to go on to the next argument; otherwise, the system will still recognize it as a value entry.

Your next entry will be the finance_rate. This will be the annual interest rate for the 140,000 loan. Again you can enter the rate manually, or you can select the cell that includes this information.

Our last entry will be the reinvest_rate. This is the rate for the reinvested profits. Now that we have entered all our entries, the formula should look like the image below.

After checking that everything is entered properly, press enters to compute the modified internal rate of return. In this case, the MIRR for the project is 7%.

The modified internal rate of return is seven percent for our project after five years. Even though we calculated the MIRR for the five years, you can use it to your advantage. You can do it for as many years as you would like.

Example 2

For the following example, we will calculate the modified internal rate of return for two investment projects to decide which project will be the best.

As we see, they both have the same initial cost, although the returns for the period and the interest rates are different.

Now that we have our information let's proceed to calculate the modified internal rate of return. The first project was already computed in the previous example, so therefore we will proceed to project 2.

We repeat the same process of entering the values and making sure to at least include a negative value and a positive one, as well as including the finance rate and reinvest rate, and we get our final results.

You can see that project 1 has a MIRR of 7%, while project 2 resulted in a 4% modified internal rate of return. When choosing which project is best, we will choose the one with the highest MIRR.

Thus, we will pick project 1 as it has the highest rate of return, and Project 1 would be the best investment in this case. 

Researched and authored by Sandra Martinez | LinkedIn

Reviewed and Edited by Wissam El Maouch | LinkedIn

Free Resources

To continue learning and advancing your career, check out these additional helpful WSO resources:

• MONTH Function
• MROUND Function
• N Function
• NA Function
• XIRR Function