The Microsoft Office Suite has a wide range of software that is essential for any business to run properly and one such application in the suite is Microsoft Excel. Excel is the dominant spreadsheet application and business tool that almost every firm uses nowadays. Because of its easy to use features and host of functions complementing every need that the Excel consultant and business user might have, Excel is not only preferred in offices but also in schools, colleges and government agencies. One such function that it possesses is the IRR function. In this free Excel help article we will discuss in detail the Microsoft Excel IRR function.
The IRR function or the Internal Rate of Return as it stands for helps to calculate the internal rate of return on a periodic flow of cash or investments. The amount of cash flows need not be even in nature as they are calculated on the basis of annuity and so it is compensated for at the end. But one thing must be there is that all the cash flows should be of regular intervals, either once a month or a year, for the rate to be properly evaluated. The cash flows are a series of investments, both payments and income and are preceded by an initial investment. The function also has to be provided with an initial estimate of the internal rate by the user for it to work on.
The IRR function takes two parameters and is written as IRR (values, guess). Here is a brief description about the two arguments:
Values: The values field is where the Excel consultant or business user has to provide the function with all the different cash flows in terms of payment and investments. Usually payments are calculated as negative and incomes as positive due to the direction of cash flow. So, the initial investment being positive and the payment being negative, the values field has to have at least one positive and one negative cash flow associated with it. So this field is required to be provided by the Excel consultant or business user.
Guess: The guess field is where the consultant can put down an estimated internal rate of return on his sequence of investments. This initial guess rate is the one which the function dwells upon and interactively figures out the actual rate of return based on the cash flows. But implicitly the function takes this initial guess to be 10% or 0.1 and therefore the guess field can also be left empty by the user.
The IRR function calculates the internal rate of return by an intricate process of iterations and the mathematical expression can be calculated as to be:
? C n /(1+r) n = 0
Here, Cn is the sequence of cash flows provided by the user, N is the number of periods over which the cash flows have been made and r is the rate of return calculated.
Below we have accumulated some of the features that are present in the IRR function and some of the errors that are commonly encountered by users:
IRR function makes use of the sequence in which the cash flows are provided to it and therefore it is vital to maintain a proper order of the investments and payments otherwise erroneous results may be shown.
The values field of the IRR function only accepts numerical values and all other inputs that are provided like alphabets, texts, logical symbols and empty fields are all ignored.
The function makes the use of continuous iterations for calculating the actual internal rate of return and for doing so it starts on the guess field that is provided by the user or if not, the value it takes implicitly. Usually there is an upper cap on the limit of tries it performs before declaring it accurate, about 0.00001 percent and failing to do so after that result in an error.
The IRR function shows the #NUM! Error if it fails to compute an effective internal rate even after the upper cap number of tries it performs (usually 20) and asks the user to recomputed using a different initial guess.
The IRR is closely related to the Net Present Value (NPV) and is the rate at 0 NPV.
The IRR function is found in all the modern Excel versions like the Excel 2000, 2003, 2007, 2008, 2010, and 2011.