MIRR - modified IRR
MIRR - is an acronym for modified internal rate of return and it is one of the many tools that an analyst makes use of to find an investor's return on investment. An investment is only viable when it generates enough money to pay off the financing and to leave enough money to keep as profits. MIRR is a modified IRR and is used when an internal rate of return may not be found or there exist multiple IRR values. There are no IRR formulas yet we can always calculate a modified IRR given that there is at least one negative amount and one positive amount. The MIRR is found by finding the geometric average of net future value of benefits and net present value of costs. The net future value is found as a sum of compounded benefits whereas net present value is found as a sum of discounted costs.
Here I present you with an MIRR document as a Word file that will examine the Modified IRR in quite detail. It starts with defining the MIRR and the reason why we select MIRR as opposed to IRR. Then it presents the MIRR formula and uses it in an example calculation of modified internal rate of return. References are given for more detailed examination of MIRR calculation when the cash flows are accompanied by a schedule of dates. This is referred to as an XMIRR calculation named after an Excel function that finds the modified internal rate of return. The guide also looks at other aspects of MIRR calculation that relate to use of compounding frequencies of interest and the possibility of cash flows other than annual such as half-yearly, quarterly, monthly, weekly, daily or even biennial.
Finding MIRR in Excel
Up until Excel 2013, there was only one native financial functions in Excel to find the MIRR - modified internal rate of return yet it was limited in its functionality In spring of 2012, tadXL add-in was released on the market that offered its own set of financial functions for the Excel 2007, 2010 and 2013. tadXL contains a number of MIRR functions that are used in finding modified IRR depending how much of information is available about the investment. The trivial of these MIRR calculations in tadXL allowed for annuity payments with start or end of period cash flows. The more complex set of MIRR calculations in later versions of tadXL allowed for use of compounding frequencies of interest, the selection of lengths for the payment periods, a number of discounting conventions. Then the different variants of tadMIRR function permitted use of schedule of transaction dates, schedule of discount rates, and a combination of both rates and dates schedules. That said, none of these options were available in Excel up to Excel 2013, if Microsoft were to include its own set of MIRR and other financial functions in upcoming Excel 2015 or 2016 that mimic the functions in tadXL then that would be outright a copyright violation. The tadXL functions were developed over a period of six years and the research work carried out by the author and none of these functions or formulas to perform such calculations were hiterto known in finance world. I suppose rights do not matter over profits even those who run large software companies claiming they lose out on millions of dollars to piracy themselves do not regard highly of others copyrighted work.
Using tadXL functions such as tadMIRR, tadMIRRSchedule, tadXMIRR and tadXMIRRSchedule finding modified internal rate of return in Excel 2007, 2010 and 2013 becomes really easy. Let us now briefly look at each of these three financial functions in tadXL to find MIRR using Excel:
=tadMIRR( finance_rate, reinvestment_rate, cash_flows, type, compounding, period, distribution )
=tadMIRRSchedule( rates, cash_flows, type, compounding, period, distribution )
=tadXMIRR( finance_rate, reinvestment_rate, cash_flows, dates, type, compounding, period, distribution )
=tadMIRRSchedule( rates, cash_flows, dates, type, compounding, period, distribution )
Download tadXL to find MIRR in Excel