Overview
- How can I find the IRR of cash flows?
- Does a project always have a unique IRR?
- Are there conditions that guarantee a project will have a unique IRR?
- If two projects both have a single IRR, how do I use the projects' IRRs?
- How can I find the IRR of irregularly spaced cash flows?
The net present value (NPV) of a sequence of cash flows depends on the interest rate ( r) used. For example, if we consider cashflows for projects 1 and 2 (see the worksheet IRR in the file IRR.xls, shown in Figure 9-1), we find that for r = 0.2, project 2 has a larger NPV, and for r = 0.01, project 1 has a larger NPV. When we use NPV to rank investments, the ranking of investments can depend on the interest rate. It is the nature of human beings to want to boil everything in life down to a single number. The internal rate of return (IRR for short) of a project is simply the interest rate that makes the NPV of the project equal to 0. If a project has a unique IRR, the IRR has a nice interpretation. For example, if a project has an IRR of 15 percent, we receive an annual rate of return of 15 percent on the cash flow we have invested. In this chapter's examples, we'll find that project 1 has an IRR of 47.5 percent, which means that the $400 we have invested at time 1 is yielding an annual rate of return of 47.5 percent. Sometimes, however, a project might have more than one IRR or even no IRR. In these cases, speaking about the project's IRR is useless.
Figure 9-1: Example of the IRR function.
How can I find the IRR of cash flows?
The Excel IRR function calculates internal rate of return. The function has the syntax IRR (range of cash flows, [guess]), where guess is an optional argument. If you do not enter a guess for a project's IRR, Excel begins its calculations with a guess that the project's IRR is 10 percent and then varies the estimate of the IRR until it finds an interest rate that makes the project's NPV equal 0 (the project's IRR). If Excel can't find an interest rate that makes the project's NPV equal 0, Excel returns #NUM.
In cell B5, I've entered the formula IRR(C2:I2) to compute project 1's IRR. Excel returns 47.5 percent. Thus, if we use an annual interest rate of 47.5 percent, project 1 will have an NPV of 0. Similarly, we find that project 2 has an IRR of 80.1 percent.
Even if the IRR function finds an IRR, a project might have more than one IRR. To check whether a project has more than one IRR, you can vary the initial guess of the project's IRR (say from -90 percent to 90 percent). I varied the guess for project 1's IRR by copying from B8 to B9:B17 the formula IRR($C$2:$I$2,A8). Because all the guesses for project 1's IRR yield 47.5 percent, we're fairly confident that project 1 has a unique IRR of 47.5 percent. Similarly, we can be fairly confident that project 2 has a unique IRR of 80.1 percent.
Does a project always have a unique IRR?
In the worksheet Multiple IRR in the file IRR.xls (see Figure 9-2), you can see that project 3 (cash flows of -20, 82, -60, 2) has two IRRs. I varied the guess about project 3's IRR from -90 percent to 90 percent by copying from C8 to C9:C17 the formula IRR($B$4:$E$4,B8).
Figure 9-2: Project with more than one IRR.
Note that for a guess of 30 percent or less, the IRR is -9.6 percent. For other guesses, we find an IRR of 216.1 percent. For both these interest rates, project 3 has an NPV of 0.
In the worksheet No IRR in the file IRR.xls (shown in Figure 9-3), you can see that no matter what guess we use for project 4's IRR, we receive the #NUM message. This message indicates that project 4 has no IRR.
Figure 9-3: Project with no IRR.
When a project has multiple IRRs or no IRR, the concept of IRR loses virtually all meaning. Despite this problem, however, many companies still use IRR as their major tool for ranking investments.
Are there conditions that guarantee a project will have a unique IRR?
If a project's sequence of cash flows contains exactly one change in sign, the project is guaranteed to have a unique IRR. For example, in projects 1 and 2, the sign of the cash flow sequence is - + + + + +. There is only one change in sign (between Time 1 and Time 2), so both projects have a unique IRR. For project 3, the signs of the cash flows are - + - +. Because the sign of the cash flows changes three times, a unique IRR is not guaranteed. For project 4, the signs of the cash flows are + - +.
Because the signs of the cash flows change twice, a unique IRR is not guaranteed in this case either. Most capital investment projects (such as building a plant) begin with a negative cash flow followed by a sequence of positive cash flows. Therefore, most capital investment projects will have a unique IRR.
If two projects both have a single IRR, how do I use the projects' IRRs?
If a project has a unique IRR, we can state that the project increases the value of the company if and only if the project's IRR exceeds the annual cost of capital. For example, if the cost of capital for a company is 15 percent, both projects 1 and 2 would increase the value of the company.
Suppose two projects are under consideration (both having unique IRRs), but we can undertake at most one project. It's tempting to believe that we should choose the project with the larger IRR. To see that this belief can lead to incorrect decisions, look at Figure 9-4 and the worksheet named Which Project in IRR.xls. Project 5 has an IRR of 40 percent, and project 6 has an IRR of 50 percent. If we rank projects based on IRR and can choose only one project, we would choose project 6. Remember, however, that a project's NPV measures the amount of value the project adds to the company. Clearly, project 5 will (for virtually any cost of capital) have a larger NPV than project 6. Therefore, if only a single project can be chosen, project 5 is it. IRR is problematic because it ignores the scale of the project. While project 6 is better than project 5 on a per-dollar-invested basis, the larger scale of project 5 makes it more valuable to the company than project 6. IRR does not reflect the scale of a project, while NPV does.
Figure 9-4: IRR can lead to an incorrect choice of which project to pursue.
How can I find the IRR of irregularly spaced cash flows?
Cash flows occur on actual dates, not just at the start or end of the year. The XIRR function has the syntax
XIRR(cash flow, dates, [guess])
The XIRR function determines the IRR of a sequence of cash flows that occur on any set of irregularly spaced dates. As with the IRR function, guess is an optional argument. As with the XNPV function, the XIRR function cannot be used unless you have installed the Analysis Toolpak. For an example of how to use the XIRR function, look at Figure 9-5 and sheet XIRR of the file IRR.xls.
No comments:
Post a Comment