Customer ROI, payback and TCO: building a business case a buyer trusts
ROI, payback, NPV, IRR and TCO for a customer business case, with a worked example that uses an adoption ramp, a 10% discount rate and five years.
A business case has to answer the questions the buyer will ask: how much the project returns, how quickly the cost comes back, whether it still creates value after the buyer's required return, and which option costs least to own. ROI, payback period, NPV and TCO each answer one of those questions. This article defines them, works one example from start to finish with an adoption ramp, a 10% discount rate and a five-year horizon, and sets out how to keep the buyer's inputs separate and conservative.
The numbers in the example are illustrative. Replace them with the buyer's own figures before you show the model to anyone.
What does each of ROI, payback, NPV and IRR tell you?
| Measure | Question it answers | How it is calculated |
|---|---|---|
| ROI | How much gain for each dollar spent? | (total benefit − total cost) / total cost |
| Payback period | How long until cumulative net cash turns positive? | Months until the cumulative line crosses zero |
| NPV | Is the project worth more than its cost at the required return? | Present value of all cash flows, year 0 included |
| IRR | What return do the cash flows earn? | The discount rate at which NPV equals zero |
| TCO | Which option costs least to own over the same period? | One-time and recurring costs over the period, with escalation |
ROI has no single definition. This article uses the one in the table, and any business case should state its formula. The discounting in NPV is the same logic as discounted cash flow. Use the same discount rate for NPV and for the present values in an options comparison, so the two parts of the case agree.
What cash flows does the customer ROI example use?
The project costs 60,000 at the start (year 0). It has a recurring cost of 12,000 a year in years 1 to 5. Once adoption is complete it saves 90,000 a year. Adoption ramps up: 40% of the full benefit in year 1, 75% in year 2 and 100% from year 3. Cash flows fall at the end of each year, and the discount rate is 10%.
| Year | Benefit | Ongoing cost | One-time cost | Net cash flow | Cumulative | Present value at 10% |
|---|---|---|---|---|---|---|
| 0 | 0 | 0 | 60,000 | -60,000 | -60,000 | -60,000.00 |
| 1 | 36,000 | 12,000 | 0 | 24,000 | -36,000 | 21,818.18 |
| 2 | 67,500 | 12,000 | 0 | 55,500 | 19,500 | 45,867.77 |
| 3 | 90,000 | 12,000 | 0 | 78,000 | 97,500 | 58,602.55 |
| 4 | 90,000 | 12,000 | 0 | 78,000 | 175,500 | 53,275.05 |
| 5 | 90,000 | 12,000 | 0 | 78,000 | 253,500 | 48,431.86 |
| Total | 373,500 | 60,000 | 60,000 | 253,500 | 167,995 |
The total present value is rounded to the dollar.
- ROI. Total benefit of 373,500 minus total cost of 120,000 leaves 253,500. Divided by 120,000, ROI is 211.25%.
- Simple payback. The cumulative line is negative at the end of year 1 (−36,000) and positive at the end of year 2 (19,500). Counting whole years, payback is 24 months.
- Interpolated payback. Year 2 nets 55,500, so the 36,000 still owed at the end of year 1 is recovered 36,000 / 55,500 = 0.65 of the way through year 2. Payback is 12 + 7.78 = 19.78 months, about 19.8 months.
- NPV. The present values sum to about 167,995. At a 10% required return, the project creates about that much value.
- IRR. The rate that sets NPV to zero is 74.4%. A high IRR on a short project is normal, so compare it with the buyer's required return rather than quoting it alone.
Which formulas build a customer ROI and payback model?
Put the discount rate in B1, with the year table starting in row 3: year 0 in A3 through year 5 in A8. Benefit is in column B, ongoing cost in C, one-time cost in D. Then:
- Net cash flow,
E3:=B3-C3-D3, filled down - Cumulative,
F3:=E3, then=F3+E4filled down - Present value,
G3:=E3/(1+$B$1)^A3, filled down - ROI:
=(SUM(B3:B8)-SUM(C3:C8)-SUM(D3:D8))/(SUM(C3:C8)+SUM(D3:D8)) - Whole-year payback in months:
=12*COUNTIF(F3:F8,"<0") - Interpolated payback in months:
=12*(COUNTIF(F3:F8,"<0")-1+(-INDEX(F3:F8,COUNTIF(F3:F8,"<0")))/INDEX(E3:E8,COUNTIF(F3:F8,"<0")+1)) - IRR:
=IRR(E3:E8)
The payback formulas assume the cumulative line crosses zero only once, which holds for this example. Check that assumption for any other project before using them.
How does Excel's NPV function treat year 0?
=NPV($B$1, E3:E8) treats the first value it receives as year 1. Year 0 is then discounted one period too many, and the result is 152,723, which is 9.1% below the correct figure. Keep year 0 outside the function:
=E3+NPV($B$1, E4:E8)
This returns 167,995, matching the table.
Why does the shortcut payback calculation mislead?
Dividing the cost by the full-year net benefit is a common shortcut: 60,000 / 78,000 years, or 9.2 months. It assumes the full benefit arrives from the first month. Here it understates payback by more than 10 months, and that gap is exactly what a buyer will test when they ask whether the ramp is credible.
How do weaker inputs change ROI, NPV, IRR and payback?
Show the buyer what happens when the inputs are weaker:
| Case | ROI | NPV at 10% | IRR | Payback, interpolated (months) |
|---|---|---|---|---|
| Base case | 211.25% | 167,995 | 74.4% | 19.8 |
| Benefits 20% lower | 149.00% | 113,298 | 56.4% | 24.2 |
| Benefits from day one (no ramp) | 275.00% | 235,681 | 127.9% | 9.2 |
With benefits 20% lower, whole-year payback moves from 24 months to 36 months. Counting full benefits from day one lifts ROI from 211.25% to 275.00% and adds 67,686 to the NPV, but only by assuming benefits that have not arrived yet.
Four habits keep the case conservative:
- Use the buyer's own numbers, and write the source next to each one, such as a time study for hours or payroll for rates.
- Ramp the benefit. Savings start when the buyer adopts the change, not when the contract is signed.
- Show at least one weaker case, and keep every input in one place so the buyer can change it.
- Use the buyer's required return as the discount rate if they have one. If they do not, state the rate you used.
The principle of keeping inputs apart from calculations is covered in spreadsheet model design. To build the model with the buyer's figures, start with the customer ROI calculator.
How do you compare options with a total cost of ownership model?
A TCO comparison answers a different question: which option costs least to own over the same period. Take three options with different price structures:
- A, hosted subscription: a 5,000 setup in year 0, then 30,000 in year 1, rising 5% a year.
- B, licensed on-site software: a 90,000 license in year 0, then 15,000 of support in year 1, rising 3% a year.
- C, self-hosted build: a 40,000 build in year 0, then 22,000 of operating cost in year 1, rising 4% a year.
With escalation, year n costs the base amount times (1 + rate) to the power n − 1. The year-by-year costs are:
| Option | Year 0 | Year 1 | Year 2 | Year 3 | Year 4 | Year 5 |
|---|---|---|---|---|---|---|
| A | 5,000.00 | 30,000.00 | 31,500.00 | 33,075.00 | 34,728.75 | 36,465.19 |
| B | 90,000.00 | 15,000.00 | 15,450.00 | 15,913.50 | 16,390.91 | 16,882.63 |
| C | 40,000.00 | 22,000.00 | 22,880.00 | 23,795.20 | 24,747.01 | 25,736.89 |
| Option | 3-year total | 5-year total | 5-year present value at 10% |
|---|---|---|---|
| A | 99,575.00 | 170,768.94 | 129,517.74 |
| B | 136,363.50 | 169,637.04 | 150,039.00 |
| C | 108,675.20 | 159,159.10 | 129,669.90 |
Over three years, A is cheapest. Over five years, C is cheapest in nominal terms. In present value, A is 152.16 lower than C, so the answer changes with both the horizon and the discounting. State both before recommending an option.
Escalation is an assumption, not a fact. Take each rate from the vendor's contract, from an index the buyer uses or from the buyer's budget guidance, and show the comparison with and without it. The buyer can then see how much the answer depends on it.
In a spreadsheet, enter the discount rate in B1, and for one option the setup cost in B2, the base cost in B3 and the escalation rate in B4. With the year in column A and the year cost in column C (rows 3 to 8 for years 0 to 5), the year cost is =IF(A3=0, $B$2, $B$3*(1+$B$4)^(A3-1)), filled down. The totals are =SUM(C3:C8) for nominal cost and =SUMPRODUCT(C3:C8/(1+$B$1)^A3:A8) for present value. The TCO comparison sheet is the place to build the year-by-year table for your own options.
What mistakes make a customer ROI business case misleading?
- Counting benefits from day one. As the sensitivity table shows, this lifts ROI by 63.75 points and hides how long the cash takes to arrive.
- Mixing nominal and discounted figures. The undiscounted net of 253,500 is not the NPV of about 167,995. Quoting one as the other overstates the value by about 85,505.
- Comparing options over different horizons. The three-year and five-year rankings above differ. Use the same period for every option. If one option lasts longer, model its replacement cost explicitly rather than stopping the comparison early.
For the definition of TCO on its own, see what is total cost of ownership (TCO).