Step-by-step cohort LTV analysis in Excel for e-commerce and SaaS founders

Why Cohort Analysis Is Non-Negotiable for Business Health

A simple, or blended, lifetime value (LTV) calculation takes all your customer revenue and divides it by your total customer count. The problem with this approach is that a single average can hide dangerous trends that threaten your business. For founders without a dedicated finance team, relying on this blended figure can lead to misguided marketing spend and a false sense of security.

A cohort, by contrast, is a group of customers who started using your product or made their first purchase in the same period, typically a specific month. Cohort analysis tracks the behavior of each of these groups independently over time, providing a much clearer picture of business performance and customer retention.

Why does this matter? Imagine your January cohort is retaining well, but your May cohort is churning twice as fast. A blended LTV would average the two, masking the fact that a recent product change, pricing update, or marketing campaign is attracting less valuable customers. Cohort LTV analysis exposes these critical differences. It allows you to see if your customer retention is improving, staying flat, or getting worse. This insight is non-negotiable for diagnosing the health of your SaaS or e-commerce business and understanding the real return on your customer acquisition efforts. Averages hide the truth; cohorts reveal it.

Part 1: How to Calculate Cohort Lifetime Value in Excel from Raw Data

This first part is often the most confusing for founders, but it is a straightforward data-cleaning exercise. Your goal is to convert a long list of individual transactions into a summary that shows when each customer first appeared and how much they spent each month thereafter. This process forms the bedrock of your entire LTV model spreadsheet.

You only need three columns from your raw data export, which you can get from payment processors like Stripe, subscription platforms like Chargebee, or your internal database. The required fields are:

• A unique Customer ID
• The Transaction Date
• The Transaction Amount

Follow these steps to structure your data correctly:

1. Assign Each Customer to a Cohort: A customer's cohort is defined by the month of their very first transaction. To find this in Excel, first create a separate summary table of all unique Customer IDs. Next to each ID, use the MINIFS function to search your raw transaction log and find the earliest transaction date associated with that specific ID. This date is their acquisition date. Format this column to show only the month and year (e.g., ‘Jan-2023’). Now every customer belongs to a specific cohort.
2. Organize Revenue by Month with a PivotTable: With each customer assigned to a cohort, use a PivotTable to organize the transaction data. This powerful Excel feature will do the heavy lifting for you. Set your Customer IDs as the rows, the individual transaction months as the columns, and the sum of transaction amounts as the values.

The resulting table now cleanly shows the total monthly revenue from every single customer, perfectly structured for the next stage of your analysis. It is generally recommended that you perform a cohort analysis once you have at least 6 to 12 months of transaction data for meaningful results. For more advanced data shaping, you can also use tools like Power Query to unpivot columns if your data is structured differently.

Part 2: Building Your Historical Cohort Retention Table

With your data cleanly organized, you can now build the central component of customer retention analysis: the cohort retention grid. The goal of this table is to show you, for each cohort, what percentage of its original customers were still active and generating revenue in subsequent months. A well-designed cohort retention grid provides an immediate visual diagnosis of customer churn.

Here’s how to build it:

1. Create the Grid Structure: Start a new table in your LTV model spreadsheet. List your cohort months (Jan-2023, Feb-2023, etc.) down the first column. Across the top row, list the customer lifetime in months: Month 0, Month 1, Month 2, and so on. Month 0 represents the month the cohort was acquired.
2. Count Active Customers Per Month: Using your PivotTable from Part 1, you will now populate this grid with raw customer counts. For each cohort (each row), start by counting the initial number of customers in Month 0. Then, for Month 1, count how many of those same customers from that specific cohort had a transaction. Continue this for each subsequent month. You can do this efficiently using the COUNT or COUNTA functions on your pivoted data.
3. Convert Counts to Retention Percentages: Finally, create a parallel table that converts these absolute numbers into percentages. For each row (each cohort), divide the number of active customers in each month by the initial number of customers in Month 0 for that same cohort. The result is a powerful visualization of your retention curves, showing exactly how each group of customers churns over time.

Part 3: Projecting Future Retention with Defensible Assumptions

Your historical data will eventually run out, yet a credible LTV calculation requires a long-term view of customer value. This is where you must shift from historical analysis to forecasting. The goal is to make a defensible assumption to project future retention rates. A common mistake is to project a simple straight-line decline, which is unrealistic and will lead to an inaccurate LTV.

In practice, customer retention curves tend to flatten over time. Customers who are prone to churn typically do so early in their lifecycle, leaving behind a more loyal, stable base of users. Your projection must reflect this reality.

1. Calculate an Average Historical Retention Curve: First, create a baseline from your existing data. Look at the percentage-based retention table you built in Part 2. For each column (Month 1, Month 2, etc.), calculate the average retention percentage across all the cohorts that have reached that age. This gives you a single, blended historical retention curve that smooths out any single cohort's anomalies.
2. Forecast a Flattening Curve: Now, you can project this average curve forward. For early-stage SaaS, modeling a logarithmic curve that flattens out around 2-5% monthly churn after year two is a common and defensible starting point. In Excel, you can use the LOGEST function on your historical average retention rates to forecast this flattening curve. This statistical approach is more robust than simply picking an arbitrary churn number.

