Calculating and Interpreting Internal Rate of Return (IRR)

This post continues a discussion about cash flow, net present value, and interest rates and NPV, which you can read by clicking on the specific links. This post deals with the topic of internal rate of return.

IRR is an indicator of the efficiency or quality of an investment, as opposed to net present value (NPV), which indicates value or magnitude. It is the interest rate that results in a zero NPV when used as the discount rate. Or it is the discount interest rate for which the present worth of all cash inflows equals the present worth of all cash outflows. It is called internal rate of return because the money invested in this project generates (internally, within the project) exactly this rate of return. In particular for bonds, the IRR is called yield to maturity (YTM).

The IRR Rule

The IRR rule states that an investment is acceptable if the IRR exceeds the required rate of return (or the interest rate for discounting purposes) and it should be rejected otherwise. In other words, if a higher rate of return can be generated by some other projects (so the discount interest rate is higher than the IRR), then the project we currently analyze is not attractive. But if the IRR is higher then the alternative rate of return from another project, the current project is profitable.

If the project involves payments made over more than two periods, the IRR is very difficult (even impossible) to be solved for algebraically. Therefore in most cases the IRR is calculated by trial and error. Considering a particular discount interest rate one should calculate the NPV. If it is positive (negative), one should consider a higher (lower) discount interest rate and calculate the corresponding NPV. The procedure continues until the NPV approaches 0.

The problem raised by the IRR method for project evaluation is that there can be more IRRs for one project and in some situations it might be difficult to identify the appropriate one. Without entering in complicated financial details, the IRR is a good method if:

1. The project has “conventional” cash flows: the first cash flow (the initial investment) is negative and all the rest are positive.
2. We are not trying to compare mutually exclusive investment projects.

If one of the two conditions described above is not fulfilled the IRR method is not a safe method for project evaluation and the NPV method should be used instead. For example, if we try to find which project out of more mutually exclusive projects is more profitable, the correct answer is the one with the highest NPV (the largest difference between discounted revenues and discounted costs). However this is not necessarily the project with the highest IRR.

Excel calculates the IRR using a function with the same name. This function calculates the IRR iteratively starting with a guess value for the IRR, which is one of the arguments we need to specify in the function: = IRR(values, guess)

The first argument- “values” represents the range or the group of cells containing the stream of cash flows generated by the project (like the second argument in the NPV function). The IRR function is based, like the NPV function, on discounting the cash flows one period before the first cash flow occurs. We know already that the formulas for the NPV have to be slightly modified if we want need to calculate the NPV at the time of the first cash flow and not one period before that. However, the result of the IRR function is the discount rate for which the NPV one period before the first cash flow equals zero. This means that the NPV at the time of the first cash flow, which is just (1 + r) times the NPV for the previous period, will also equal O. So the result of the IRR function is not affected by the particular way Excel calculates the NPV.

This post is part of a series of articles on cash flow analysis. Visit our cash flow analysis page to find a summary of each method.