How to calculate the internal rate of return

The internal rate of return (IRR) is the rate of return at which the present value of a series of future cash flows equals the present value of all associated costs. IRR is commonly used in capital budgeting, to discern the rate of return on the estimated cash flows arising from a prospective investment. A project having the highest IRR is selected for investment purposes (subject to other considerations). The easiest way to calculate the internal rate of return is to open Microsoft Excel. Then follow these steps:

  1. Enter in any cell a negative figure that is the amount of cash outflow in the first period. This is normal when acquiring fixed assets, since there is an initial expenditure to acquire and install the asset.

  2. Enter the subsequent cash flows for each period following the initial expenditure in the cells immediately below the cell where the initial cash outflow figure was entered.

  3. Access the IRR function and specify the cell range into which you just made entries. The internal rate of return will be calculated automatically. It may be useful to use the Increase Decimal function to increase the number of decimal places appearing in the calculated internal rate of return.

As an example of an internal rate of return calculation, a company is reviewing a possible investment, for which there is an initial expected investment of $20,000 in the first year, following by incoming cash flows of $12,000, $7,000 and $4,000 in the next three years. If you input this information into the Excel IRR function, it returns an IRR of 8.965%.

The IRR formula in Excel is extremely useful for quickly deriving a possible rate of return. However, it can be used for a less ethical purpose, which is to artificially model the correct amounts and timing of cash flows to produce an IRR that meets a company's capital budgeting guidelines. In this case, a manager is fudging the results in his or her cash flow model in order to gain acceptance of a project, despite knowing that it may not be possible to achieve those cash flows.

While the internal rate of return is useful for estimating the return on projected cash flows, it does not account for other factors, which may be more important to someone evaluating capital budgeting proposals. For example, it may be more important to upgrade the capacity of a bottleneck operation, irrespective of any related cash flows, or to comply with a legal requirement to reduce pollution emissions. In these cases, the presence of IRR information does not influence the final investment decision made, and does not even need to be calculated.

Related Courses

Capital Budgeting
Excel Formulas and Functions
Financial Analysis