Using internal rate of return to evaluate technology projects

One of the most basic and most important functions of technology management is to evaluate projects to determine whether your organization should invest time and resources in them. As fundamental as this function is, too many people do it the wrong way. I initially wrote a draft version of this post that outlined a variety of wrong ways to evaluate projects, but I gave up on it because it was too depressing. Instead of that, let's get right to it. The best practical way to evaluate technology projects is to calculate the proposed project's internal rate of return. In the rest of this post, I'll explain what the internal rate of return (IRR) is, show how to calculate it, point out a few technical caveats, discuss some real-world considerations and offer a sample spreadsheet that you can use for your own projects.

IRR explained

Internal rate of return is defined in terms of net present value (NPV), so let's look at that first. NPV is the sum of the present value of all the cash flows associated with a project, and present value is based on the idea that a dollar today is worth more than a dollar tomorrow. Mathematically speaking, the present value (PV) of a cash flow is equal to:Net cash flow for a period of time / (1 + discount rate) ^ the time period in which the cash flow is receivedConsider the following simplified example. You can make a website that will bring in $6,000 per year in membership fees for the next three years. It will cost you $10,000 to build and deploy the site, and you will spend $1,000 per year in ongoing support costs. You can borrow money from the bank at a 10-percent interest rate. What's the NPV?

First we calculate the individual PVs:
Year 0 (initial investment) = -$10,000 / ((1 + .10) ^ 0 )= -$10,000
_
Year 1 = ($6,000 - $1,000) / ((1 + .10) ^ 1) = $4,545.45
_Year 2 = ($6,000 - $1,000) / ((1 + .10) ^ 2) = $4,132.23
_
Year 3 = ($6,000 - $1,000) / ((1 + .10) ^ 3) = $3,756.57_

Add the PVs to get the NPV, and you get $2,434.26. This means your project will make money, so you should build the website. If you could only bring in $5,000 per year instead of $6,000 per year, you would end up with a negative NPV, which means you'd lose money.The decision rule with NPV is:
NPV > 0 - Do the project!
_
NPV < 0 - Don't do the project!
_NPV = 0 - Doing the project (or not doing the project) will have no financial impact.
_
(Wikipedia has more on NPV here.)_

Now that you understand NPV, you're ready for this definition of IRR. A project's internal rate of return is the discount rate that would result in a net present value of $0. That is, the IRR is the cost of capital for which you are indifferent to undertaking a project. Thus an actual cost of capital less than the calculated IRR means your project will have a positive NPV, and an actual cost of capital greater than the calculated IRR means your project will have a negative NPV. If you calculate an IRR equal to your cost of capital, your project's NPV is $0 by definition.

This results in the following decision rule:
IRR > cost of capital - Do the project!
_
IRR < cost of capital - Don't do the project!
_IRR = cost of capital - Doing the project (or not doing the project) will have no financial impact.

Calculating IRR

Calculating IRR is harder than calculating NPV because you have to solve for the discount rate where NPV equals $0. For the website example above, if you let "r" represent the interest rate, the math starts out looking like this:(-$10,000 / ((1 + r) ^ 0)) + (($6,000 - $1,000) / ((1 + r) ^ 1)) + (($6,000 - $1,000) / ((1 + r) ^ 2)) + (($6,000 - $1,000) / ((1 + r) ^ 3)) = $0I'll leave it to you to solve for r.If you're not into using the secant method to find roots, you're in luck because popular spreadsheet programs have an IRR function. In both Excel and OpenOffice/LibreOffice Calc, you can calculate IRR by putting the cash flows in separate cells and then passing the cell range to the function. For example, our website project from earlier would be entered like this:A1: -10,000A2: 5,000A3: 5,000A4: 5,000A5: =IRR(A1:A4)The output would be 23%.(Wikipedia has more on the calculation of IRR here.)

Caveats

Now that you know how to use IRR and how to calculate it, you're almost ready to start evaluating projects, but first you need to be aware of a few caveats. The primary problem from a technology management perspective has to do with the evaluation of mutually exclusive projects because IRR can only tell you whether a project will be profitable, but it can't tell you how profitable it will be.

For example, let's say you are considering two projects. The first is the previously discussed website project, and the second is a much larger one in which all the cash flows are 10 times as large as the first ($100,000 investment and $50,000 net cash flow per year). Each project will have the same IRR, but the second one has a greater net present value, so if you can't do both, you should pick the second one.

From a mathematical perspective, there are two additional things to consider. First, IRR assumes you're reinvesting returns in projects with equal rates of return. This means a project with an exceptionally high IRR could overstate the annual return because your organization may not have other projects with comparable rates of return in which to invest. Second, because IRR doesn't consider the cost of capital like NPV does, you shouldn't use it to compare projects of different durations.

Caveats aside, IRR is relatively easy for all stakeholders to understand because you can say, "we need to get X-percent rate of return to make it worth our while to do this." Also, while NPV can be considered the gold standard, you, as a tech manager, probably don't know your organization's actual cost of capital, so it would be difficult to accurately determine NPV.

Real-world considerations

First, you need to determine what kinds of things you'll subject to IRR evaluation. In a purely internal, IT-as-cost-center organization, I draw the line at 40 man-hours of work or equivalent cash expenditures. Anything less than this isn't worth the effort of calculating all the cash flows. I also excluded report writing and ongoing maintenance, even if these would exceed 40 man-hours of work over a relatively short duration (and yes, I've dealt with many a report that went well beyond 40 hours).

Second, the IRR you calculate likely won't be the project's actual rate of return because you're not going to get the cash flows right before you even start working on a project. Rare is the person who requests a project and doesn't overestimate its ability to make or save money, and if you're managing just the technology side of things, your ability to guarantee exacting accuracy at this point will be limited. Similarly it can be difficult to estimate your resource costs accurately. If you have a team of programmers, business analysts and QA analysts, you'll have to figure out how allocate their costs appropriately. I generally prefer to come up with some sort of weighted average hourly resource cost and use that instead of figuring out the actual costs down to the penny.

Third, it's tempting to completely dismiss internal labor costs because you might think of people who are already on the payroll as being free. This fails to consider that people working on a project incurs an opportunity cost in that there's something else they can't be doing instead. On the other side of the balance sheet, you'll want to consider the dollar value of people's time saved.

Fourth, you probably don't know your actual cost of capital. Calculating an organization's cost of capital goes well beyond knowing the interest rate for a bank loan and is a subject better suited for a finance blog than a technology blog. Let's just say that if you determine a project IRR in excess of 15 or 20 percent, you should probably do it. An IRR of less than 15 percent is definitely a gray area.

Fifth, depending on your organization's political realities, some projects may never be subjected to an evaluation. For example, if IT reports up through the CFO, something near and dear to the CFO's heart is probably going to get done regardless of whether it's going to make money for the company. Of course, if your CFO is pushing money-losing projects, you've got a serious problem that an IRR spreadsheet isn't going to solve.

Ultimately the internal rate of return evaluation may end up being more of a starting point for discussion than some sort of omniscient number, and that might be good enough for a lot of organizations. I've worked in a number of situations in the past where nobody ever thought through the financial implications of IT projects. If you're in a similar situation and you can at least get people thinking about dollars and cents as part of the process, you'll have improved things considerably.

Sample

Here's a sample Excel file for calculating a project's IRR: irr_sample.xlsx (14.05 kb). It has a basic worksheet that breaks out cash flows as cost savings, revenue generation or investment. I've filled it out for the $10,000 website project so you can see how the numbers flow through to the IRR.

comments powered by Disqus