Excel tools to demonstrate

IRR Function

Companies often have investment opportunities where they pay initially and then get returns in the future. When discussing NPV, I illustrated in the “Try it!” exercise that the NPV of such an investment decreases as the discount rate increases. In fact, for large enough discount rates, the NPV will typically become negative, meaning that the future returns are not enough to offset the initial cost. The discount rate at which NPV changes from positive to negative is called the internal rate of return, or IRR. Specifically, the IRR is the discount rate at which NPV equals 0. Companies are interested in IRR for the following reason. They typically have a hurdle rate that they use to discount potential investments. If the NPV of an investment is positive, discounted at the hurdle rate, it is worth pursuing. If it is negative, it is not worth pursuing. Stated equivalently, the investment is worth pursuing only if its IRR is greater than the company’s hurdle rate.

Fortunately, Excel has an IRR function that calculates an investment’s IRR—a tough calculation. As with the NPV function, we assume the investment is structured so that there is an initial cash payment at the beginning of year 1 and then regular cash returns at the ends of years 1, 2, and so on.

To use the IRR function:

Enter the formula =IRR(cashstream,rateguess), where cashstream is a stream of cash flows, where the first (the initial payment) should be negative, and rateguess is an initial guess for the IRR.

This seems strange. Why should you have to guess at the answer? It is because Excel calculates the IRR iteratively, starting with your guess. Your actual guess shouldn’t make any difference in the final answer except in unusual cases.

Try it! Find the IRR for the following investment, using an initial guess of 15%. What does this IRR say about the attractiveness of the investment? (Scroll down to see the answer.)