The Internal Rate of Return (IRR) provides an indication of the profitability of an investment and therefore, is commonly used in business, when choosing between investments.
This measurement uses a series of cash flows (an initial investment, along with the net income) over a number of periods, to calculate the compounded return, assuming the Net Present Value of the investment is zero.
where the series of cash flows provide the values for Cn and N is the number of periods over which the returns have been made.For a full explanation of the IRR, see the Wikipedia Internal Rate of Return page
The Excel IRR function returns the Internal Rate of Return for a supplied series of periodic cash flows (i.e. an initial investment value and a series of net income values).
The syntax of the function is:
Where the arguments are as follows:
|values||-||This must contain at least one negative and at least one positive value.|
|[guess]||-||(Note: This is only a value for Excel to start off working with - Excel then uses an iterative procedure to converge to the IRR).|
In the spreadsheet below, an initial investment of $100 is shown in cell B1 and the net income over the next 5 years is shown in cells B2-B6.
The IRR function in cell C2 shows the calculation of the Internal Rate of Return after 3 years and the function in cell C4 shows the Internal Rate of Return after 5 years.
For further information and examples of the Excel Irr function, see the Microsoft Office website.
If you get an error from the Excel Irr function, this is likely to be the #NUM! error: