How to calculate roas in Excel?

Why Most ROAS Spreadsheets Lie to You

Teams calculating ROAS in Excel often get misleading numbers because they match revenue to the month a sale closed instead of when the lead was generated. This timing mismatch distorts performance evaluation, as revenue attribution should align with lead generation periods to reflect true campaign effectiveness. Using gross revenue instead of net revenue and omitting hidden costs like platform fees, agency expenses, and call tracking further skews results, making campaigns appear more profitable than they actually are.

Proper ROAS calculation requires capturing the full cost of lead acquisition beyond just media spend. According to Wall Street Prep, ad spend components can include platform fees, salary costs (in-house or agency), vendor/partnership costs, affiliate commissions, and network transaction fees—all essential for accurate lead cost calculations in Excel. Roman Agency demonstrates this with a concrete example: $3,000 revenue divided by ($1,000 ad spend + $150 agency fee + $50 call tracking) yields a 2.5× ROAS, showing how incremental costs significantly impact the final metric.

GrowthPros clients purchasing leads as a product must account for both lead costs and associated delivery expenses to measure true profitability. Ignoring these elements leads to inflated ROAS figures that misguide budget decisions and strategy adjustments. Before applying any formula in Excel, ensuring inputs reflect accurate timing, net revenue, and comprehensive costs is critical—otherwise, even a perfectly structured spreadsheet will produce misleading outputs that undermine data-driven optimization.

• Match revenue to lead generation month, not sale closure month
• Include platform fees, agency costs, and call tracking in total spend
• Use net revenue after deductions, not gross revenue

The ROAS Formula and the Excel Setup That Gets It Right

Getting the ROAS calculation right in Excel starts with a simple formula but depends entirely on what you include in the numbers. The core equation—Revenue divided by Ad Spend—becomes meaningful only when you accurately attribute revenue to when leads were generated and capture every cost tied to acquiring those leads. This precision separates a useful metric from a misleading one, especially for businesses buying leads as a product where incremental fees can significantly alter the outcome.

For GrowthPros clients purchasing exclusive or capped-shared leads, tracking begins with structuring a spreadsheet that reflects the full lead lifecycle. Essential columns include lead generation date, conversion date, cost per lead (CPL), lead-to-sale conversion rate, and a comprehensive cost column that rolls up not just the base lead price but also agency fees, call tracking, or platform surcharges. Revenue must align with the month the lead was generated, not when the sale closes, to avoid distorting performance— a timing mismatch Roman Agency identifies as critical for accurate evaluation. Their research shows that attributing revenue to the correct lead generation window prevents misleading ROAS reports and ensures cash flow tracking remains separate from marketing performance analysis.

Implementing the formula in Excel follows the syntax recommended by Triple Whale: place total revenue from leads in column C and total advertising costs in column B, then use =(C2/B2)*100 to express ROAS as a percentage. This setup makes interpretation immediate—400% equals a 4:1 ratio, meaning $4 revenue per $1 spent. Consider a worked example: if $3,000 in revenue comes from leads generated in January, but the total cost includes $1,000 for the leads themselves plus a $150 agency fee and $50 for call tracking, the ROAS is $3,000 ÷ ($1,000 + $150 + $50) = 2.5x (or 250%), not the 3.0x you’d get by omitting those incremental expenses. Triple Whale emphasizes that forgetting such costs—software, design, or vendor fees—is a common pitfall that inflates ROAS and masks unprofitable campaigns.

Industry context helps frame what these numbers mean. While a 4:1 ROAS ($4 revenue per $1 spent) is frequently cited as a strong benchmark, acceptable minimums vary by business model and margin profile. Corporate Finance Institute notes that high-margin industries like SaaS may find 2:1 sufficient, whereas e-commerce often requires 3:1 or higher just to break even. For lead generation specifically, understanding whether you’re measuring initial sale revenue or lifetime value (LTV) changes the interpretation—Roman Agency’s plumbing example shows how a 1.2x short-term ROAS becomes 3.6x when LTV is included. Their worked examples across niches like garage doors (7.0x ROAS) and moving (5.0x ROAS) demonstrate how CPL, conversion rates, and revenue per sale interact in real scenarios.

To build a diagnostic spreadsheet, add columns that calculate CPL (total cost ÷ number of leads), lead-to-sale conversion rate (sales ÷ leads), and revenue per lead (revenue ÷ leads). These metrics, used alongside ROAS, reveal whether underperformance stems from expensive leads, poor conversion, or low revenue per sale—insights Roman Agency recommends for optimizing lead generation efforts. Wall Street Prep advises including all cost components—platform fees, agency costs, creative production—in the ad spend total to ensure accuracy, especially when scaling campaigns where costs and revenue may not increase proportionally. This granular approach turns ROAS from a headline number into a tool for refining lead acquisition strategy.

