How to Build a Customer Lifetime Value Calculator in Excel for SaaS Businesses
Step-by-step guide to creating a Customer Lifetime Value (CLV) calculator in Excel for SaaS businesses, including formulas, tables, and practical tips for Australian startups.
Introduction
Customer Lifetime Value (CLV) is a critical metric for SaaS businesses, helping to measure the total revenue a customer generates during their relationship with your company. It answers the fundamental question: how much should you spend to acquire a customer?
For Australian SaaS businesses, CLV is especially important given the market dynamics - smaller total addressable market than the US or Europe means customer acquisition costs are often higher, making retention and lifetime value the real growth levers.
By building a CLV calculator in Excel, you can make data-driven decisions about customer acquisition, retention, and marketing strategies. This guide walks through the process with Australian-specific adjustments.
Understanding Customer Lifetime Value (CLV)
CLV represents the total revenue a business can expect from a single customer over the duration of their relationship. For SaaS businesses, this metric is particularly important because it helps determine the long-term value of recurring revenue streams.
Key Components of CLV
- Average Revenue Per User (ARPU): The average revenue generated per customer per month (ex GST)
- Customer Churn Rate: The percentage of customers who stop using your service over a given period
- Gross Margin: The percentage of revenue remaining after deducting COGS (hosting, support, payment processing)
- Customer Lifespan: The average duration a customer stays with your business (
1 / Churn Rate)
The Simple CLV Formula
CLV = (ARPU × Gross Margin) × (1 / Churn Rate)
For Australian SaaS, adjust ARPU to exclude GST and adjust gross margin to include Australian-specific costs: payment gateway fees (typically higher in AU at 1.5-2.5% vs US 2.9%), hosting costs (AWS Sydney region premium), and any AU-specific compliance overhead.
Steps to Build a CLV Calculator in Excel
Step 1: Gather Data
Collect the following data points:
- Monthly recurring revenue (MRR) - ex GST
- Number of customers
- Churn rate (monthly)
- Gross margin percentage
Step 2: Calculate ARPU
Formula: ARPU = MRR / Number of Customers
| MRR (ex GST) | Number of Customers | ARPU |
|---|---|---|
| $50,000 | 500 | $100 |
Step 3: Calculate Customer Lifespan
Formula: Customer Lifespan = 1 / Churn Rate
| Churn Rate | Customer Lifespan |
|---|---|
| 5% | 20 months |
Step 4: Calculate CLV
Formula: CLV = (ARPU * Gross Margin) * Customer Lifespan
| ARPU | Gross Margin | Customer Lifespan | CLV |
|---|---|---|---|
| $100 | 70% | 20 | $1,400 |
Step 5: Create a Dynamic Excel Calculator
Build a clean input/output model:
| Metric | Input/Formula | Value |
|---|---|---|
| MRR (ex GST) | Input | $50,000 |
| Number of Customers | Input | 500 |
| ARPU | =MRR/Customers | $100 |
| Churn Rate (monthly) | Input | 5% |
| Customer Lifespan | =1/Churn Rate | 20 months |
| Gross Margin | Input | 70% |
| CLV | =(ARPU*Gross Margin)*Lifespan | $1,400 |
| CAC | Input | $500 |
| LTV:CAC Ratio | =CLV/CAC | 2.8 |
Add a cohort-based CLV table to see how CLV changes by customer segment:
| Segment | ARPU | Mo. Churn | Lifespan | Gross Margin | CLV |
|---|---|---|---|---|---|
| Freemium | $0 | 15% | 6.7 mo | 0% | $0 |
| Starter | $49 | 8% | 12.5 mo | 70% | $429 |
| Professional | $99 | 5% | 20 mo | 80% | $1,584 |
| Enterprise | $299 | 2% | 50 mo | 85% | $12,708 |
This segmentation reveals the real insight: enterprise customers are worth 30x more than starter customers, not 6x (the simple ARPU ratio). The combined effect of higher ARPU, lower churn, and higher margin compounds dramatically.
Worked Example: Australian SaaS Startup
Consider an Australian SaaS startup based in Melbourne with the following metrics:
- MRR: $30,000 (ex GST)
- Customers: 300
- Monthly churn: 8%
- Gross margin: 75%
The Calculation:
- ARPU: $30,000 / 300 = $100
- Customer lifespan: 1 / 0.08 = 12.5 months
- CLV: ($100 × 0.75) × 12.5 = $937.50
What This Means:
The startup can spend up to $937 to acquire a customer and break even over their lifetime. If their customer acquisition cost (CAC) is $500, the LTV:CAC ratio is 1.9x - which is below the 3:1 healthy benchmark but not yet critical. They need to either reduce churn or increase pricing.
The Fix:
The startup runs a cohort analysis and discovers that customers who complete onboarding within the first 7 days have 4% monthly churn (vs 8% average). They implement a structured onboarding program:
- Week 1: 3 automated onboarding emails + a 15-minute setup call
- Week 2: Usage milestone tracking with in-app prompts
- Week 4: Business review call to identify value realisation
Result: Average monthly churn drops from 8% to 5.5% within three months. New CLV:
- Customer lifespan: 1 / 0.055 = 18.2 months
- CLV: ($100 × 0.75) × 18.2 = $1,365
- LTV:CAC ratio: $1,365 / $500 = 2.7x
A 2.5% reduction in monthly churn increased CLV by 45%. This is the compound effect of retention improvements.
Note: The above figures are illustrative. Actual CLV depends on specific revenue, churn, and margin characteristics.
Practical Applications of CLV
CLV Benchmarking for Australian SaaS
| Metric | Good | Great | Excellent |
|---|---|---|---|
| Monthly Churn | < 5% | < 3% | < 2% |
| LTV:CAC Ratio | > 3:1 | > 5:1 | > 10:1 |
| CLV (B2B SaaS, mid-market) | $1,000+ | $3,000+ | $10,000+ |
| Payback Period | < 12 mo | < 6 mo | < 3 mo |
Australian SaaS companies typically face 10-20% higher CAC than US equivalents due to smaller market and higher sales costs, making CLV optimisation even more critical.
Frequently asked questions
Why is CLV important for SaaS businesses?
CLV helps SaaS businesses understand the long-term value of customers, guiding decisions on acquisition spend, retention investment, and pricing strategy. It's the north star metric for subscription businesses.
How often should I update my CLV calculations?
Update CLV calculations monthly or quarterly to reflect changes in revenue, churn rate, and customer behaviour. Monthly updates are preferred for fast-growing SaaS businesses with churn above 5%.
Can I use CLV for non-recurring revenue models?
CLV is most effective for recurring revenue models like SaaS, but it can be adapted for other business models with adjustments - typically by modelling average transaction value × purchase frequency × average customer lifespan.
What if my churn rate fluctuates significantly?
Use a rolling 3-month or 6-month average churn rate to smooth out fluctuations. For early-stage startups (under 12 months of data), benchmark against industry averages and update as your own data matures.
How can I improve my CLV?
Focus on reducing churn (the biggest lever), increasing ARPU through upselling or cross-selling, and improving customer satisfaction to extend customer lifespan. A 1% reduction in churn can improve CLV by 10-15%.
What is a healthy LTV:CAC ratio for Australian SaaS?
3:1 or higher is considered healthy. Below 1:1 means you're losing money on each customer. Between 1:1 and 3:1, focus on reducing CAC or increasing pricing. Australian SaaS companies typically operate at 2.5:1 to 4:1.
How does GST affect CLV calculations?
Always use revenue ex-GST in CLV calculations. Including GST inflates ARPU by 10% and gives a misleading picture of customer value. This is a common mistake in Australian SaaS metrics.
Conclusion
Building a CLV calculator in Excel is a straightforward yet powerful way to analyse customer value for SaaS businesses. By understanding and applying this metric - with Australian-specific adjustments for GST, higher CAC, and gross margin components - you can make informed decisions that drive growth and profitability.
The most important insight: improving retention is almost always a better investment than increasing acquisition. A small reduction in churn compounds into dramatically higher CLV.