Chapter 8: Internal Rate of Return

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 each have a single IRR, how do I use the projects’ IRRs?

• How can I find the IRR of irregularly-spaced cash flows?

• What is the MIRR and how do I compute it?

The net present value (NPV) of a sequence of cash flows depends on the interest rate (r) used. For example, if we consider cash flows for Projects 1 and 2 (see the worksheet IRR in the file IRR.xlsx, shown in Figure 8-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 outcome 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) 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 8-1: Example of the IRR function

• How can I find the IRR of cash flows?

• The Microsoft Office Excel 2007 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 (for example, 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.xlsx (see Figure 8-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 8-2: Project with more than one IRR

• Note that when a guess is 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.xlsx (shown in Figure 8-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 8-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, for Project 2 in worksheet IRR, the sign of the cash flow sequence is – + + + + +. There is only one change in sign (between Time 1 and Time 2), so Project 2 must have a unique IRR. For Project 3 in worksheet Multiple IRR, 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 in worksheet No IRR, 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 each 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 Project 1 and Project 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 illustrate that this belief can lead to incorrect decisions, look at Figure 8-4 on the next page and the Which Project worksheet in IRR.xlsx. 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 one project can be chosen, Project 5 is it. IRR is problematic because it ignores the scale of the project. Whereas 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, whereas NPV does.

  
  Figure 8-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. For an example of how to use the XIRR function, look at Figure 8-5 and worksheet XIRR of the file IRR.xlsx.

  
  Figure 8-5: Example of the XIRR function

• The formula XIRR(E3:E7, D3:D7) in cell D9 shows that the IRR of Project 7 is 12.97 percent.

• What is the MIRR and how do I compute it?

• In many situations the rate at which a company borrows funds is different than the rate at which the company reinvests funds. IRR computations implicitly assume that the rate at which a company borrows and reinvests funds is equal to the IRR. If we know the actual rate at which we borrow money and the rate at which we can reinvest money, then the modified internal rate of return (MIRR) function computes a discount rate that makes the NPV of all our cash flows (including paying back our loan and reinvesting our proceeds at the given rates) equal to 0. The syntax of MIRR is MIRR(cash flow values,borrowing rate,reinvestment rate). A nice thing about MIRR is that it is always unique! Figure 8-6 in worksheet MIRR of file IRR.xls contains an example of MIRR. Suppose we borrow $120,000 today and receive the following cash flows: Year 1: $39,000 Year 2: $30,000 Year 3: $21,000 Year 4: $37,000 Year 5: $46,000. Assume we can borrow at 10 percent per year and reinvest our profits at 12 percent per year.

  
  Figure 8-6: Example of the MIRR function

• After entering these values in cells E7:E12 of worksheet MIRR we find the MIRR in cell D15 with the formula MIRR(E7:E12,E3,E4). Thus, our project has an MIRR of 12.61 percent. In cell D16 we computed the actual IRR of 13.07 percent.