How to Use MIRR Function in Excel (3 Quick Examples)

In Microsoft Excel, the MIRR function estimates the likely profitability of an investment or project. In this article, you’ll learn how to use MIRR function in Excel and how it differs from the IRR function.

mirr function excel

The above screenshot is an overview of the article which represents the application of the MIRR function in Excel. Granted this, in the following sections, you’ll learn more about the dataset as well as how to use the function.


Download Practice Workbook

You can download the practice workbook from the link below.


Introduction to MIRR Function

In a nutshell, MIRR (Modified Internal Rate of Return) considers the reinvestment of the net present value of the capital inflows at a rate equal to the firm’s cost of capital.
Besides, MIRR gives a more realistic estimation of the profitability of an investment compared to the internal rate of return.

Introduction to MIRR Function

  • Function Objective:

The MIRR function considers both the finance and reinvest rates to calculate the modified internal rate of return.

  • Syntax:

=MIRR(values, finance_rate, reinvest_rate)

  • Argument Explanation:
Argument Required/Optional Explanation
values Required An array consisting of the yearly cash flows
finance_rate Required The discount rate as a percentage
reinvest_rate Required The rate of interest (as a percentage) on the reinvested yearly cash flows 
  • Return Parameter:

The Modified Internal Rate of Return while taking into account the discount rate and reinvestment rate.


3 Examples of Using MIRR Function in Excel

Now, let’s consider the Yearly Cash Flow of Project Alpha dataset shown in the B4:C15 cells. Here, the dataset shows the Year starting with 0 (Initial investment), the yearly Cash Flows, the Discount Rate of 10%, and the Reinvest Rate of 7.5%.

Dataset for Using MIRR Function in Excel

Here, we have used Microsoft Excel 365 version, you may use any other version according to your convenience.


1. Calculating MIRR of an Investment

In the first place, let us start with a simple example of computing the profitability of an investment using the MIRR function. So, let’s see it in action.

📌 Steps:

  • First and foremost, go to the F7 cell and insert the formula given below.

=MIRR(C5:C15,F4,F5)

In this formula, the C5:C15 range of cells refers to the Cash Flows, the F4 cell points to the Discount Rate, and the F5 cell indicates the Reinvest Rate.

Calculating MIRR of an Investment

  • Next, press the ENTER key and the result should look like the image given below.

mirr function excel simple example

That’s it you’ve calculated the Modified Internal Rate of Return of the project. It’s that simple!


2. Comparing Two Investments with MIRR Function

Assuming Yearly Cash Flow of Projects Alpha and Beta dataset shown in the B4:D15 cells. Here, we want to compare the Modified Internal Rate of Return of the two projects to determine which is more profitable. Now, we the yearly Cash Flows for both Projects Alpha and Beta, moreover, the Discount and Reinvest Rates are 10% and 7.5% respectively.

Comparing Two Investments with MIRR Function

📌 Steps:

  • At the very beginning, move to the G7 cell and enter the expression given below.

=MIRR(C5:C15,G4,G5)

In this expression, the C5:C15 range of cells refers to the Cash Flows,  while the G4 and G5 cells represent the Discount Rate and Reinvest Rate.

mirr function excel comparing two projects

  • Now, this returns the Modified Internal Rate of Return of Project Alpha, in this case, it is 11.18%.
  • Likewise, navigate to the G8 cell and calculate the Modified Internal Rate of Return of Project Beta which is 9.69%.

Eventually, the results should look like the picture shown below, and since the MIRR of Project Alpha is greater than Beta, so it is more profitable.

mirr function excel comparison of projects

Read More: XIRR vs IRR in Excel (Differences with Examples)


3. Applying VBA Code

You can also use VBA Code to obtain the Modified Internal Rate of Return of a project, it’s simple & easy, just follow along.

📌 Steps:

  • First, navigate to the Developer tab >> click the Visual Basic button.

Applying VBA Code

This opens the Visual Basic Editor in a new window.

  • Second, go to the Insert tab >> select Module.

Inserting Module

For your ease of reference, you can copy the code from here and paste it into the window as shown below.

Sub Calculate_MIRR()

Dim M_IRR As Double
Static Cash_flows(11) As Double

