• Technology
  • CFO.com | US

Spreadsheets at Work: Rating Your Own IRR

Some tips for doing these key calculations; and introducing "modified" internal rate of return.

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.


Your email address will not be published. Required fields are marked *