Projecting this curve out is essential, as Series A/B investors expect a model projected out 36-60 months. This process turns your limited history into a credible long-term forecast, which is crucial for calculating a complete and compelling lifetime value.

Part 4: Calculating Final LTV and Payback Period

With a full retention curve combining historical data and future projections, you can now calculate the final LTV. This process involves converting your retention percentages into a cumulative, margin-adjusted revenue figure that reflects the true profitability over a customer's lifetime.

1. Determine Average Revenue Per User (ARPU): First, establish the average monthly revenue you receive per customer. You can calculate a simple average across all customers or get more precise by calculating it on a cohort-by-cohort basis. For this model, an overall average provides a solid starting point.
2. Model Lifetime Revenue: Create a new table to calculate the full lifetime revenue stream. In one column, list the months of the customer lifetime (Month 0, Month 1, all the way to Month 60). In the next column, paste your complete retention curve (historical average plus your projection). In a third column, multiply the retention percentage for each month by your ARPU. This gives you the expected revenue per original customer for that specific month.
3. Calculate Cumulative Gross Margin: Sum the expected revenue column cumulatively to see the total lifetime revenue grow over time. However, revenue isn't profit. The final, critical step is to adjust for gross margin. This is essential for understanding true profitability, as guided by principles like SaaS revenue recognition. Multiply your total lifetime revenue by your gross margin percentage (e.g., 80% for a typical SaaS business) to arrive at the Gross Margin-adjusted LTV. This is the number that truly matters for your unit economics.

Part 5: Putting Your LTV Model to Work for Growth

An LTV model is not an academic exercise; it is a powerful decision-making tool that directly impacts your growth strategy, marketing budget, and cash flow planning. Once you have a reliable LTV figure, you can use it to drive smarter business decisions.

Set Your Customer Acquisition Cost (CAC) Ceiling

The most immediate application of LTV is setting a ceiling on your Customer Acquisition Cost (CAC). By understanding what a customer is worth in gross margin over their lifetime, you know exactly how much you can afford to spend to acquire one while maintaining profitability.

The key metric here is the LTV:CAC ratio. This simple ratio compares the lifetime value of a customer to the cost of acquiring them. For early-stage SaaS and e-commerce companies, investors typically look for an LTV:CAC ratio of 3:1 or higher. If your LTV is $3,000, this framework gives you a clear justification to spend up to $1,000 on CAC, providing a data-driven basis for your marketing budget.

Manage Cash Flow with the Payback Period

Next is the payback period, which measures how many months it takes to recoup your CAC from a new customer. You can calculate this by tracking the cumulative gross margin generated by a new customer month by month until it exceeds the initial CAC. This metric is critical for cash flow management.

A shorter payback period means your marketing spend is recycled back into the business faster, enabling more rapid, capital-efficient growth. For example, a 6-month payback period is far superior to a 24-month one, even if the LTV is the same, because you can reinvest that capital four times in two years. A payback period under 12 months is considered excellent for most SaaS businesses. This analysis connects your model directly to your bank account, informing how aggressively you can invest in growth without running out of cash.

Practical Takeaways for Founders

Building a cohort LTV model in Excel is one of the highest-impact financial exercises a founder can perform. It forces you to move beyond misleading blended averages and truly understand the underlying dynamics of your customer base. The process itself, from cleaning raw data to making defensible assumptions about the future, builds a deeper intuition for your business's health.

Remember that this is a living model. As you acquire more customers and collect more historical data, you should update your analysis to refine your retention curves and improve the accuracy of your forecasts. The goal is not to predict the future with perfect certainty, but to create a robust framework for making better-informed decisions today. Use this LTV model spreadsheet to set smarter marketing budgets, understand your cash flow dynamics, and confidently communicate your company's value to investors. You can find more strategies in the Improving Unit Economics guide and see related topics in the Unit Economics & Metrics hub.

Frequently Asked Questions

Q: How much historical data do I need for a cohort LTV analysis?
A: It is best to have at least 6 to 12 months of transaction data to create a meaningful analysis. Less than six months of data makes it difficult to establish a reliable historical retention curve, which can lead to inaccurate future projections and a less trustworthy LTV model.

Q: How does cohort analysis differ for SaaS and e-commerce businesses?
A: The core methodology is the same, but the revenue patterns differ. SaaS businesses typically see consistent monthly recurring revenue, leading to smoother retention curves. E-commerce businesses often see more irregular purchase behavior, which may require analyzing repeat purchase rates rather than continuous subscription revenue to calculate customer lifetime value.

Q: How often should I update my LTV model spreadsheet?
A: For most early-stage companies, updating your cohort LTV analysis on a quarterly basis is a good cadence. This is frequent enough to spot emerging trends in customer retention or spending habits without becoming an excessive administrative burden. If your business is experiencing very rapid change, a monthly update may be warranted.

Q: My retention curve doesn't seem to be flattening. What does that mean?
A: A retention curve that continues to decline steeply without flattening may indicate a product or market-fit issue. It suggests that even long-term customers are churning at a high rate, which is often unsustainable. This is a critical insight that your cohort analysis can reveal, prompting a deeper investigation into customer satisfaction.