MIRR

Returns the modified internal rate of return of a series of cash flows.

Syntax:

MIRR(payments, financerate, reinvestrate)

payment is a range containing the payments made or received, at regular intervals.

financerate is the rate of interest you pay per period on outstanding balances.

reinvestrate is the rate of interest you receive per period on invested balances.

At least one of the payments must be negative and at least one positive. The payments are assumed to arise at the start of each period; the order in which the payments are given is important.

MIRR finds the modified internal rate of return from the formula:

$(1 + M I RR)^{n - 1} = \frac{f ina l _ v a l u e _ o f _ p os i t i v e _ p a y m e n t s}{ini t ia l _ v a l u e _ o f _ n e g a t i v e _ p a y m e n t s}$

Example:

MIRR(A1:A4, 5%, 8%)

where A1:A4 contain -5000, 1000, 2000, 3000, returns approximately 8.16%.

Application:

A Business Expansion Project

Let's imagine a company, "Tech Innovations Inc.," is considering a new project to develop a cutting-edge software application. They need to analyze the project's financial viability.

The project requires an initial investment of $500,000. Over the next five years, the company projects a series of cash flows, some negative (due to additional R&D or marketing costs) and some positive (from sales and licensing).

The company's cost of financing (the interest rate on borrowed funds) is 8%, and its average reinvestment rate for positive cash flows is 12%.

Here is a table of the projected cash flows for the project:

	Year	Cash Flow
	A	B
1	0	-$500,000.00
2	1	$150,000.00
3	2	-$50,000.00
4	3	$200,000.00
5	4	$250,000.00
6	5	$180,000.00

Calculation using the MIRR Function

You would use the MIRR function. The syntax is:

MIRR(values, finance_rate, reinvest_rate)

Where:

values: The range of cells containing the cash flows (e.g., A1:A6).
finance_rate: The cost of financing for negative cash flows (8% or 0.08).
reinvest_rate: The rate at which positive cash flows can be reinvested (12% or 0.12).

For this example, the formula would be:

MIRR(A1:A6, 0.08, 0.12)

The result of this calculation would be 13.57%.

Analysis of the Result

The MIRR of 13.57% means that, given the specific financing and reinvestment rates, the project is expected to generate a 13.57% annual rate of return.

To make a decision, Tech Innovations Inc. would compare this MIRR to its hurdle rate or required rate of return. If the hurdle rate is, say, 10%, the project's MIRR of 13.57% is higher, indicating that it is a financially attractive investment and should be accepted.

This example demonstrates how MIRR provides a more realistic and practical measure of a project's profitability by using two distinct and more accurate interest rates, avoiding the pitfalls of the traditional IRR calculation.

Result:

13.57%