Spreadsheets at Work: Rating Your Own IRR

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?

• « previous
• 1
• 2
• 3
• next »