Reading Your Numbers: Benchmarks and the LTV Trap

So your Excel formula spat out a number. Now what? A 2.5 in cell D2 means nothing until you hold it against the right benchmark — and the right benchmark depends entirely on your business model.

According to Google's Economic Impact report, the average ROAS across industries hovers around 2:1, while a good target sits closer to 4:1 — meaning $4 in revenue for every $1 spent. But that single number hides a lot of variation, and applying the wrong yardstick to your spreadsheet can kill campaigns that are actually working.

The Corporate Finance Institute breaks the benchmarks down by business context rather than treating 4:1 as universal:

• E-commerce: 4:1 or higher is considered strong, with some analytics guidance suggesting 5:1 is the real bar for viability
• High-margin niches (SaaS, luxury): 2:1 can be sufficient
• Growth-stage startups: 1.5:1 may be acceptable while buying market share
• Anything below 1:1 — you're spending more than you're earning back, full stop

The LTV trap: when a "bad" ROAS is actually great

Here's where spreadsheet-only analysis misleads people. A worked plumbing example illustrates it perfectly: $2,000 in ad spend at a $120 cost per lead produces roughly 17 leads. At a 35% close rate, that's about $2,400 in first-ticket revenue — a 1.2x ROAS that looks like a failure on paper.

But plumbing customers come back. Factor in lifetime value and that same campaign generates $7,200 — a 3.6x ROAS. Same spend, same leads, same Excel file. The only thing that changed was the revenue column you chose to measure.

This is the core distinction between short-term and lifetime-value ROAS, and it matters most in lead-driven businesses like home services, where repeat business is the real profit engine. If your spreadsheet only tracks the first invoice, you'll systematically undervalue campaigns that acquire customers worth three times their first ticket.

The fix is structural: build two revenue columns in Excel — one for initial sale value, one for projected LTV — and calculate ROAS against each. A campaign that clears your benchmark on LTV-based ROAS deserves budget even if its first-ticket number looks thin.

For businesses buying leads as a product, like the clients GrowthPros serves, this dual view is essential: the cost side is your lead spend, and the revenue side should reflect what a customer is worth over the full relationship, not just the first job. Match revenue to the month the lead was generated, not when the sale closes, and your benchmarks will finally mean something.

From Spreadsheet to Scale: Turning ROAS Into Decisions

Turning your ROAS calculation into actionable decisions starts with understanding what’s driving the ratio—lead cost, conversion rate, or follow-up speed. When you buy leads as a product, your Excel sheet becomes a diagnostic tool: it tracks cost per lead by niche, revenue per closed lead, and how quickly you contact each prospect. This level of detail lets you isolate whether a low ROAS stems from paying too much per lead, failing to convert, or letting leads go cold before you reach them. Roman Agency’s research emphasizes that matching revenue to lead generation timing—not sale closure—is critical for accurate analysis, especially when measuring speed-to-lead impact.

For GrowthPros clients, the difference between a 2x and 4x ROAS often comes down to contacting leads within five minutes versus thirty. Data shows that responding within five minutes makes contact roughly 100x more likely than waiting half an hour, and 78% of buyers choose the vendor who replies first. If your Excel tracking reveals a high cost per lead but strong conversion when contacted quickly, the fix isn’t necessarily lowering your bid—it’s accelerating your follow-up. Conversely, if conversion remains low even with rapid response, you may need to refine lead targeting or offer messaging. Triple Whale notes that a 4:1 ROAS ($4 revenue per $1 spent) is a strong benchmark, but acceptable minimums vary—1.5:1 may suffice for growth-stage penetration, while high-margin niches might require 10:1 to be truly profitable.

Use your Excel sheet to test hypotheses: filter by niche, compare CPL against close rate, and overlay speed-to-lead metrics. If dormant leads in your CRM show 8–15% re-engagement via AI-powered reactivation, that’s a lever to pull when new-lead costs rise. Coupler.io warns that common pitfalls include ignoring hidden costs or misattributing revenue—so ensure your cost column includes every expense tied to lead acquisition and delivery, and align revenue to the month the lead was generated, not when the deal closed. Once your spreadsheet highlights the weak link—whether it’s CPL, conversion, or latency—you can act with precision.

Ready to turn insight into action? Book a 15-minute qualification call to get exclusive, followed-up-in-minutes leads—including reviving the dormant opted-in list you already paid for. This conversation is free, honest about fit, and commits you to nothing—just a clear path to higher ROAS through better lead execution.