How to Calculate Customer Acquisition Cost (CAC) and LTV in Excel

In unit economics and venture finance, two metrics govern whether a subscription or e-commerce business can scale profitably:

1. Customer Acquisition Cost (CAC): The fully-loaded cost to acquire a single paying customer.
2. Customer Lifetime Value (LTV): The gross profit a customer generates throughout their entire relationship with your business.

This guide provides formulas to build a SaaS and growth analytics dashboard.

---

1. The Core Unit Economics Formulas

Customer Acquisition Cost (CAC):

$$\text{CAC} = \frac{\text{Total Sales & Marketing Expenses (Ad spend + Salaries + Tools)}}{\text{Total New Paying Customers Acquired}}$$

Customer Lifetime Value (LTV):

$$\text{LTV} = \frac{\text{ARPU} \times \text{Gross Margin %}}{\text{Customer Churn Rate}}$$

$$\text{Average Customer Lifespan (Months)} = \frac{1}{\text{Monthly Churn Rate}}$$

CAC Payback Period (Months):

$$\text{Payback Period} = \frac{\text{CAC}}{\text{ARPU} \times \text{Gross Margin %}}$$

---

2. Monthly SaaS Growth Model Setup

Let’s model a B2B SaaS startup with $120/month subscription pricing:

Row	Metric (A)	Formula (B)	Value (C)	Unit / Explanation
2	Paid Ad Spend	Input	$45,000.00	Google Ads + Meta Ads
3	Sales & Marketing Salaries	Input	$35,000.00	Reps, content team, SDRs
4	Marketing Software & Tools	Input	$5,000.00	HubSpot, analytics, CRM
5	Total Acquisition Spend	=SUM(C2:C4)	$85,000.00	Fully loaded cost
6	New Customers Acquired	Input	170	Paid accounts onboarded
7	Blended CAC	=C5 / C6	$500.00	Cost per acquired customer
8	Average Monthly ARPU	Input	$120.00	Revenue per account/month
9	Gross Profit Margin %	Input	80.0%	Software gross margin
10	Monthly Customer Churn Rate	Input	3.5%	Cancellations / Start active
11	Customer Lifespan (Months)	=1 / C10	28.57 mos	Average retention
12	Customer Lifetime Value (LTV)	=(C8 * C9) / C10	$2,742.86	Gross profit per lifetime
13	LTV : CAC Ratio	=C12 / C7	5.49x	High unit profitability
14	CAC Payback Period	=C7 / (C8 * C9)	5.21 mos	Capital recovered in 5.2 months

---

3. Cohort Retention Matrix (Calculating Churn)

To calculate exact month-over-month retention from customer cohort data:

Cohort Month	Starting Accounts	Month 1	Month 2	Month 3	Month 6	Month 12
Jan 2026	200	100.0%	94.0%	90.5%	84.0%	78.0%
Feb 2026	240	100.0%	95.0%	91.0%	85.5%	—
Mar 2026	290	100.0%	93.5%	89.0%	—	—

The formula for active percentage in cell D2:

= Active_Accounts_In_Month / $B2

---

4. Benchmark Health Check Dashboard

Use conditional formatting to monitor company unit economics:

=IFS(
  LTV_CAC_Ratio >= 3.0, "🟢 Strong Unit Economics",
  LTV_CAC_Ratio >= 2.0, "🟡 Acceptable / Optimize CAC",
  TRUE, "🔴 Warning: Unprofitable Acquisition"
)