Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to calculate the LTV-to-CAC ratio in Excel and Google Sheets
Topic:SaaS metricsExcel & Google Sheets
=(B2*(C2/100)/(D2/100))/E2Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Create columns: Avg Monthly Revenue per Customer (B), Gross Margin % (C), Monthly Churn Rate % (D), CAC (E), LTV:CAC (F).
- 2Enter representative values in row 2: B2=120 (ARPU), C2=75 (gross margin %), D2=2.5 (monthly churn %), E2=900 (CAC in dollars).
- 3The LTV formula is ARPU × Gross Margin % / Monthly Churn Rate. In F2 enter =(B2*(C2/100)/(D2/100))/E2.
- 4The result is the LTV-to-CAC ratio. A value of 3.0 means LTV is three times the acquisition cost.
- 5To also show raw LTV separately, add a column G with =B2*(C2/100)/(D2/100) so stakeholders can see both LTV and the ratio.
Tips
- Monthly churn must stay in the same unit as ARPU (monthly). If you track annual churn, divide by 12 before plugging it in.
- Gross margin should reflect only cost of goods sold (hosting, support, payment processing) — do not subtract sales and marketing here, since CAC already captures that.
- A ratio below 1 means you lose money on every customer; below 3 means payback is slow. Most investors expect at least 3:1 before recommending aggressive growth spend.
Need it for your exact data?
Describe your columns in plain English and get the precise formula for your sheet, with the right Excel or Sheets syntax.
Frequently asked
What's the simplest LTV formula?
LTV = ARPU × Gross Margin % / Monthly Churn Rate. This assumes constant revenue per customer and a constant churn rate, which is a reasonable approximation for most SaaS businesses.
Should I use monthly or annual churn in the formula?
Use the same time unit as ARPU. If ARPU is monthly, use monthly churn. If ARPU is annual, use annual churn. Mixing time units is the most common mistake.
How do I account for expansion revenue in LTV?
Replace ARPU with Net Revenue Retention-adjusted ARPU, or use a more advanced cohort-based LTV that tracks actual customer revenue over time rather than a steady-state formula.
More on SaaS metrics
See all →Reference
RANDARRAY
Spills a grid of random numbers with specified rows, columns, min, max, and integer/decimal options. Excel 365 and Google Sheets only.
How-toHow to calculate churn rate
Calculate monthly or annual customer churn rate as lost customers divided by starting customers, expressed as a percentage, in Excel and Google Sheets.
Error fixFix #####
Fix ##### in Excel — the column is too narrow to display the number or date. Widen the column or change the format to resolve it.
ReferenceARRAYTOTEXT
Converts an array or range into a single text string, with concise or strict formatting options.
How-toHow to calculate monthly recurring revenue (MRR)
Learn how to calculate MRR in Excel and Google Sheets using SUMIF to sum active subscription revenue by billing period, normalized to a monthly figure.
Error fixFix Array result
Array result was not expanded in Google Sheets means output cells are blocked. Clear adjacent cells or use a blank area.