Customer ROI and payback calculator for a technical sale

Three-year ROI, NPV, IRR and payback from one-time and recurring costs, benefits and an adoption ramp.

Downloads are included in the $19.85 yearly membership. Sign in

XLSXCSVODSXMLNumbers

	A	B	C	D
1	Customer ROI and payback
2	Example data — replace the blue input cells with your own.
3
4	3-YEAR ROI	NET PRESENT VALUE	IRR	PAYBACK (MONTHS)
5	97%	$963,038	75.9%	17.5
6	Customer: Example Foods Inc., solution: Plant monitoring system
7
8	Headline figures (USD)
9	Benefits over the horizon	$2,247,300	Undiscounted, after the adoption ramp
10	Costs over the horizon	$811,731	One-time and recurring costs, escalated
11	Net cash flow over the horizon	$1,435,569	Benefits minus costs, undiscounted
12	Costs in years 0 to 3	$634,272	Denominator of the 3-year ROI
13	Benefits in years 1 to 3	$1,248,500	Numerator of the 3-year ROI, before costs
14
15	Totals by benefit source (over the horizon)
16	Benefit source	Total	Share of benefits

Showing the first 16 of 32 rows and 6 of 6 columns. Cells with formulas show the formula on hover.

	A	B	C
1	Customer case inputs
2	Blue cells are inputs. Replace the example figures with the customer's own numbers; black cells are formulas.
3
4	Customer and solution
5	Customer	Example Foods Inc.	Fictional customer name, shown on the Summary tab
6	Solution	Plant monitoring system	Fictional product used in this example
7
8	One-time costs (year 0)
9	Hardware	$180,000	Sensors, gateways and controllers, installed
10	Software licenses	$60,000	Perpetual licenses for the monitoring platform
11	Implementation services	$95,000	Vendor or partner deployment and integration
12	Training	$12,000	Operator and maintenance training across sites
13	Internal staff time	$40,000	Project team time at loaded cost, paid once
14	Total one-time costs	$387,000
15
16	Recurring annual costs (year 1, escalated from year 2)

Showing the first 16 of 47 rows and 3 of 3 columns. Cells with formulas show the formula on hover.

	A	B	C	D	E	F	G	H
1	Cash flow by year
2	Years 0 to 5 across the columns. Years after the analysis horizon are set to zero. Every cell is a formula.
3
4	Line	Year 0	Year 1	Year 2	Year 3	Year 4	Year 5	Total
5	Year number	0	1	2	3	4	5
6	In analysis horizon (1 = yes)	1	1	1	1	1	1
7	Adoption ramp (share of full benefit)	0%	60%	90%	100%	100%	100%
8	Costs
9	One-time costs	$387,000	$0	$0	$0	$0	$0	$387,000
10	Recurring costs, escalated from year 2	$0	$80,000	$82,400	$84,872	$87,418	$90,041	$424,731
11	Total costs	$387,000	$80,000	$82,400	$84,872	$87,418	$90,041	$811,731
12	Benefits (full-adoption value times ramp times horizon flag)
13	Labor savings	$0	$131,040	$196,560	$218,400	$218,400	$218,400	$982,800
14	Unplanned downtime avoided	$0	$133,200	$199,800	$222,000	$222,000	$222,000	$999,000
15	Scrap reduction	$0	$20,400	$30,600	$34,000	$34,000	$34,000	$153,000
16	Other avoided costs	$0	$15,000	$22,500	$25,000	$25,000	$25,000	$112,500

Showing the first 16 of 28 rows and 8 of 8 columns. Cells with formulas show the formula on hover.

	A
1	Customer ROI and payback calculator
2	Notes: what this workbook does, how to use it and the method behind it.
3
4	What it does
5	Models the return on a technical purchase for one customer. It takes one-time costs, recurring costs, benefit drivers and an adoption ramp, then reports the 3-year ROI, net present value, internal rate of return and payback in months.
6
7	How to use it
8	1. On the Inputs tab, enter the customer and solution names, then the one-time costs for hardware, software licenses, implementation services, training and internal staff time.
9	2. Enter the recurring annual costs for the subscription fee, support and maintenance, and internal administration. Year 1 uses these amounts as entered.
10	3. Enter the benefit drivers at full adoption: labor hours saved, loaded hourly rate, unplanned downtime hours avoided, cost per downtime hour, scrap reduction, annual scrap cost and other avoided costs.
11	4. Set the adoption ramp for years 1 and 2 and for year 3 onward, the analysis horizon (3 to 5 years), the discount rate and the annual cost escalation.
12	5. Read the headline results on the Summary tab, then go through the Cash flow tab one line at a time with the customer.
13
14	Formulas and method
15	Benefit value per year at full adoption: labor hours times loaded hourly rate; downtime hours avoided times cost per downtime hour; scrap reduction percentage times annual scrap cost; other avoided costs as entered.
16	Each year's benefit equals the full-adoption value times the adoption ramp for that year. Years after the horizon are set to zero.

