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.

Introduction to the MIRR Function

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

• 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:

• Return Parameter:

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

How to Use the MIRR Function in Excel: 3 Examples

Consider the Yearly Cash Flow of Project Alpha dataset shown in the B4:C15 cells. 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%.

Example 1 – Calculating MIRR of an Investment

Steps:

• Go to the F7 cell and insert the formula given below.

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.

• Hit Enter.

Example 2 – Comparing Two Investments with the MIRR Function

Consider the Yearly Cash Flow of Projects Alpha and Beta dataset shown in the B4:D15 cells. We want to compare the Modified Internal Rate of Return of the two projects to determine which is more profitable.

Steps:

• Go to the G7 cell and enter the expression given below.

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

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

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

Example 3 – Applying VBA Code

Steps:

• Navigate to the Developer tab and click the Visual Basic option.

This opens the Visual Basic Editor in a new window.

• Go to the Insert tab and select Module.

• 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

• Close the VBA window and click the Macros button.

This opens the Macros dialog box.

• Select the Calculate_MIRR macro and hit the Run button.

The results should look like the screenshot given below.

Comparing MIRR and IRR Functions

Steps:

• Jump to the F7 cell and insert the formula below.

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.

• Go to F8 and compute the IRR using the expression given below.

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

Because a project’s earnings are not always fully reinvested, 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

• 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).
• The #VALUE! error occurs when one of the arguments, finance rate or reinvest rate, is not a numeric value.
• 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

We have provided a Practice section on the right side of each sheet so you can practice.

Download the Practice Workbook

<< Go Back to Excel Functions | Learn Excel