Spreadsheets at Work: Rating Your Own IRR

Some tips for doing these key calculations; and introducing "modified" internal rate of return.
Richard Block and Jan BellFebruary 20, 2009

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.

Drive Business Strategy and Growth

Drive Business Strategy and Growth

Learn how NetSuite Financial Management allows you to quickly and easily model what-if scenarios and generate reports.

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.

Calculating internal rate of return on a spreadsheet

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.

Calculating internal rate of return on a spreadsheet

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.

As Exhibit 3a demonstrates, the MIRR function permits both a finance and reinvestment rate to be associated with the stream of cash outflows and inflows in our investment evaluation example. When both rates are 10 percent, the MIRR is 10 percent, the same as with the IRR function.

However, as can be seen in Exhibit 3b, the reinvestment rate mutes the overall rate of return (the MIRR) when it is lower than the finance rate, although it improves the overall rate when that rate is higher than the 10-percent finance rate previously calculated. This impact might imply that a terrifying increase in the number of additional investment scenarios must now be modeled. Not true. The rate of MIRR change for any series of re-investment rates is fairly constant as can be seen below and in our subsequent investment and savings examples.

Does changing the reinvestment rate have the same diminishing or enhancing effect on long term capital projects that are more complicated, or that are staggered, compared to the one presented in Exhibit 3a? In Exhibits 3c-1 and 3c-2, a more common multiyear investment project projection is presented, with cash savings per year increasing over future time periods. An ERP implementation would be an example of this type of investment and return: the planned ERP functionality providing greater efficiencies in outer years, as higher business transaction levels and reporting complexity can be handled more effectively than with today’s non-integrated computer systems.

Not surprisingly, these larger savings increase the IRR (Exhibit 3c-1), to 19.5 percent from the previous 10 percent. Once again, as demonstrated in Exhibit 3c-2, if the reinvestment rate is lower than the finance rate, the overall rate, the MIRR, is diminished and enhanced, but not on a one-for-one basis, when it is higher than the finance rate.

Take another common multiyear project, in which more than one cash outlay or investment is necessary. Sometimes the investments in subsequent years are anticipated, and therefore planned at the outset: our more-efficient lighting project in Exhibit 3a, but now requiring some new building wiring in year two; or a new piece of equipment requiring a major upgrade or overhaul in year two. In Exhibits 3d-1 and 3d-2, the original multiyear project is modified to reflect a second investment (cash outlay) in period 2, reducing the overall return on this project to 5 percent.

It bears repeating that the resulting overall rate of return of 5 percent (the IRR) assumes both the finance rate and the reinvestment rate are equal. If the re-investment rate falls below the new lower finance rate, the MIRR is reduced; if higher, the MIRR is enhanced, but not one for one.

Finally, there are projects with savings that don’t live up to their original hype, and fail to produce the expected savings in future years. Often, the discovery of lower-than-planned cash savings occurs just after the project is started — funded by an erroneous original investment projection, no doubt. An example of this type of impact on an investment plan: discovery of a configuration flaw soon after implementation in our ERP example in Exhibit 3c-1. The flaw results in higher ongoing maintenance costs (and lower saving), or the inability of the ERP solution to scale to the business transaction levels originally expected.

In Exhibits 3e-1 and 3e-2, this type of revised project expectation is portrayed. The cash savings are lower in future years, reducing the overall rate of return to 5 percent. And again, if the reinvestment rate falls below the finance rate, the overall MIRR falls; and if higher, it is increased, though not in lock-step with the reinvestment rate.

Typically, funded projects are ones that have been projected to yield high IRRs. It is these types of projects that are most affected by the naive or questionable assumption that a high reinvestment rate will be available, and will be achieved when cash inflows from savings are realized. Ask yourself, How realistic is planning for a reinvestment rate significantly higher than the company finance (WACC) rate? And what if the reinvestment rate is lower than the finance rate incurred in funding a highly touted project? Would it be funded at all?

In the next article in this two-part series, we will address issues related to optimistic or uneven cash-flow timing.

Richard Block is an adjunct professor of management accounting at Babson College and a CFO Leadership Partner at Tatum LLC, an executive consulting services firm. Dr. Jan Bell holds the Weiner Family Term Chair and is a professor of accounting at Babson College.

To download an Excel file containing the spreadsheets we reference in this article, click here.