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?
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?