Excel IRR function is pretty basic in its features whereas one would desire more options in IRR calculation. These options include different compounding frequencies of interest, the different lengths for the time periods, the mid-year discounting to name a few. The good news however is the availablity of tadXL add-in that offers all such features in its financial functions including one for IRR. Here me, Abraham A, will take you through a guided tour to find internal rate of return using the Excel tadIRR function.
All tad Finance products are available for download at this link.
IRR is popular financial tool that helps an analyst find an investor's ROI - return on investment. It is widely used at corporations along with NPV - net present value and both these have correlation as at the IRR the NPV is zero. To find IRR manually requires burdensome calculations as there aren't any formulas to find an internal rate of return. Thus we have to resort to iterative techniques such as the popular Newton-Raphson method in finding an approximation of the IRR. This involves interative calculations that use a guess rate or a seed value and the results of these calculations are checked for convergence. If the values converge then we have found the IRR otherwise Excel will return a #NUM! error. If the iterative calculations encounted a division by zero then Excel will return #DIV0! error. The Excel IRR function lacks many features and options that are desired of IRR calculations such as use of compounding frequencies of interest and specification of periods of varying lengths. Another desired option includes the use of mid-year discounting convention as opposed to full-year discounting convention. All these features are readily available in a 3rd party Excel tadIRR function that is part of tadXL add-in for Excel 2007, 2010, and 2013.
Before we proceed to find internal rate of return 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 internal rate of return Excel function requires the following values as input:
=tadIRR ( cash_flows, guess, compounding, period, distribution, iterations, precision )
You can download a copy of 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.