Subscription Revenue Model in Excel: MRR, Churn, Cohorts & Forecasting

A subscription business model describes how customers pay: repeatedly for ongoing access, service, or usage. A subscription revenue model is the financial model behind it. It forecasts customers, recurring revenue, churn, expansion, recognized revenue, cash collections, gross margin, and unit economics so founders can see what growth actually does to revenue and cash.

TL;DR:

• Model subscription revenue as movements: beginning MRR plus new, expansion, and reactivation MRR, less contraction and churned MRR, equals ending MRR.
• Track customer churn, gross revenue retention (GRR), net revenue retention (NRR), gross margin, CAC payback, and cohort behavior alongside MRR and ARR.
• Separate recurring run-rate metrics from accounting revenue and cash. Annual prepayments can produce cash today while revenue is recognized over time when the performance obligation is satisfied over the service period.
• Build from a clean customer/subscription data layer, then use an MRR bridge, cohort analysis, and a forecast/unit-economics dashboard rather than relying on manually typed summary totals.
• Hybrid pricing can fit better than a pure subscription when usage or delivery costs vary materially, but the model should separate contracted recurring revenue from variable usage revenue.

Table of Contents

• What a subscription revenue model is and where it applies
• Types of subscription models and how to choose between them
• Why subscriptions pay off: benefits and business case
• How subscriptions operate: the lifecycle and revenue math
• Core KPIs and a spreadsheet-friendly dashboard checklist
• Step-by-step implementation sequence for pricing and launch
• When subscription is a poor fit and hybrid alternatives to consider
• How Frac CFO’s spreadsheet tools support subscription models
• Three priorities we would push on if we were launching a subscription today
• Get hands-on help building your subscription model
• FAQ
• Sources

What a subscription revenue model is and where it applies

A subscription revenue model turns a product or service into a recurring relationship rather than a single transaction. Instead of closing a sale and starting from zero the next month, you build a base of paying customers whose contracts renew automatically unless they cancel. According to Harvard Business School’s background note on subscription models, this structure spans far more industries than most owners assume, and its resurgence comes from a simple shift: customers increasingly prefer access to ownership, and businesses prefer stable, forecastable revenue over lumpy sales cycles.

Common applications include:

• Software as a service (SaaS): tools billed monthly or annually for continued access, like project management or accounting platforms.
• Membership media: newsletters, streaming services, and content platforms billed for ongoing access rather than per-article or per-episode purchases.
• Product replenishment: razors, pet food, supplements, and other consumables shipped on a recurring schedule.
• Professional services retainers: ongoing advisory, legal, or financial support billed monthly instead of per project.

Consider a graphic design studio that used to charge $2,000 per logo project. Switching even a portion of clients to a $500 monthly retainer for ongoing design work changes the economics entirely: instead of chasing a new client every month to replace the one that just finished, the studio knows its baseline revenue for the next quarter before it starts.

Types of subscription models and how to choose between them

Not every subscription looks the same, and picking the wrong archetype for your offer can quietly cap your growth. Here are the five structures worth considering:

1. Pure recurring: a flat fee for unlimited or defined access, billed on a fixed cadence. This fits software, memberships, and content where usage is hard to meter and customers want price certainty. The downside is that heavy users and light users pay the same, which can leave money on the table.
2. Usage or consumption based: customers pay based on what they consume, like API calls or storage. This fits infrastructure and utility-style products where usage varies widely. It aligns price with value but makes revenue harder to forecast month to month.
3. Hybrid (base plus usage): a fixed platform fee plus variable charges for overages or add-ons. This fits businesses that want forecasting stability and the ability to capture upside from power users.
4. Membership: access to a community, discounts, or a bundle of benefits rather than a single product. This fits retailers and service businesses building loyalty rather than metering a discrete deliverable.
5. Replenishment: scheduled recurring shipments of a physical product. This fits consumables with a predictable consumption cycle, though shipping and fulfillment costs eat into margin more than digital models.

