How to Calculate IRR with Unequal Timing of Cash Flows

Calculating the internal rate of return, or IRR, of an investment is a powerful tool for businesses. When a manager is faced with a capital intensive decision, IRR can quickly compare the financial returns for any number of different investment choices to give a quantitative basis on which to make the decision.

The problem for many is that the math behind the IRR calculation can be quite complicated, particularly when the cash inflow and outflow from the project happens at irregular intervals. Thankfully, the process to calculate IRR in these cases can be don't fast and easy with the help of a spreadsheet

A short primer on IRR In math terms, the internal rate of return is the interest rate required to make the net present value of a series of cash flows equal to zero. Net present value is the current, lump sum value of a set of future cash flows, discounted for the time value of money.

Translated, IRR tells us the interest rate at which an investment returns cash to us. The IRR calculation is used most commonly as a tool to evaluate internal capital expenditures.

For example, a company may need to increase its manufacturing capacity. Management could be considering two options: expanding its manufacturing facility's square footage, or buying a new, space-saving piece of equipment. The best financial choice will be the one that achieves the increased productivity the fastest and with the least cost.

To quantitatively determine which option is the best financial choice, the company should calculate the IRR for both options and choose the option with the higher IRR.

A complex but powerful calculation There are several variables at play in this calculation, making the problem particularly complex -- if not impossible -- to solve with a pen and paper.

There is the initial cost of the project, in this case the budget to construct the expansion of the facility versus the price of the new equipment. Beyond the acquisition costs, the company may also have opportunity costs from reduced production while the construction is under way or while the new equipment is being installed and brought online.

There is also the element of time. The construction project could take weeks or even months to complete, during which time the company would not see any returns on its investment. The same is true for the time to order, ship, and install the new equipment.

Complicating things even more, the returns on this investment may not occur in regular intervals. Instead, the returns could come only when the company receives a specific order or reaches a certain volume for a period of time. This makes perfect sense in the business sense, but it adds a new level of sophistication to the IRR math.

At this point, the urge to panic is understandable. This math is far too complex for a modern manager to tackle by hand. Thankfully your handy spreadsheet program can make quick work of this problem.

Using Excel to calculate IRR with unequal timing of cash flows In the chart below, we have management's estimation for the initial cost and cash flow returns for both the expansion and new equipment options discussed above. Note that cash outflows, like the initial purchase cost, are shown as negatives while cash inflows over time are positive. This will be important later when we actually do the calculation.

The initial investment outflow is considerably higher for the expansion option. Because of the increase in space the company predicts that it will be able to achieve larger returns in absolute terms once the space is fully operational. The new machine option requires much less capital up front, and the time to receive cash inflows is much shorter than in the expansion option. However, the absolute returns are lower for the machine option as it has less overall capacity than the expanded plant would. In both cases, the timing of cash flows is irregular.

It is not immediately clear which project has the better IRR. For that, we'll need to use a special function in Excel.

Plugging the data into a spreadsheet, we'll use the XIRR function to calculate the internal rate of return for each project.

The XIRR function takes into account the cash flows, the dates, and a guess of the IRR to instantly compute the internal rate of return. In this example, 0.2 -- 20% -- was used as the IRR estimate to start the computation. You are not required to input a guess. Excel will default to a 10% IRR guess if one is not provided.

Doing this for both projects, we calculate that the facility expansion would have an internal rate of return of 14.5%, versus 26.5% for the new equipment option. Therefore, over this time period and with these estimated cash flows, the company should choose to buy the new equipment based on the IRR calculation.

It's important to remember that IRR is not the only consideration in important capital decisions. It doesn't take into account any risks, any market conditions, or any factors other than the projected cash flows. Because of this, IRR should be used as a tool in the decision making process, not as the end all and be all of capital allocation.

This article is part of The Motley Fool's Knowledge Center, which was created based on the collected wisdom of a fantastic community of investors. We'd love to hear your questions, thoughts, and opinions on the Knowledge Center in general or this page in particular. Your input will help us help the world invest, better! Email us at knowledgecenter@fool.com . Thanks -- and Fool on!

The article How to Calculate IRR with Unequal Timing of Cash Flows originally appeared on Fool.com.

Try any of our Foolish newsletter services free for 30 days. We Fools may not all hold the same opinions, but we all believe that considering a diverse range of insights makes us better investors. The Motley Fool has a disclosure policy.

Copyright 1995 - 2016 The Motley Fool, LLC. All rights reserved. The Motley Fool has a disclosure policy.