I. Intro

The MIRR function calculates the modified internal rate of return based on the difference between the funding rate for a series of fixed cash flows and received reinvestment.

Note: The modified internal rate of return, also known as the modified internal rate of renumeration, indicates the internal rate of return when calculating expenditure and income with different interest rates. It also takes into account investment costs and cash reinvestment output.

II. Explanation

•Formula: =MIRR(values,finance_rate,reinvest_rate)

•Parameters:

◦values (required): This includes arrays or ranges of income or expenditure related to an investment.

◦finance_rate (required): The interest rate for investment capital.

◦reinvest_rate (required): The profits (expressed in percent) made from reinvesting investment profits.

•Example: =MIRR(A2:A25,8%,11%)

•Note:

◦Note that the cash flow amount should be an array or a range. It cannot be an individual number.

◦The MIRR function returns the order of the cash flow according to the order of the entered values. For this reason, be sure to enter the expenditure and income amounts according to their actual order, and use accurate signs (positive values for enter incoming cash flow and negative values for outgoing cash flow).

III. Steps

Use the MIRR function

1.Select a cell and enter =MIRR.

2.Enter formula parameters =MIRR(B2:B7,B8,B9) into the cell.

3.Press Enter to display the result in the cell, which is 13%.

Delete the MIRR function

Select the cell with the MIRR function applied, and press Delete to clear the formula from the selected cell.

IV. Scenarios

•Finance essentials: Use the MIRR function to quickly calculate a modified return rate for an investment

The formula in the figure below is used to calculate the modified internal return rate for an investment. We can use the MIRR function to avoid manually performing tedious calculations. We simply enter the parameters in different locations in a Feishu sheet to quickly get the result.

•Formula used in the figure below: = MIRR(B2:B6,B7,B8)

•Formula parameter explanation: In the example in the figure below, we need to calculate the modified internal return rate after four years. B2:B6 is a group of cash flow amounts, the 7% in cell B7 represents the finance rate, and the 10.5% in cell B8 represents the reinvest rate.