To choose, start with how customers perceive value. If value scales with consumption, usage-based or hybrid pricing may fit better; if customers value continuous access and predictability, fixed recurring or membership pricing may fit better. Test alternatives with a sufficiently large and mature cohort, then compare conversion, ARPA, expansion, churn, gross margin, and retention quality. Do not force a fixed 60- or 90-day test window when the sales cycle or renewal cadence requires more time.

Why subscriptions pay off: benefits and business case

The business case for subscriptions rests on three measurable advantages. First, predictable revenue improves forecasting accuracy, since you can project next quarter’s baseline from existing subscribers rather than guessing at new sales. Second, the ongoing relationship creates natural expansion opportunities: upsells, add-ons, and tier upgrades become easier conversations once a customer already trusts you. Third, retained customers tend to cost less to serve over time relative to the revenue they generate, improving the ratio between customer lifetime value and acquisition cost.

• Forecasting: recurring billing gives you a known revenue floor for each upcoming period.
• Expansion revenue: existing subscribers are easier to upgrade than new prospects are to close.
• Lower relative acquisition drag: a customer retained longer spreads your acquisition cost across more revenue.

A word of caution: research on subscription renewal behavior finds that cancellation friction and simple inertia can inflate apparent retention, with some services experiencing renewal revenue that significantly exceeds what engagement alone would predict. That means a healthy-looking churn number can mask a product customers no longer value. Pair your churn metrics with engagement data and cancellation-reason tracking so you know whether people are staying because they want to or because canceling is a hassle.

How subscriptions operate: the lifecycle and revenue math

A subscription business runs on a repeating cycle, and treating it as a single pipeline rather than isolated events is what makes forecasting possible. The Harvard Business School note frames this as an operating system with distinct, trackable stages:

1. Acquire: a prospect converts into a paying subscriber.
2. Activate: the customer completes onboarding and reaches first value.
3. Deliver: ongoing use of the product or service across the billing period.
4. Renew or retain: the subscription continues into the next period.
5. Expand or contract: the customer upgrades, adds seats, or downgrades.
6. Churn: the customer cancels and stops paying.

MRR is monthly-normalized recurring subscription value. It is not invoice value, cash collected, or accounting revenue. For example, a $1,200 annual subscription normally contributes $100 of MRR. Stripe’s MRR/ARR guidance makes the same distinction between recurring run rate and cash or recognized revenue.

A complete MRR bridge is: Beginning MRR + New MRR + Expansion MRR + Reactivation MRR − Contraction MRR − Churned MRR = Ending MRR. Reactivation matters because a returning customer is economically different from a brand-new acquisition.

Say you start the month with $50,000 in MRR. New customers add $8,000, existing customers expand by $3,000, reactivated customers add $500, downgrades remove $1,500, and cancellations remove $4,000. Ending MRR is $56,000. Tracking each movement separately shows whether growth comes from acquisition, expansion, recovery of former customers, or simply lower churn.

Keep four concepts separate in the model: MRR/ARR for normalized recurring run rate, recognized revenue for the income statement, billings/invoices for what customers are charged, and cash collections for what reaches the bank. An annual prepayment can create cash and deferred revenue immediately, while revenue is recognized as the related performance obligation is satisfied. Under IFRS 15, recognition can occur over time or at a point in time depending on the promised performance obligation.

Core KPIs and a spreadsheet-friendly dashboard checklist

MRR and ARR are only the starting point. A founder-level dashboard should explain whether the installed customer base is shrinking, stable, or expanding—and whether that growth produces enough gross profit to recover acquisition cost.

