Excel MIRR function lacks the features one would want in modified IRR calculation, such options include use of compounding frequencies of interest, the different lengths for period, the mid-year discounting convention to name a few. The good news however is that tadXL add-in offers hosts of options that makes it robust and feature rich. Here me, Abraham A. will take you through a guided tour to find the modified IRR using the Excel tadMIRR function.
All tad Finance products are available for download at this link.
MIRR sounds like a Russian space station but in finance it refers to modified or adjusted internal rate of return. It is used when a regular IRR is not found or if there are multiple IRR values. The purpose of using Modified IRR in finance is to find an investor's ROI - return on investment. To find the MIRR manually you would have at first find the future value of benefits compounded at the reinvestment rate. You will also need the present value of costs discounted at the finance rate. The last step is to find the geometric average of compounded benefits over the discounted costs. This results in interest rate that may be used by an analyst to compare against the company's cost of capital. Excel MIRR function works only with limited options and leaves out features that are desired yet are not there. Some of these features include the option of compounding frequencies of interest and specification of periods of varying lengths. Other options such as the mid-year discounting conventions will be desired as well. All these features are readily available in tadMIRR function that comes with 3rd party tadXL add-in for Excel 2007, 2010, and 2013.
Before we proceed to find modified IRR in Excel, you would need the tadXL add-in which is available for download here. There are different versions of this add-in i.e. v0.5, v1.0, v2.0 and v2.5 and you would have to select one of them for installation.
The modified internal rate of return Excel function requires the following values as input:
=tadMIRR ( cash_flows, type, compounding, period, distribution )
You can download a copy of modified internal rate of return template here.
Finance tutorials on a variety of topics ranging from finding ROI using IRR, NPV, Payback period, etc. TVM calculations i.e. present value & future value of loans and bank deposits. Valuation & yield on stocks and bonds i.e. cost of equity, & debt. Rates of return on investments i.e. ARR, GRR, holding period return & yield.
Excel 4 finance to perform DCF analysis NPV, xNPV, IRR, xIRR, MIRR, xMIRR etc. Time value of money calculations i.e GRADIENT, INTEREST RATE, NPER, PMT, etc, yields and prices of bonds and stocks. Rates of return i.e. HPR, HPY, AHPR, and AHPY, etc.
TI BA II plus guide to analyze investments using capital budgeting methods i.e IRR, NPV, MIRR etc. TVM calculations i.e. RATE, NPER, PMT, PV, FV. Interest rate factors i.e. PVIFA, PVIF, FVIFA, FVIF, etc .
Finance tables to find interest factors for present value and future value of $1 and of annuities with start of period and end of period payments in amount of $1. Interest rates for growing and shrinking annuities are available as well.