• 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.

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.

Discuss

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