Subscription Churn & Cohort Analysis Model — Excel Template with LTV & CAC Payback

Excel Subscription Churn and Cohort Analysis Model

What This Model Does

This Excel Subscription Churn and Cohort Analysis model tracks six monthly customer cohorts over a 12-month retention curve, applying a monthly churn rate to project surviving customers and cohort revenue over time. It rolls the cohorts up into a blended retention curve, computes customer lifetime value (LTV) from ARPU, gross margin, and churn, and compares LTV to customer acquisition cost (CAC) to derive an LTV:CAC ratio and CAC payback period.

Who It Is For

Built for:

• SaaS and subscription business finance teams tracking retention and lifetime value


• Founders preparing investor metrics decks


• Growth and marketing teams evaluating CAC efficiency


• Consultants benchmarking subscription unit economics
  
  

What Is Included

The workbook follows the BK Finance Models Playbook v3.0 architecture with 9 sheets:

• 00 Cover


• 01 Instructions


• 02 Assumptions


• 03 Inputs (ARPU, churn, margin, CAC, and cohort sizes)


• 04 Calculations (6-cohort retention and revenue engine plus LTV and CAC payback)


• 05 Dashboard (retention curve chart and key metrics)


• 06 Sensitivity (churn rate vs. LTV table)


• 07 Checks (10 automated validation checks)


• 09 Version History
  
  

Formatting: Blue cells are user inputs, black cells are protected formulas. The file works in Microsoft 365, Excel 2016+, and LibreOffice Calc.