ROI Calculator Template (Excel and Google Sheets)

An ROI template gives you one place to enter a project's costs and benefits and see ROI, payback and net present value calculated the same way every time. This page describes a simple ROI calculator you can build yourself in Excel or Google Sheets in under an hour, with the layout, the formulas and a worked example with checked arithmetic. We show the structure inline and do not offer a downloadable file here. It supports the practice in business analytics for finance and operations, builds on the ROI formula and project ROI templates, and works with how to write a business case and cost-benefit analysis. The outputs feed the capital plan in budgeting and forecasting.

Layout

Use a single sheet with four blocks, from top to bottom.

Mark input cells with a colored fill, such as light yellow, and leave formula cells uncolored. Add a note under the title stating which cells to change.

The formulas

Assume the cash flow table has years in columns C to H (year 0 to year 5), one-time costs in row 11, recurring costs in row 12, benefits in row 13, net cash flow in row 14 and cumulative net cash flow in row 15.

The NPV function in spreadsheets discounts the first value by one period, so year 0 is added outside the function, as in the formula above. For payback, a helper row that shows the fraction of the year when cumulative first becomes positive keeps the formula readable. A contingency input can multiply one-time costs: one-time cost = base estimate × (1 + contingency).

A worked example

Suppose the inputs are a discount rate of 10 percent, a horizon of five years and the following cash flows for a reporting automation project (hypothetical).

Check the totals: total cost = 40,000 + 5 × 8,000 = $80,000; total benefit = 30,000 + 4 × 34,000 = $166,000; the net gain is $166,000 - $80,000 = $86,000, which matches the final cumulative figure.

• ROI: 86,000 / 80,000 = 107.5 percent over five years.
• Annualized ROI: (1 + 1.075)^(1/5) - 1 = 2.075^0.2 - 1 = 1.1572 - 1 = about 15.7 percent a year.
• Benefit-cost ratio: 166,000 / 80,000 = 2.08.
• Payback: cumulative turns positive during year 2: 1 + 18,000 / 26,000 = 1 + 0.69 = 1.69 years.
• NPV at 10 percent: -40,000 + 22,000 / 1.10 + 26,000 × (1/1.21 + 1/1.331 + 1/1.4641 + 1/1.6105). The discounted terms are 20,000 for year 1 and for years 2 to 5: 21,488 + 19,535 + 17,759 + 16,144 = 74,926. The total is -40,000 + 20,000 + 74,926 = $54,926.

If your spreadsheet gives a different NPV, check that year 0 is outside the NPV function and that the discount rate cell is formatted as a percentage.

Sensitivity block

Add two input cells, "benefit adjustment" and "cost adjustment" (for example -20 percent and +15 percent), and recompute the results with benefits multiplied by (1 + benefit adjustment) and costs by (1 + cost adjustment). For the example, benefits 20 percent lower are 0.80 × 166,000 = $132,800, and costs 15 percent higher are 1.15 × 80,000 = $92,000. The ROI becomes (132,800 - 92,000) / 92,000 = 44.3 percent, still positive. A second column can show a combined pessimistic case, and a third can show a break-even benefit level. Seeing how far the benefits can fall before the project loses money is often more useful than any single result.

Using the template well

1. Fill in all costs first, including internal time and training.
2. Enter benefits with a note on the source of each.
3. Use the same horizon and discount rate for all projects you want to compare.
4. Save a copy for each project, and a summary sheet that lists the results side by side.
5. After the project, add an "actual" row and compare.

Want a working version?

If you would like an ROI workbook built around your approval process, with a project register, portfolio summaries and links to your finance system, contact us. This is part of our Analytics and Planning Automation work.