• MRR and ARR: normalized recurring run rate. For a stable monthly base, ARR is commonly shown as MRR × 12; exclude one-off implementation fees and other non-recurring revenue.
• Logo churn: churned customers ÷ beginning customers.
• Gross Revenue Retention (GRR): (Beginning MRR − Churned MRR − Contraction MRR) ÷ Beginning MRR. GRR excludes expansion and cannot exceed 100%.
• Net Revenue Retention (NRR): (Beginning MRR + Expansion MRR − Contraction MRR − Churned MRR) ÷ Beginning MRR. NRR shows whether the existing customer base expands or contracts economically.
• ARPA: recurring revenue per account, useful for tracking mix and pricing changes.
• Gross margin: recurring revenue less direct service or delivery costs. This matters because $100 of MRR at 90% gross margin is not economically equivalent to $100 at 35%.
• CAC and CAC payback: acquisition cost and the months of recurring gross profit required to recover it.
• LTV and LTV:CAC: useful unit-economics indicators, but avoid treating a simplistic ARPA ÷ churn formula as precise when churn, expansion, or gross margin varies materially by cohort.

Blended company averages can hide serious problems. Track both logo-retention cohorts and revenue-retention cohorts: a cohort may retain fewer customers while still expanding its original MRR if the remaining accounts upgrade.

A practical workbook uses four connected tabs:

Pro Tip: Build your cohort retention grid with signup month as rows and months-since-signup as columns, then conditional-format it as a heat map so declining retention jumps out visually before it shows up in your topline numbers.

Step-by-step implementation sequence for pricing and launch

Moving to a subscription model works best as a sequence rather than a single leap. The Salesforce implementation guide outlines a practical order:

• Define the recurring promise: decide exactly what ongoing value you are delivering and to whom.
• Choose billing frequency and pricing metric: monthly versus annual, and flat fee versus usage based.
• Build scenario-based forecasts: model new, expansion, contraction, and churned MRR separately rather than assuming one blended growth rate.
• Instrument billing and customer data: make sure every signup, upgrade, downgrade, and cancellation is captured somewhere you can analyze.
• Plan onboarding: customers who reach value quickly renew at higher rates.
• Review cohort results and iterate: adjust pricing and delivery based on what actual cohorts show, not assumptions.

For the forecast itself, build monthly drivers rather than typing a single growth percentage. Start with Beginning Customers + New Customers − Churned Customers = Ending Customers, preferably by plan or segment. Then model new MRR, expansion, reactivation, contraction, churn, and ending MRR from those customer movements. Add Base, Upside, and Downside assumptions for acquisition, churn, pricing, expansion, gross margin, and collection timing.

For annual or hybrid contracts, keep contracted/base recurring revenue separate from variable usage revenue. Then bridge the model from recurring run rate to recognized revenue, billings, cash collections, and deferred revenue. That prevents a temporary usage spike or annual prepayment from being mistaken for permanently higher recurring revenue.

Document how the model treats free trials, discounts, delinquent accounts, pauses, cancellations, upgrades, downgrades, and reactivations. Consistent classification matters more than choosing the most sophisticated formula.

When subscription is a poor fit and hybrid alternatives to consider

Subscriptions are not the right structure for every offer, and forcing one onto a business that does not fit it usually shows up as high churn and frustrated customers. The Harvard Business School note points to a few clear warning signs.

• The customer need is genuinely one-off, like a single home renovation or a one-time legal filing.
• Usage is too irregular to justify a steady monthly charge.
• Ongoing delivery costs run too high relative to what you can charge, eroding margin every period.
• You cannot demonstrate value before renewal, which guarantees cancellations once the novelty wears off.

When these conditions apply, a hybrid structure can fit better than a pure subscription. A base platform fee plus usage charges preserves recurring revenue while reflecting variable demand. A setup fee plus ongoing retainer can fit services with heavy upfront work. A transaction fee plus maintenance retainer can fit project-based businesses with genuine ongoing support value.

Evaluate a hybrid pilot using the economics that actually matter: conversion, recurring and usage revenue per account, gross margin, expansion, contraction, churn, and cohort retention. The right observation window depends on the billing and renewal cycle rather than an arbitrary calendar quarter.

How Frac CFO’s spreadsheet tools support subscription models

