Project ROI: A Template for Capital and Operating Projects

Project ROI is the return a specific project earns on the money and effort put into it, and a consistent worksheet makes projects across the company comparable. Without a standard layout, each project owner chooses their own time period, leaves out different costs and applies different discount rates, and the finance team spends the capital committee meeting reconciling assumptions instead of choosing projects. This guide describes a project ROI worksheet that works for both capital projects, such as equipment, and operating projects, such as a process redesign or a new system. We show the layout inline and do not offer a downloadable file here. It includes a worked example with checked arithmetic for ROI, payback, net present value and internal rate of return, and rules for review and approval. It is part of business analytics for finance and operations, follows the ROI of analytics investments and the ROI of a software purchase, and leads to ROI vs. payback period and incremental ROI. The results feed into the capital plan in budgeting and forecasting.

The worksheet layout

A one-page worksheet with five blocks is enough for most projects.

Add a short approvals section: sponsor, finance reviewer and date. A reviewer in finance should sign off on the assumptions, not just the arithmetic.

Standard assumptions to fix

Set these company-wide, so that projects are comparable:

• Evaluation horizon: for example five years for equipment and three for software.
• Discount rate: a stated rate based on cost of capital, plus a premium for higher-risk projects. In the example below we use 10 percent.
• Tax treatment: state whether cash flows are pre-tax or after-tax. Many small projects are evaluated pre-tax for simplicity, which is acceptable if used consistently.
• Terminal value: normally zero, or a stated salvage value.
• Inflation: whether cash flows are in nominal or real terms.
• Contingency: a percentage added to one-time cost estimates, scaled to the uncertainty.

A worked example: a new packaging line

A manufacturer considers a packaging line (hypothetical). Capital cost is $250,000 for the equipment plus $30,000 for installation and commissioning, a total of $280,000 at year 0. The line is expected to save $85,000 a year in net operating costs (labor, material waste and maintenance, after the added running cost of the new line) for five years, with no salvage value.

• Total inflows: 5 × $85,000 = $425,000
• Net gain: $425,000 - $280,000 = $145,000
• ROI: $145,000 / $280,000 = 51.8 percent over five years
• Payback: $280,000 / $85,000 = 3.29 years

At a 10 percent discount rate, the annuity factor for five years is 3.7908, so the present value of the savings is 85,000 × 3.7908 = $322,217, and the NPV is 322,217 - 280,000 = $42,217. The internal rate of return (IRR) is the rate at which the NPV is zero, where the annuity factor equals 280,000 / 85,000 = 3.2941. The factor is 3.3522 at 15 percent and 3.2743 at 16 percent, so the IRR is a little under 16 percent, about 15.7 percent.

The project clears its hurdle, but payback is long, so the savings estimate matters. A sensitivity test: if savings are 20 percent lower, at $68,000 a year, the PV is 68,000 × 3.7908 = $257,774, and the NPV is -$22,226, which is negative. The project breaks even at the 10 percent rate only if it saves at least $73,863 a year (280,000 / 3.7908). This number, the break-even savings, is a powerful figure for the review: is it plausible to deliver at least $73,863 a year, about 13 percent below the base case? The reviewers can then challenge the savings estimate directly.

An operating project example

Operating projects, such as redesigning a planning process, have little or no capital cost, and their costs are mostly people's time and consulting. A process project costs $40,000 in year 1, consisting of $25,000 of external support and $15,000 of internal time, with no recurring cost, and it delivers savings of $30,000 in year 1 and $45,000 a year in years 2 and 3. Total benefits are $30,000 + $90,000 = $120,000, and the net gain is $120,000 - $40,000 = $80,000, an ROI of 200 percent over three years. Payback occurs during year 2: after year 1 the cumulative position is -$10,000, and the $45,000 of year 2 recovers it in about 0.22 of that year, so payback is about 1.2 years. Small operating projects often show high ROI because the costs are low, but the benefits are the harder part to prove. Insist on a named owner for each benefit.

Reviewing the worksheet

A finance reviewer should check:

1. Completeness of costs, including internal time, training and maintenance.
2. Evidence for benefits: where does each number come from, and who owns it?
3. Consistency with company-wide assumptions.
4. No double counting with other projects.
5. Timing: are benefits phased realistically?
6. Sensitivity: what must be true for the project to break even?
7. Post-implementation measurement plan.

After approval

Track the project against the worksheet. Record actual costs and benefits each quarter, compare with plan and explain variances. Hold a post-implementation review, six to twelve months after completion, and record the lessons. Over time, the post-implementation data tells you how reliable each project owner's estimates are, and it lets you adjust for systematic optimism. See how to track ROI across departments.

Using the worksheet in a spreadsheet

Build it in Excel or Google Sheets with a small set of formulas: =NPV(rate, cash flows year 1 to N) + year 0 for NPV, =IRR(cash flows) for the internal rate of return, and a cumulative row for payback. Keep inputs (in blue cells) separate from calculations. Add a sensitivity table that varies savings and cost by plus and minus 20 percent. Protect formula cells and keep a version history. If you have a lot of projects, summarize them on a portfolio page ranking by NPV per dollar of capital, which is the NPV divided by the investment and helps allocate scarce capital. For the example, that is 42,217 / 280,000 = 0.15.

Want a working version?

If you would like a project ROI workbook built around your approval process, with portfolio roll-ups and automatic comparison against actual results, contact us. We build planning tools of this kind as part of our Analytics and Planning Automation work.

Choosing between mutually exclusive options

Sometimes you must choose one of several ways to solve the same problem. Suppose the manufacturer also has a semi-automatic option costing $150,000 that saves $50,000 a year for five years. At 10 percent, the present value of savings is 50,000 × 3.7908 = $189,540 and the NPV is $39,540. The NPV per dollar invested is 39,540 / 150,000 = 0.26, compared with 0.15 for the full line, and the IRR is about 20 percent (the annuity factor of 150,000 / 50,000 = 3.0 lies between 3.0576 at 19 percent and 2.9906 at 20 percent). The full line has the higher NPV ($42,217 against $39,540), but the semi-automatic option is a more efficient use of capital.

The sound way to decide is to look at the incremental investment. Choosing the full line over the semi-automatic one means spending an extra $130,000 for extra savings of $35,000 a year. The incremental NPV is $42,217 - $39,540 = $2,677, and the incremental IRR is about 10.8 percent (the factor 130,000 / 35,000 = 3.714 lies between 3.7908 at 10 percent and 3.6959 at 11 percent). The extra spend earns barely more than the 10 percent hurdle, so unless capital is plentiful and the extra capacity has strategic value, the semi-automatic line is the better choice. See incremental ROI.

What contingency does to the result

Estimates for capital projects are usually optimistic. Suppose a 15 percent contingency is added to the $280,000 cost: 280,000 × 1.15 = $322,000. The NPV at 10 percent becomes $322,217 - $322,000 = $217, which is effectively zero. A project that looked comfortably positive is now marginal. This is the purpose of contingency and sensitivity analysis: to show how close the decision is to the edge before the money is committed. If the project is still attractive with contingency and a modest shortfall in savings, it is robust. If not, the approvals process should ask for a smaller scope, a firmer quote or a pilot.

Projects that don't fit the format

Some projects are required for safety, regulatory or contractual reasons, and others create options that are hard to value. Use the same worksheet but change the question. For required projects, compare the cost of alternative ways of complying and choose the cheapest that meets the standard. For strategic projects, list the assumptions that would need to be true for the project to pay off, and decide whether they are plausible. In both cases, keep the worksheet so that the decision is documented and the reasoning can be reviewed later.

FAQs

What should a project ROI template include?

A summary, a baseline and scope, cash flows by year, ROI, payback, NPV and IRR with the discount rate stated, and a risk and sensitivity section.

What discount rate should I use?

Your cost of capital, plus a premium for riskier projects. Fix it company-wide so that projects are comparable.

How long should the horizon be?

Equipment typically uses its useful life, up to five or ten years. Software and process projects commonly use three years.

How do I compare projects of different sizes?

Use NPV per dollar invested, along with absolute NPV, to rank projects when capital is limited.

Should I track results after the project?

Yes. Compare actual costs and benefits with the worksheet at regular intervals and hold a post-implementation review.