MIRR

 The MIRR (Modified Internal Rate of Return) function in Excel is used to calculate the modified internal rate of return for a series of cash flows, considering both the cost of investment and the interest received on reinvestment of cash. Unlike the IRR function, MIRR assumes that positive cash flows are reinvested at a different rate than the cost of capital.

Here’s a detailed explanation of the MIRR function, including its syntax, example data, and formulas.

---

Syntax of MIRR

excel

Copy

MIRR(values, finance_rate, reinvest_rate)

• values: A range of cells representing the series of cash flows (must include at least one negative and one positive value).

• finance_rate: The interest rate paid on the money used in the cash flows (cost of capital).

• reinvest_rate: The interest rate received on reinvested cash flows.

---

Example Data

Let’s assume the following cash flow data for an investment:

Year	Cash Flow
0	-$100,000	(Initial Investment)
1	$20,000
2	$30,000
3	$40,000
4	$50,000

Assume:

• Finance Rate (Cost of Capital): 10%

• Reinvestment Rate: 12%

---

Steps to Calculate MIRR

1. Enter the Data in Excel:

   • Place the cash flows in a column (e.g., B2:B6).

2. Use the MIRR Formula:

   • In a cell, enter the formula:

     excel

     Copy

     =MIRR(B2:B6, 10%, 12%)

   • This calculates the modified internal rate of return for the cash flows.

---

Explanation of the Formula

• B2:B6 : The range of cash flows.

• 10%: The finance rate (cost of capital).

• 12%: The reinvestment rate.

---

How MIRR Works

1. Negative Cash Flows: Discounted at the finance rate (10%).

2. Positive Cash Flows: Reinvested at the reinvestment rate (12%).

3. MIRR Calculation: Computes the rate of return that equates the present value of negative cash flows to the future value of positive cash flows.

---

Manual Calculation of MIRR

To understand how MIRR works, you can manually calculate it using the following steps:

1. Calculate the Future Value of Positive Cash Flows:

   • Reinvest each positive cash flow at the reinvestment rate (12%).

   • Example:

     • Year 1: $20,000\times (1+12$20,000 × 1.4049 = $28,098

     • Year 2: $30,000\times (1+12$30,000 × 1.2544 = $37,632

     • Year 3: $40,000\times (1+12$40,000 × 1.1200 = $44,800

     • Year 4: $50,000\times (1+12$50,000 × 1.0000 = $50,000

   • Total Future Value = $28,098+$37,632 + $44,800+$50,000 = $160,530

2. Calculate the Present Value of Negative Cash Flows:

   • Discount the initial investment at the finance rate (10%).

   • Example:

     • Year 0: -$100,000\times (1+10$100,000

3. Calculate MIRR:

   • Use the formula:

     $$\text{MIRR}={\left(\frac{\text{Future Value of Positive Cash Flows}}{\text{Present Value of Negative Cash Flows}}\right)}^{\frac{1}{n}}-1$$

     Where $n$ is the number of periods (4 years).

   • Plugging in the numbers:

     $$\text{MIRR}={\left(\frac{160,530}{100,000}\right)}^{\frac{1}{4}}-1=1.6053^{0.25}-1=1.126-1=0.126\text{ or }12.6\mathrm{\%}$$

---

Result

The MIRR for this investment is 12.6%.

---

Key Points

• MIRR is more realistic than IRR because it accounts for different rates for financing and reinvestment.

• It avoids the issue of multiple IRRs that can occur with non-conventional cash flows.

• Always ensure the cash flows include at least one negative and one positive value.