MIRR Calculation in Excel

Modified Internal Rate of Return or MIRR is a an efficient function to use when one wants to factor in cost of finance and reinvestment rate for periodic returns during the life of project / investment.

IRR calculation in Excel assumes that the periodic returns or positive cash flows that occur during the life of a project are reinvested at interest rate equivalent to the IRR computed, though in reality, it may not be the case many a time. IRR calculation is therefore considered to be flawed to this extent. Excel's MIRR function is an improved version of IRR which takes care of the above deficiency of IRR. MIRR calculation factors in the reinvestment rate - which can be input by the user - which is basically the interest rate at which the returns or positive cash flows during the life of the project are expected to be reinvested. MIRR also factors in the cost of finance or the interest rate that the negative cash flows or the initial investment carries. MIRR function has the following syntax: =mirr(values,finance_rate,reinvest_rate); There must be at least one negative value and one positive value in the cash flow values to calculate MIRR, else MIRR returns error.

It may be worthwhile to understand the logic of MIRR calculation as well. There are 3 steps involved in MIRR calculation; First, computation of the present value of the negative cash flows discounted at the finance rate. Second, computation of cumulative future value of returns or positive cash flows grown at the reinvestment rate; Third, determination of the discount rate at which the present value of the value in Step 2 equals the value in Step1 - and this rate is precisely MIRR. If you follow these steps, MIRR can be computed even without using Excel's MIRR function - this would give a better insight into the logic of this function - though using Excel's built in MIRR function is quick and efficient.

MIRR function usage and its logic as explained above is demonstrated in this MIRR calculation example spreadsheet; You may also like to see XIRR calculation logic.