We built our template and service lineup around the same metrics and lifecycle stages covered above, because that is what spreadsheet-based finance looks like when it is done properly. Our SaaS Metrics Dashboard Excel Template is structured around MRR, ARR, churn, and cohort tracking, so the layout suggestions in the KPI section above map directly onto tabs you can start filling in.

• SaaS Metrics Dashboard Excel Template: pre-built formulas for MRR movement, churn, and cohort retention so you are not starting from a blank sheet.
• Custom Financial Model: scenario-based forecasting for new, expansion, contraction, and churned MRR when your business needs projections tailored beyond a template.
• Sales CRM Excel Template: tracks leads and conversions, which supports the acquisition and cohort attribution work described in the implementation section.

Every template is built for Excel or Google Sheets, since that is the environment typically used by clients and the one we specialize in supporting.

Three priorities we would push on if we were launching a subscription today

First, validate that customers receive recurring value before building recurring billing around the offer. Second, instrument customer-level MRR movements and cohorts from day one so low churn is not automatically mistaken for high engagement. Third, model gross margin and cash—not just ARR—because growth that consumes cash or carries weak delivery margins can look healthier in an MRR chart than it is economically.

One common modeling mistake is treating the whole customer base as a single blended average. Segmenting cohorts, plans, acquisition channels, and customer sizes makes churn, expansion, payback, and gross-margin problems visible much earlier. Build that math before committing to a complex billing stack.

About the author: Jonas is the founder of Frac CFO, holds a master’s degree in Quantitative Finance and the FMVA® credential, and builds financial models, cash-flow systems, dashboards, and decision tools for growing businesses.

Get hands-on help building your subscription model

If you are ready to put these metrics into practice, we can build the spreadsheet infrastructure for you instead of leaving you to piece it together alone. Our Custom Financial Model service builds scenario-based MRR and cohort forecasts tailored to your specific pricing structure, while our SaaS Metrics Dashboard Excel Template and Sales CRM Excel Template give you ready-made frameworks to start tracking today.

Every deliverable comes with onboarding support, and if you want ongoing guidance as your subscriber base grows, our GROWTH plan at $1,750 per month pairs dashboard work with continued forecasting support. For a lighter starting point, the STARTER plan runs $600 per month. Reach out through our custom finance project page to talk through what fits your stage.

FAQ

What is a subscription revenue model?

A subscription revenue model charges customers repeatedly, typically monthly or annually, for ongoing access to a product or service rather than collecting full payment in one transaction. Its economic engine is retention, and it spans industries including SaaS, media, product replenishment, and professional services.

Is a subscription model profitable?

Subscription models can be profitable when retention is strong enough to offset acquisition costs over a customer’s lifetime, since repeat billing spreads that cost across many periods. Profitability depends heavily on whether retention reflects genuine value or simple cancellation friction, so tracking engagement alongside churn matters.

What are the three main types of subscription models?

While there are more than three structures in practice, the most commonly cited core types are pure recurring access, usage or consumption based billing, and hybrid models that combine a base fee with usage charges. Membership and replenishment models are often treated as variations built on these foundations.

What are the three main types of revenue models?

Revenue models are broadly grouped into transactional (one-time sales), recurring or subscription based (ongoing billing for continued access), and usage based (charges tied to consumption). Many businesses combine elements of more than one as they grow.

How is subscription revenue recognized for accounting purposes?

Under ASC 606, subscription revenue is recognized as performance obligations are satisfied, which usually means spreading an annual payment across the service period rather than recording it all upfront. This differs from cash collected, which may arrive in full at the start of the contract.

Sources

• Subscription Models: Recurring Revenues for Lasting Growth – Background Note – Harvard Business School
• How To Move To a Subscription Business Model — Salesforce
• Seizing the subscription business model | IBM
• A guide to revenue recognition V2 — RSM US

Recommended

• Investor Pitch Model Excel Template
• Three Statement Financial Model Excel Template
• Finance Excel Bundle
• 13-Week Cash Flow Forecast