If you want to know how to calculate sales funnel performance without relying on black-box tools, the core formula is straightforward: for any stage, conversion rate = (count at stage N / count at stage N-1) × 100. But that is only the tip. In my decade building revenue models for SaaS and ecommerce founders, I have learned the real calculation ties stage conversion to customer acquisition cost (CAC), lifetime value (LTV), and ROI—usually inside a spreadsheet. Below I will show you exactly how to make a sales funnel in Excel using a real case, with every formula laid out so you can apply it today.
What the Sales Funnel Formula Actually Looks Like (And Why Most People Get It Wrong)
The direct answer to ‘what is the formula for sales funnel’ is a ratio between sequential stages. If 1,000 people visit your site and 200 sign up, signup conversion = 200/1,000 × 100 = 20%. The inverse, drop-off, is 80%. That answers ‘how to calculate funnel’ at the most basic level. But treating funnel math as isolated percentages is the mistake I see constantly.
When I first tried to calculate a sales funnel for a B2B SaaS client in 2016, I made the mistake of only measuring visit-to-signup conversion. We celebrated a 5% rate, but ignored that each signup cost $80 in paid ads—meaning we were losing money on every plan sold under $200 lifetime value. The lesson: a funnel calculation is incomplete until you attach economics.
The Basic Stage Conversion Equation
Write it as: Stage N Conversion % = (Count at Stage N ÷ Count at Stage N-1) × 100. In Excel you would place counts in column B and use =B3/B2 formatted as percentage. This is the skeleton.
Most people don’t realize that funnel drop-off percentages are not additive. A 50% drop from stage 1 to 2 and 50% from 2 to 3 yields 25% overall retention, not 100% loss. You multiply the survival rates, not sum the leaks.
Holistic Funnel Math: Linking Volume to Revenue
To move beyond toy metrics, you need a combined model: Revenue = Top-of-Funnel Volume × Stage1→2 Conv × … × StageFinal→Customer Conv × Average Sale Value. Then subtract CAC to get gross profit per customer, and compare to LTV for long-term ROI.
This is the formula for sales funnel that actually predicts business health. I recommend building it in Excel because you can flex inputs and see sensitivity. Our Sales Funnel Calculator mirrors these equations if you want a quick browser check after your sheet is built.
How to Make a Sales Funnel in Excel: Step-by-Step Spreadsheet Build
Answering ‘how to make a sales funnel in Excel’ requires more than a funnel chart. You need a calculation grid. I will walk through the exact workbook I use for early-stage clients, built in Excel 365 but compatible with Google Sheets.
Step 1: Define Stages and Pull Raw Counts
List stages in column A: Visitors, Signups, Trials, Paid Customers for SaaS; or Sessions, Add-to-Cart, Checkout, Purchase for ecommerce. In column B, paste absolute counts from your analytics for a fixed 30-day window. Never mix periods.
For a real SaaS case I handled: B2=10000, B3=500, B4=100, B5=30. Those are the raw inputs. If your CRM splits channels, pull each channel separately before blending.
Step 2: Layout Your Columns and Absolute References
Column C will hold conversion from prior stage. Column D will hold drop-off. Column E can hold cost per stage if you have ad spend or labor tied to acquisition. Use $ signs for any fixed assumption cells, like average sale price in a separate assumptions block.
I keep an assumptions table on a side tab: Avg Sale = $99, Gross Margin = 85%, Monthly Churn = 3%, Ad Spend = $20,000. Referencing those cells prevents hard-coded errors when you tweak later.
Step 3: Write the Conversion and Drop-Off Formulas
In C3 type =B3/B2 and drag down. Format as percentage. In D3 type =1-C3 to get drop-off share. This instantly shows where the leak is worst. In our SaaS row, C3 = 5%, D3 = 95%; C4 = 20%, D4 = 80%; C5 = 30%, D5 = 70%.
The thing nobody tells you about Excel funnel math: rounding hidden in percentage formatting can make your stage counts not reconcile. Always keep one helper column with raw decimals to 4 places.
Step 4: Add Economic Layers (CAC, LTV, ROI)
CAC = Total Stage Spend / Paid Customers. With $20k ad spend and 30 paid, CAC = $666.67. LTV = (Avg Sale × Gross Margin) / Churn = (99 × 0.85) / 0.03 = $2,805. ROI per customer = (LTV – CAC) / CAC = 320%. Build these as cells referencing your assumptions tab.
If you are doing this manually and wondering whether your time is worth it, our guide on how to calculate freelance hourly rate helps you assign a cost to the hour you spend building the model.
Step 5: Build a Funnel Chart for Visualization
Highlight A2:B5, insert a funnel chart (Excel 365: Insert > Chart > Funnel). It is cosmetic; the real work is the table. But a chart helps stakeholders see that 95% drop from visitor to signup is the prime fix area.
A Real Sales Funnel Example: SaaS and Ecommerce Side by Side
The PAA question ‘what is a sales funnel example’ is usually answered with a vague diagram. Here is a worked numeric example you can copy. I will show both a subscription and a transactional store so you see the math adapts.
SaaS Case: 10,000 Visitors to 30 Paid
Stage counts: Visitors 10,000; Signups 500; Trials 100; Paid 30. Conversion chain: 5%, 20%, 30%. Overall visitor-to-paid = 0.3%. Average sale $99/mo, margin 85%, churn 3%. LTV $2,805. CAC $666. Simple payback under 7 months.
| Stage | Count | Conv from Prior | Drop-off |
|---|---|---|---|
| Visitors | 10000 | – | – |
| Signups | 500 | 5% | 95% |
| Trials | 100 | 20% | 80% |
| Paid | 30 | 30% | 70% |
Ecommerce Case: 20,000 Sessions to 50 Orders
Stage counts: Sessions 20,000; Add-to-Cart 800; Checkout 200; Purchase 50. Conversions: 4%, 25%, 25%. Overall 0.25%. AOV $60. Repeat purchase rate 20% over 12 months implies simple LTV = $60 × 1.2 = $72. If ad spend $5,000, CAC = $100. That funnel loses money—a reality many store owners miss.
| Stage | Count | Conv from Prior | Drop-off |
|---|---|---|---|
| Sessions | 20000 | – | – |
| Add-to-Cart | 800 | 4% | 96% |
| Checkout | 200 | 25% | 75% |
| Purchase | 50 | 25% | 75% |
Calculating LTV and CAC in the Same Sheet
For ecommerce, LTV is trickier because of non-recurring base. I use LTV = AOV × (1 + Repeat Rate). For SaaS, LTV = ARPU × Margin / Churn. Put both as named cells. The comparison shows SaaS funnel despite lower volume is economically superior in this example.
One edge case: if your churn is seasonally high (holiday churn for subscription gift boxes), annualize carefully. I once modeled a client with 8% December churn but 1% other months; blind averaging inflated LTV by 40%.
Beyond Conversion Rate: Lead Velocity, Pipeline Math, and Combined ROI
Competitors stop at conversion. Practitioners need velocity. Lead Velocity Rate (LVR) = (Qualified Leads this month – Last month) / Last month × 100. If you had 100 trials last month and 130 this month, LVR = 30%. Growth compounds even if conversion holds.
Lead Velocity Rate (LVR)
LVR predicts revenue better than raw conversion because it captures top-of-funnel momentum. In Excel, = (B5_current – B5_prior)/B5_prior. Track it alongside stage conversion to avoid false panic when a later stage dips but inflow rises.
Pipeline Velocity Formula
For sales-led funnels, use Pipeline Velocity = (#Qualified Leads × Win Rate × Avg Deal Size) / Sales Cycle Days. Example: 100 leads × 30% win × $3k / 30 days = $3,000 daily pipeline generation. This connects funnel math to cash timing.
Funnel ROI: Blending CAC and LTV
ROI = (LTV – CAC) / CAC. But holistic ROI should also discount for payback period. A 300% LTV:CAC with 24-month payback is riskier than 150% with 3-month payback. I add a payback column = CAC / (Monthly Gross Profit per Customer). For SaaS above: $666 / ($84.15) = 7.9 months.
Trade-off: Excel gives you this control; a free calculator widget may hide the payback nuance. Use both—the manual sheet for planning, the Sales Funnel Calculator for fast what-if on calls.
The Funnel Economic Viability Matrix (Unique Framework)
I developed this matrix to decide where to invest. It scores each stage by conversion, cost to improve, and impact on final ROI. Copy this table into your Excel as a second tab.
| Stage | Current Conv | Cost to Lift 1pp | ROI Impact if Lifted | Priority |
|---|---|---|---|---|
| Visitor→Signup | 5% | $2k (ad creative) | High (top lever) | 1 |
| Signup→Trial | 20% | $500 (onboarding email) | Medium | 2 |
| Trial→Paid | 30% | $1.5k (demo calls) | Low (already best) | 3 |
The insight: fixing the worst absolute drop (95%) often beats optimizing the stage with best conversion. But only if cost to lift is reasonable. This matrix forces that comparison.
Common Mistakes When Calculating Your Sales Funnel
Even with the right formula, execution fails. Here are three errors I have made or audited in client sheets.
Mismatched Time Windows
If you pull visitors from January but paid customers from January-March (due to cycle), your conversion will look impossibly low. Align all stages to the same cohort entry date. I use a ‘cohort month’ column to tag each lead.
Stage Definition Drift
The thing nobody tells you about funnel math: your numbers are only comparable if definitions freeze. If you reclassify a ‘trial’ to include free users who never activated, historical 20% becomes 35% overnight—not real improvement. Document definitions in the sheet header.
Ignoring Multi-Touch Attribution
Excel rows assume linear path. But a customer may visit via organic, return via paid, then convert. If you count both visits as separate funnel entries, you double-count top. Use a unique user ID and count first touch for stage 1 only.
Reverse-Engineering a Revenue Goal From Funnel Math
Founders rarely ask ‘what is my conversion’; they ask ‘how many visitors do I need for $50k MRR?’ Start from goal: Required Customers = Goal MRR / Avg Sale = 50,000 / 99 = 505 customers. Then divide by final conversion (0.3%) = 168,333 visitors needed.
But if you improve trial→paid from 30% to 40%, required visitors drop to 126,250. That is the power of manual funnel calculation: you see which lever reduces ad spend. I run this as a data-table in Excel with conversion as variable.
Edge case: if your funnel has a hard capacity cap (sales reps can only demo 150 trials/month), no top-of-funnel volume saves you. Include a capacity row to avoid impossible plans.
Free Excel Template and Where to Go Next
I have packaged the exact workbook described—with the SaaS and ecommerce examples, LTV/CAC tabs, and the Viability Matrix—as a free download on our site. It is the same one I hand clients before we touch a paid tool.
After you build your manual model, validate with our Sales Funnel Calculator to catch formula typos. And if you decide to outsource the ongoing tracking, price your own time using the freelance hourly rate guide so you know the true cost of not automating.
Calculating a sales funnel is not just division; it is a financial model of your growth engine. Do it in Excel once, and every future decision gets cheaper.