Cash_flows(0) = -50000
Cash_flows(1) = 5000
Cash_flows(2) = 8500
Cash_flows(3) = 8000
Cash_flows(4) = 9000
Cash_flows(5) = 10000
Cash_flows(6) = 9500
Cash_flows(7) = 11000
Cash_flows(9) = 13500
Cash_flows(10) = 15000
Cash_flows(11) = 20000

M_IRR = MIRR(Cash_flows(), 0.1, 0.075)

M_IRR_pct = Format(M_IRR, "0%")
MsgBox "MIRR = " & M_IRR_pct

End Sub

VBA Code for mirr function excel

Code Breakdown:

Now, I will explain the VBA code used to calculate MIRR which code is divided into 3 steps.

  • In the first portion, the sub-routine is given a name, here it is Calculate_MIRR().
  • Next, define the variables M_IRR, and Cash_Flows and assign the data type Double.
  • In the second potion, enter all the Cash flows manually.
  • In the third portion, use the MIRR function to compute the value and format it as a percentage using the Format function.
  • Finally, display the results in the MsgBox function.

Code Explanation for mirr function excel

  • Third, close the VBA window >> click the Macros button.

This opens the Macros dialog box.

  • Following this, select the Calculate_MIRR Macro >> hit the Run button.

Running Code

Consequently, the results should look like the screenshot given below.

Results for mirr function excel


Comparing MIRR and IRR Functions

What if you want to compare the MIRR and IRR for the same project? In the following section, we’ll answer this exact question therefore let us see the process in the steps below.

📌 Steps:

  • Initially, jump to the F7 cell and type in the formula below.

=MIRR(C5:C15,F4,F5)

Here, the C5:C15 range of cells refers to the Cash Flows,  the F4 cell points to the Discount Rate, and the F5 cell indicates the Reinvest Rate.

Comparing MIRR and IRR Functions

  • From this point, proceed to the F8 and compute the IRR using the expression given below.

=IRR(C5:C15)

Applying IRR Function

Lastly, we can see that the IRR of the project is greater than the MIRR which is an overly optimistic estimation of the profitability.


Shortcomings of MIRR Function

For one thing, experts suggest that because a project’s earnings are not always fully reinvested, so the MIRR function generates a less predictable rate of return. Nevertheless, by changing the reinvest rate, you can compensate for the partial investments.


Things to Remember

In this case, a few issues you may encounter when using the MIRR function in Excel.

  • First, a #DIV/0! error results if the values don’t have at least one positive number (referring to the cashflows) and one negative number (indicating the initial investment).
  • Second, the #VALUE! error occurs when one of the arguments, finance rate or reinvest rate, is not a numeric value.
  • Third, the MIRR function uses the order of values to determine the order of cash flows under the assumption that all cash flows occur at regular intervals and at the end of each year.

Practice Section

Here, we have provided a Practice section on the right side of each sheet so you can practice yourself. Please make sure to do it by yourself.

Practice Section


Conclusion

To sum up, in this article, I have shown you 3 examples of how to use MIRR function in Excel. Now, I suggest you read the full article carefully and apply this knowledge in the practice workbook. which you can also download for free. I hope you find this article helpful and informative and if you have any further queries or recommendations, please feel free to comment here or visit ExcelDemy for many more articles like this.


Related Articles

Taryn N

Taryn N

Taryn is a Microsoft Certified Professional, who has used Office Applications such as Excel and Access extensively, in her interdisciplinary academic career and work experience. She has a background in biochemistry, Geographical Information Systems (GIS), and biofuels. She enjoys showcasing the functionality of Excel in various disciplines. She has over ten years of experience using Excel and Access to create advanced integrated solutions.

2 Comments
  1. Hi, thanks for the article,

    If your net income cash flows are already diminished of the interests to be paid, should we put a value for the “finance rate”, or simply 0 to avoid double counting?

    Thanks !
    Laurene

    • Hi Laurene,
      Thanks for staying with us. If the net income cash flows reduced, or 0 or negative, whatever it is. The value of the argument finance rate doesn’t depend on it. You must give the rate as input which is paid by you for cash flows. When you select the payment and incomes at specified intervals as the Values argument, the finance rate as the rate paid by you for your income, and finally the reinvestment rate, the MIRR function will calculate the rate by automatically adjusting the values.

Leave a reply

ExcelDemy
Logo