Showing the first 16 of 36 rows and 1 of 1 columns. Cells with formulas show the formula on hover.

What does this template do?

This workbook puts a customer's own numbers into a return-on-investment model for a technical purchase. Enter one-time costs for hardware, software licenses, implementation services, training and internal staff time. Add recurring costs for the subscription, support and internal administration, then enter the benefit drivers: labor hours saved, unplanned downtime avoided, scrap reduction and other avoided costs.

The Cash flow tab runs years 0 to 5. Benefits follow an adoption ramp of 60 percent in year 1, 90 percent in year 2 and full value from year 3. Recurring costs escalate each year, and every year is discounted at the chosen rate. The Summary tab reports the 3-year ROI, net present value, internal rate of return and payback in months, with totals by benefit source and cost type. Payback is interpolated within the year the cumulative cash flow turns positive.

The example is fictional: a plant monitoring system sold to Example Foods Inc. Replace the blue input cells with the customer's figures before you present the result.

What’s inside

One-time costs in year 0 and recurring costs that escalate from year 2
Benefits ramp up by year (60%, 90%, then 100%) and stop at the analysis horizon
NPV equals year 0 net cash flow plus the NPV of years 1 to 5 at the chosen rate
Payback in months, interpolated within the year the cumulative cash flow turns positive
Totals by benefit source and by cost type, for a business case

Which tabs does the workbook have?

Tab	What it holds
Summary	Headline ROI, NPV, IRR and payback, with totals by benefit source and by cost type.
Inputs	Customer, one-time and recurring costs, benefit drivers, adoption ramp and analysis settings.
Cash flow	Years 0 to 5: costs, benefits, net and cumulative cash flow, discount factors and discounted flows.
Notes	Purpose, steps, formulas used, assumptions and limits.

What formulas does this template use?

This template holds 142 formulas in 339 cells across 4 tabs, so 42% of its cells calculate. They use 10 distinct functions; the longest formula is 183 characters and 75 of them read from another tab.

Function	Uses	What it does
`IF`	37	one result when a test is true, another when false
`SUM`	26	adds numbers
`AND`	5	true when every test is true
`OR`	2	true when any test is true
`ABS`	1	absolute value
`COUNT`	1	counts numeric cells
`IFERROR`	1	swaps an error for a fallback value
`IRR`	1	internal rate of return
`MIN`	1	smallest value
`NPV`	1	net present value of a cash flow series

Counted from the workbook itself. Only functions that Excel, LibreOffice Calc, Google Sheets and Apple Numbers evaluate the same way are used, so the formulas survive every download format.

How do you use it?

On the Inputs tab, enter the customer name, the solution name and the one-time costs.
Enter the recurring annual costs and the benefit drivers at full adoption.
Set the adoption ramp, the analysis horizon (3 to 5 years), the discount rate and the cost escalation.
Read the 3-year ROI, NPV, IRR and payback on the Summary tab.
Go through the Cash flow tab line by line with the customer before you present the result.

What is it good for?

Business case for a plant monitoring or automation purchase
Comparing payback across two configurations of one solution
Testing how sensitive the return is to the speed of adoption
Pre-sales ROI discussions with a customer's finance team

Questions about this sheet

How is the 3-year ROI calculated?

ROI is the benefits in years 1 to 3 minus the costs in years 0 to 3, divided by the costs in years 0 to 3. The costs include the year 0 one-time costs.

How is payback in months found?

The sheet finds the first year in which the cumulative net cash flow turns positive and interpolates within that year. It shows Not within horizon when that does not happen by the end of the analysis period.

Do benefits escalate each year?

No. Only recurring costs escalate, from year 2 onward. Benefits follow the adoption ramp and stay at full value from year 3.

Does the sheet include taxes or depreciation?

No. The model uses pre-tax cash flows with no depreciation, financing or residual value. Add those in the customer's own model if the buyer needs them.