Spreadsheets at Work: Rating Your Own IRR

It is budgeting season again. Financial analysts are completing their analyses of the R&D or capital spending projects being proposed. And financial executives are either anxiously awaiting those analyses, or already getting started on their reviews. No doubt the analyses include investment costs, anticipated future savings, discounted cash flows, computed internal rates of return, and a ranking of which projects make the "cut," and which do not.

Almost certainly, a spreadsheet was used for each project — to compute the discounted cash flows, the internal rates of return, and the presentation of the overall rankings.

You will take comfort, of course, because these analyses, and your decision on which projects to accept or fund, were based on a sound financial principle: namely, the better the internal rate of return, the better the project.

But is that comfort warranted? Or might you be vulnerable to the weaknesses long pointed out — if too often ignored — by researchers who have warned that IRR calculations often contain built-in reinvestment assumptions that improperly improve the appearance of bad projects, or make the good ones look too good .

IRR, of course, is the actual compounded annual rate of return from an investment, often used as a key metric in evaluating capital projects to determine whether an investment should be made. IRR also is used in conjunction with the Net Present Value (NPV) function, determining the current value of the sum of a future series of negative and positive cash flows; namely investments and savings. The prescribed discount factor to be used in computing NPV is the company's weighted average cost of capital, or WACC. The internal rate of return is the annual rate of return, also known as the discount factor, which makes the NPV zero.

The rub in justifying long-term project funding decisions by using IRR is two-fold. First, IRR assumes that interim cash inflows, or savings, will be "reinvested," and will produce a return — the reinvestment rate — equal to the "finance rate" used to fund the cash outflows (the investment.) Second, the anticipated investment cash outflows required for the project, and for the anticipated cash inflows from savings once the project is complete, are so far in the future that their timing is difficult to determine with reasonable accuracy.

Is the reinvestment-at-the-same-rate assumption true? It may not be, when interim cash inflows occur far in the future, or if there is limited available capital to fund competing projects. Is timing important? Yes, it is vital. A change in the expected receipt of future cash inflows by as little as 30 days has a significant impact on the computed IRR.

But by knowing and using the subtleties of the various IRR functions available in an electronic spreadsheet, we can safeguard ourselves against miscalculations based on faulty assumptions, and minimize the range of error by early detection of faulty assumptions.

In this article, part one of a two-part series, we will study the reinvestment issue. The second article will address how to reduce inaccuracies — minimizing the range of error — based on timing concerns.

In Exhibit 1 (below), we portray a simple multiyear investment and return, in which a single cash outflow (investment) of $100,000 occurs in period 0, and anticipated annual cash inflows (savings) of $31,547 are planned during each of the next four years. After four years of savings, the expected total savings is $126,188. Replacing less-efficient incandescent or older fluorescent lighting in an office building with more efficient compact fluorescent lighting is one example of this type of investment.

In the examples portrayed, cash outflows or investments are presented as negative numbers; cash inflows or savings are presented as positive numbers. This "sign convention" is dictated by specific functionality requirements within Microsoft Excel. Further, when the present value of these period-specific cash flows is computed, Microsoft Excel reverses the sign.

In Exhibit 2 (below), using the IRR function, the IRR for this stream of cash outflows and inflows is determined to be an even 10 percent. To further prove that this IRR of 10 percent produces a Net Present Value of $0 for these cash flows, each specific annual cash flow has been discounted using 10 percent, producing a net sum of $0.

Also noted is the number of years (3.17) it takes the savings to pay back the initial investment. The payback method is another mechanism for evaluating an investment. More simplistic perhaps, but the longer it takes for the savings to pay back the investment, the greater the inherent investment risk.

So, how "good" is this IRR of 10 percent? It is only accurate if both the finance rate funding the investment and the reinvestment rate from future savings are both 10 percent. If one wants to change the reinvestment rate and assess the impact, one should use a lesser-known function in the electronic spreadsheet tool suite: the Modified Internal Rate of Return (or MIRR) function.

• 1
• 2
• 3
• next »