Spreadsheets and IRR: It’s All in the Timing

Overoptimistic or uneven payments can hurt calculations. In the second part of our internal-rate-of-return review, we show how XIRR can help.
Richard Block and Jan BellFebruary 27, 2009

In part one of our exploration of how using spreadsheets effectively can improve internal rate of return calculations, we looked at the sometimes-improper assumption that IRR cash inflows will be reinvested at a rate equal to the IRR. Today we address a second hidden IRR obstacle: cash flow timing.

The rate functions of IRR and of the Modified Internal Rate of Return, or MIRR, both assume that project cash inflows and outflows occur evenly and equally over annual periods of 365 days each. (MIRR, also available in a financial spreadsheet, permits both a finance and reinvestment rate to be associated with a stream of cash outflows and inflows.)

But is it realistic to assume that evenness? Couldn’t the receipt or realization of a project’s expected savings three years from now slip by 30 days, 60 days, even 90 days? Of course. And what would be the impact of that delay on the rate of return?

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.

In Exhibit 4 the annual cash flows from the $100,000 project that we posited in the first article are presented again, along with the IRR and MIRR they yield.

In Row 13 the dates of the cash inflows are modified to reflect a 90-day slip in the period between each expected cash inflow or savings. Over the life of the planned project these 90-day slips accumulate to delay the end of the project by a full year. The original project end date of 12/31/2012 is exactly four years from 1/1/2009, the project commencement date, see Row 5.  If we indeed incur a 90-day slip per cash flow period, the new project end date is 1/1/2014, a full year later than plan.

The IRR and MIRR functions can’t easily address uneven timing for the payments. However, an even less-known rate of return function can. It is called XIRR. If not many know about this electronic spreadsheet financial function, it’s because it appears 49th out of the 53 financial functions in Microsoft Excel 2007 — probably because the functions are listed in alphabetical order. While foregoing the nuances of finance and reinvestment rates addressed with the MIRR function, the XIRR function addresses uneven (non-annual) timing issues.

In Row 11 of Exhibit 4, the XIRR function is used to compute the rate of return on our $100,000 investment evaluation example. It incorporates both the cash flow stream and the dates of each stream. When the dates are exactly one year apart (Row 5), XIRR computes the same rate of return as the IRR and MIRR functions.

However, when we change the dates to reflect 90-day delays (Row 13), the same total cash flow stream, which now takes five years versus four years to be realized, yields a rate of return of 7.93 percent, over a two-point reduction from our original (and possibly naïve) rate. This surprising result demands that we then ask: Would this project have been funded if an honest estimate of the period cash flows savings had been made initially? 

To further demonstrate the impact of timing delays on long term investment rates of return, Exhibit 5 highlights rates of return on our sample investment project impacted by delays ranging from four one-month (30-day) delays, to four four-month (120-day) delays. Not realizing expected cash savings by as little as 30 days reduces the rate of return of this project by almost a point!

And if the cash savings realization from the project is delayed 16 months (not an unusual or unreasonable situation), the overall rate of return on the project is reduced by almost 2.6 points, from 10 percent, down to 7.4 percent. The conclusion from Exhibit 5 is painfully obvious: Planning for and then managing reasonable timeframes for realized cash savings ought to be a key criterion for any CFO or financial executive when evaluating the viability of long term projects ranked by rates of return.

In in October 2004, McKinsey consultants John C. Kelleher and Justin J. MacCormack also tackled this issue in an article titled “Internal Rate of Return: A Cautionary Tale.” The article concluded: “Despite flaws that can lead to poor investment decisions, IRR will likely continue to be used widely during capital-budgeting discussions because of its strong intuitive appeal. Executives should at least cast a skeptical eye at IRR measures before making investment decisions.”

We whole-heartedly concur. It is unfortunate that there is no one rate-of-return function that addresses both the deficiencies at once. If you are about to either prepare or evaluate long-term projects based on expected cash flows ranked by some form of a rate of return, we hope that we have heightened your awareness to the subtle pitfalls buried within these types of financial evaluations and maybe given you some extra ammunition so as to avoid most of them.

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.

Reference: Management Accounting, Fifth Edition, Atkinson, Kaplan, Matsumura, and Young; 2007, Pearson Prentice Hall, was used as a general reference for this article.

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