The five-minute test that kills 40% of automation pitches
I built my first automation ROI calculator in Excel after wasting three weeks on a Slack-to-Airtable sync that saved 11 minutes a week.
The annual value? $143. The build cost? My time plus $19/month for Zapier.
That calculator now sits in row one of every scoping call. If a project doesn't clear a 3:1 return in year one, we don't build it. The spreadsheet has killed more ideas than it's greenlit—and that's exactly why it works.
You don't need a SaaS dashboard or a consultant's template. You need six columns, four formulas, and a willingness to admit when the math doesn't add up.
Here's how to build your own automation ROI calculator in Excel in the next 30 minutes.
What goes into an automation ROI model
Most ROI calculators on the internet are lead magnets in disguise. They ask for your email, spit out a fake percentage, and dump you into a nurture sequence.
A real calculator answers one question: Will this pay for itself in 12 months?
To answer that, you need three categories of input:
- Cost of the current manual process. How many hours per week does someone spend doing this task? What's their loaded hourly cost?
- Cost of the automation. Build time (your time or a contractor's), tool subscriptions, maintenance hours per month.
- Time savings. How many hours per week will the automation actually eliminate?
Everything else—error reduction, faster turnaround, employee morale—is real, but it's not math you can put in a cell. If the pure time-savings case doesn't work, the soft benefits won't save it.
If the time-savings math doesn't hit 3:1 in year one, the "soft benefits" won't save it.
Setting up your Excel sheet structure
Open a blank workbook. Label your columns in row 1:
- A1: Task / Process Name
- B1: Hours per Week (Manual)
- C1: Hourly Cost (Loaded)
- D1: Annual Manual Cost
- E1: Automation Build Cost
- F1: Annual Tool Cost
- G1: Hours per Week (Post-Automation)
- H1: Annual Automated Cost
- I1: Net Annual Savings
- J1: ROI Ratio
- K1: Payback Period (Months)
Freeze row 1 so it stays visible when you scroll. Select row 2, click View → Freeze Panes → Freeze Top Row.
Now you have a reusable template. Each row below will be one automation candidate.
The formulas that do the math
In row 2 (your first project), enter sample data in columns A, B, C, E, F, and G. For example:
- A2: Email triage and reply routing
- B2: 8 (hours per week someone currently spends)
- C2: 28 (loaded hourly cost—salary + benefits + overhead, divided by 2080 work hours/year)
- E2: 1200 (one-time build cost)
- F2: 240 (annual tool cost—e.g., $20/month subscription)
- G2: 1 (hours per week post-automation—rarely zero)
Now add these formulas:
D2: Annual Manual Cost
=B2*C2*52
This multiplies weekly hours by hourly cost by 52 weeks.
H2: Annual Automated Cost
=(G2*C2*52)+F2
Post-automation labor (hours × cost × 52) plus the annual tool cost. Note: this formula does not include the build cost—we handle that separately in payback.
I2: Net Annual Savings
=D2-H2
Manual cost minus automated cost.
J2: ROI Ratio
=I2/(E2+F2)
Annual savings divided by first-year total cost (build + first year of tools). If this number is above 3, you have a strong candidate. Below 1 means you lose money in year one.
K2: Payback Period (Months)
=IF(I2<=0,"Never",(E2/I2)*12)
If savings are zero or negative, return "Never." Otherwise, divide the build cost by annual savings, then multiply by 12 to get months.
Copy row 2 down to rows 3–10 so you can compare multiple projects side-by-side.
Adding decision logic with conditional formatting
Numbers in a grid don't jump out. You want green cells for winners, red cells for losers.
Select the range J2:J10 (your ROI Ratio column).
- Home tab → Conditional Formatting → Color Scales → More Rules.
- Set a 3-color scale:
- Minimum (0): Red
- Midpoint (2): Yellow
- Maximum (5): Green
Do the same for K2:K10 (Payback Period), but reverse the colors—short payback is good, so lower numbers should be green.
Now you can scan ten automation ideas in three seconds and spot the one that pays for itself in four months.
The two hidden costs everyone forgets
Most automation ROI models stop at "hours saved × hourly rate." That's where they break.
Hidden cost #1: Maintenance time.
Every automation needs babysitting. APIs change. Tools deprecate endpoints. Someone has to check logs, update credentials, and fix broken steps.
Budget 1–3 hours per month per automation. Add that to column G (post-automation hours) as a fractional weekly number. For example, 2 hours/month = 0.5 hours/week.
If you don't account for maintenance, your year-two ROI will look great on paper and terrible in practice.
Hidden cost #2: Opportunity cost of your time.
If you're the founder or the only technical person, the 20 hours you spend building a Zap is 20 hours you didn't spend selling, hiring, or shipping product.
Use your actual hourly value in column C—not your engineer's salary. If your time is worth $150/hour, a 15-hour build costs $2,250 in opportunity cost, even if you didn't write a check.
Our Repetitive Task Cost Calculator makes this visible in about 90 seconds. Punch in weekly hours and loaded cost, and it spits out annual waste. It's the same math as column D in your sheet, just faster.
When the ROI is good but you still shouldn't build it
A 5:1 ROI and a six-month payback looks great in Excel. But there are three deal-breakers that don't show up in a formula:
- The task changes every month. If the workflow isn't stable, you'll spend more time rewriting the automation than you save.
- The error cost is catastrophic. Automating invoice generation sounds great until the bot emails the wrong client the wrong number and you lose a contract.
- The person doing it manually is about to leave. If the role is getting eliminated or reassigned in three months anyway, the manual cost drops to zero. Your ROI just evaporated.
I keep a notes column (column L) in my sheet for these flags. If I write "unstable process" next to a 4:1 ROI, I know to wait three months and re-evaluate.
How to use this sheet in a scoping conversation
If you're hiring someone to build an automation—or pitching one to your boss—this sheet is your shared reality.
Walk through it together:
- "You said Sarah spends eight hours a week on this. Is that every week, or just month-end?"
- "What's your loaded cost for her time—salary plus benefits?"
- "If we automate this, she'll still spend an hour a week reviewing outputs. Does that sound right?"
Most scoping calls skip this step and jump straight to "Can you build it?" The answer is always yes. The better question is "Should we?"
If you want a second pair of eyes on the opportunity itself—what's worth automating in the first place—our Automation Opportunity Scanner runs a quick audit of your site and ranks the top three automation candidates by ROI. It's free, takes two minutes, and uses the same math you just built in Excel.
Comparing multiple projects: the priority matrix
Once you've scored five or six automation ideas, you need a tiebreaker.
Add a new sheet in your workbook. Label it "Priority Matrix."
In column A, list your project names (copy from column A of your main sheet).
In column B, paste the ROI Ratio (column J).
In column C, paste the Payback Period (column K).
Sort by column C (Payback Period), ascending. The project that pays for itself fastest wins—assuming the ROI ratio is above 3.
If two projects tie on payback, pick the one with the higher ROI ratio. If they tie on both, pick the one that eliminates the most manual hours (column B – column G on your main sheet).
This ranked list becomes your roadmap. Build the top one. Measure it. If it works, build number two.
The formula for ongoing maintenance cost
You added maintenance hours to column G, but let's make it explicit with a separate column so you can track it over time.
Insert a new column after G. Label it H1: Maintenance Hours/Week.
In H2, enter your estimate—usually 0.25 to 0.5 for simple automations, up to 2 for complex multi-tool workflows.
Now update your Annual Automated Cost formula in what is now column I:
=((G2+H2)*C2*52)+F2
This adds maintenance labor to the post-automation labor, multiplies by cost and weeks, then adds annual tool cost.
Copy the formula down. Your ROI ratio and payback will adjust automatically.
When to rebuild this sheet from scratch
Your first version will have placeholder numbers. That's fine. The goal is to get something working in 30 minutes, not to model every edge case.
Rebuild the sheet when:
- You finish your first automation and have real before/after data.
- Your team grows and loaded costs change.
- You switch tools (Zapier → Make → n8n) and subscription costs shift.
I rebuild mine every six months. It takes 15 minutes, and I usually find one assumption that's now wrong.
The calculator isn't a one-time artifact. It's a living document that gets better every time you use it.
What to do with the projects that don't clear the bar
If you score six ideas and only two hit a 3:1 ROI, you just saved yourself from four bad builds. That's a win.
Archive the losers in a separate tab labeled "Backlog." Add a column for "Re-eval Date"—three or six months out.
Situations change. Tool prices drop. Headcount grows. A process that wasn't worth automating in March might be a no-brainer in September.
I've gone back to the backlog twice in the past year and found a project that made sense the second time around. The ROI math didn't change—my business did.
From spreadsheet to build: what happens next
You've ranked your projects. The top one clears a 4:1 ROI and pays for itself in five months. Now what?
If the automation is simple—two tools, linear logic, no branching—build it yourself in Zapier or Make. Budget the hours you estimated in column E. If you hit hour 10 and you're still stuck, kill it and move to plan B.
If it's complex—multi-step, requires error handling, touches multiple data sources—hire someone who ships this stuff daily. Speed matters. A two-week build is worth more than a three-month DIY slog that never ships.
We build custom automations in n8n, scoped and fixed-price, shipped in 2–3 weeks. No retainer, no discovery bloat—just your ROI model, a build plan, and a done date.
The calculator proves it's worth doing. The builder makes sure it actually gets done.
Your next 30 minutes
You now have the structure, the formulas, the conditional formatting, and the decision framework.
Open Excel. Build the sheet. Plug in three real tasks from your business—candidate automations you've been thinking about but haven't committed to.
Run the math. One of them will surprise you. Either it's way better than you thought, or it's way worse.
That's the point. The calculator doesn't tell you what to build. It tells you what not to build—and that's worth more than the time it takes to set it up.