Questions answered in this chapter:
■ 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 you consider cash flows for Projects 1 and 2 (see the worksheet IRR in the file IRR.xlsx, shown in Figure 9-1), you find that for r=0.2, Project 2 has a larger NPV, and for r=0.01, Project 1 has a larger NPV. When you 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, you receive an annual rate of return of 15 percent on the cash flow you invested. In this chapter’s exam-ples, you’ll find that Project 1 has an IRR of 47.5 percent, which means that the $400 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.
64 Microsoft Excel 2010: Data Analysis and Business Modeling
Answers to This Chapter’s Questions
How can I find the IRR of cash flows?
The 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 entered the formula IRR(C2:I2) to compute Project 1’s IRR. Excel returns 47.5 percent. Thus, if you use an annual interest rate of 47.5 percent, Project 1 will have an NPV of 0. Similarly, you can see 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 copy-ing 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, I can be fairly confident that Project 1 has a unique IRR of 47.5 percent.
Similarly, I 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 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 when a guess is 30 percent or less, the IRR is –9.6 percent. For other guesses, the IRR is 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 9-3), you can see that no matter what guess you use for Project 4’s IRR, you receive the #NUM message. This message indicates that Project 4 has 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.
FIGURE 9-3 Project with no IRR.
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 the 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 the 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 the worksheet No IRR, the signs of the cash flows are + – +. Because the signs of the cash flows change twice, a unique IRR is not guaran-teed 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 do 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, you 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.
66 Microsoft Excel 2010: Data Analysis and Business Modeling
Suppose two projects are under consideration (both having unique IRRs), but you can under-take at most one project. It’s tempting to believe that you should choose the project with the larger IRR. To illustrate that this belief can lead to incorrect decisions, look at Figure 9-4 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 you rank projects based on IRR and can choose only one project, you 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 valu-able to the company than Project 6. IRR does not reflect the scale of a project, whereas 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. For an example of how to use the XIRR function, look at Figure 9-5 and worksheet XIRR of the file IRR.xlsx.
FIGURE 9-5 Example of the XIRR function.
The formula XIRR(F4:F7,E4:E7) in cell D9 shows that the IRR of Project 7 is -48.69 percent.
What is the MIRR and how do I compute it?
In many situations the rate at which a company borrows funds is different from 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 9-6 in worksheet MIRR of file IRR.xls contains an example of MIRR. Suppose you 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 you can borrow at 10 percent per year and reinvest your profits at 12 percent per year.
After entering these values in cells E7:E12 of worksheet MIRR, you can find the MIRR in cell D15 with the formula MIRR(E7:E12,E3,E4). Thus, this project has an MIRR of 12.61 percent. In cell D16 I computed the actual IRR of 13.07 percent.
FIGURE 9-6 Example of the MIRR function.
I’ll close by noting that if a cash flow is left blank, the IRR function ignores both the cash flow and the period. If a cash flow is left blank, the IRR function will return a #NUM error.
Problems
1. Compute all IRRs for the following sequence of cash flows:
Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 –$10,000 $8,000 $1,500 $1,500 $1,500 $1,500 –$1,500
2. Consider a project with the following cash flows. Determine the project’s IRR. If the annual cost of capital is 20 percent, would you undertake this project?
Year 1 Year 2 Year 3
–$4,000 $2,000 $4,000
68 Microsoft Excel 2010: Data Analysis and Business Modeling 3. Find all IRRs for the following project:
Year 1 Year 2 Year 3
$100 –$300 $250
4. Find all IRRs for a project having the given cash flows on the listed dates.
1/10/2003 7/10/2003 5/25/2004 7/18/2004 3/20/2005 4/1/2005 1/10/2006
–$1,000 $900 $800 $700 $500 $500 $350
5. Consider the following two projects, and assume a company’s cost of capital is 15 per-cent. Find the IRR and NPV of each project. Which projects add value to the company?
If the company can choose only a single project, which project should it choose?
Year 1 Year 2 Year 3 Year 4
Project 1 –$40 $130 $19 $26
Project 2 –$80 $36 $36 $36
6. Twenty-five-year-old Meg Prior is going to invest $10,000 in her retirement fund at the beginning of each of the next 40 years. Assume that during each of the next 30 years Meg will earn 15 percent on her investments and during the last 10 years before she retires, her investments will earn 5 percent. Determine the IRR associated with her investments and her final retirement position. How do you know there will be a unique IRR? How would you interpret the unique IRR?
7. Give an intuitive explanation of why Project 6 (on the worksheet Which Project in the file IRR.xlsx) has an IRR of 50 percent.
8. Consider a project having the following cash flows:
Year 1 Year 2 Year 3
–$70,000 $12,000 $15,000
Try to find the IRR of this project without simply guessing. What problem arises? What is the IRR of this project? Does the project have a unique IRR?
9. For the cash flows in Problem 1, assume you can borrow at 12 percent per year and invest profits at 15 percent per year. Compute the project’s MIRR.
10. Suppose today that you paid $1,000 for the bond described in Problem 9 of Chapter 8,
“Evaluating Investments by Using Net Present Value Criteria.” What would be the bond’s IRR? A bond’s IRR is often called the yield of the bond.
69