Reading: Internal Rate of Return (IRR)

Example 1 before has shown how to compute and interpret the IRR of an investment project that lasts only one period. But how to compute the IRR of a multi-period project? There, the math gets a bit trickier… Remember from before, that the IRR corresponds to the discount rate that sets the project NPV to zero. We can therefore generalize the computation of the IRR for a project that lasts T periods as follows:

\( NPV = C_0 + \frac{C_1}{(1+IRR)}+ \frac{C_2}{(1+IRR)^2}+...+ \frac{C_T}{(1+IRR)^T} = 0 \)

To find the project's IRR, we would have to solve the above expression for IRR, which is very difficult, often inconclusive, and sometimes even impossible. Therefore, in reality, the actual calculation of IRR often involves trial and error, or it is conducted with spreadsheet programs such as Microsoft Excel.

Example 2

Let us consider a project that promises the following cash flows:

What's the project's IRR?

Trial and error

To solve the problem with trial and error, we would simply use different discount rates to capitalize the project cash flows. Eventually, this would allow us to find the project's IRR. For example, we could start with a discount rate of 10% and compute the corresponding project NPV of 892.6:
  

\( NPV_{10\%} = -12'000+\frac{6'000}{1.1}+\frac{9'000}{1.1^2} = 892.6 \)

 
Since the NPV is positive at a discount rate of 10%, the project's IRR will be larger than 10%. Therefore, the next trial could apply a discount rate of 20%, which results in a NPV of −750:

\( NPV_{20\%} = -12'000+\frac{6'000}{1.2}+\frac{9'000}{1.2^2} = -750.0 \)

Since this NPV is negative, we know that the IRR will be smaller than 20%, but larger than 10%. So the next trial should involve a discount rate between 10% and 20%, etc. After many trials, we would figure out that the project's  NPV is exactly zero when we discount the future cash flows at a cost of capital of 15.1%:

\( NPV_{15.1\%} = -12'000+\frac{6'000}{1.151}+\frac{9'000}{1.151^2} = 0 \)

Consequently, the project's IRR is 15.1%. Equipped with this knowledge, investors can now decide whether an annual expected return of 15.1% is appropriate given the riskiness of the project.

Needless to say that trial and error is not a very elegant way to solve this problem. The more elegant way to go is to team up with Excel's "IRR" function.

Excel's IRR function

Microsoft Excel has a built-in function "IRR" that allows you to easily compute a project's IRR. To trigger that function, you go to the desired output cell and enter:
 

=IRR(

then you simply mark the cash flow stream in question, close the parentheses, and press enter. Excel now shows you the IRR that sets the NPV of the cash flow stream equal to zero. 

The following Excel screenshot summarizes this procedure:

• The relevant cash flows are in cells C3 to E3
• We want Excel to display the IRR in cell C5
• To compute this IRR, we enter in cell C5: =IRR(C3:E3). This tells Excel to compute the IRR of the cash flow stream that goes from C3 to E3.
• The result is an IRR of 15.1%.

Once we have the cash flows in Excel, it is therefore only a minor step to compute their IRR. Because it is so simple to compute and communicate the IRR, we should always report it when presenting investment proposals.