MIRR function

MIRR (Modified Internal Rate of Return) term is basically used in financial management or corporate finance and it indicates the profitability of an investment and therefore is commonly used in when making business decision, when choosing between investments. Calculation of MIRR uses a series/schedule of payments (which includes an initial outflow, along with the net income/inflows), in calculation of MIRR compounding of interest is done with the assumption of Net Present Value of the investment is zero.

The major difference between the MIRR and IRR (Internal Rate of Return) is that, in MIRR calculation, it considers the initial cost of the investment and also the interest received on the reinvestment of cash (compounding), whereas the IRR calculation does not consider the compounding effect.

MIRR is an improved version of their IRR approach to capital budgeting decisions. It does not require the assumption that the project cash flows are reinvested at the IRR; rather, it factors in a discrete reinvestment rate into the model.

Decision rule: MIRR of projects greater the hurdle rate (of project) should be accepted; in case of mutually exclusive projects, project with higher MIRR should be selected.

Note: in calculation of MIRR cash flows should be occur at regular intervals, but do not have to be the same amounts for each interval.

Syntax of MIRR function in Excel:

=MIRR( range, finance_rate, reinvestment_rate )

Parameters:

  • range - is a range of cells that represent the series of cash flows, (investment and net income values) that occur at regular periods. These must contain at least one negative value (representing payment) and at least one positive value (representing income)
  • finance_rate - is the interest rate that you pay on the cash flow amounts.
  • reinvestment_rate - is the interest rate that you receive on the cash flow amounts as they are reinvested.

Example - choosing between two projects

Suppose you are a financial manager at some investment company and working for an engineering firm. Two of the company's recent bids are accepted. First is related to construction of a new railroad and the second one is related to construction of a motorway. Both the projects are expected to take 3 years. The applicable financing rate is 10% and the project's cash flows (in dollars) are given below:

 MIRR function choose project

Both projects had positive net present values.

You are asked to recommend which project the company should accept. You believe that the economy might slow down a little in next few years and a lower reinvestment rate should be factored in. Your boss asked you to calculate MIRR for both the projects.

MIRR function is used to calculate MIRR for both projects as shown in the spreadsheet below. It uses finance rate of 10% and reinvestment rate of 8%.

MIRR function example

The motorway project should be preferred based on MIRR approach.