Creator and newsletter model
Subscriber funnel, paid tier, sponsorship, LTV, breakeven. Build a newsletter from its subscriber funnel to monthly cash, compare a per-subscriber platform fee with a revenue share, and read the lifetime value, acquisition cost and payback of a paid subscriber from the same lines.
By Surojit Chakraverti, founder of L3VLUP and an investor running a long-short healthcare and technology equities strategy.
On this subject: creator and newsletter model.Updated 30 October 2026
Who builds it, and for whatThe plan a writer builds before turning on a paid tier or selling sponsorship, and the model a creator-economy investor, a media strategy team or a newsletter platform uses to judge one. It turns sign-ups by channel into a free list and a paid list, prices the paid tier through monthly and annual plans net of platform and card fees, adds sponsorship sold on opens, and asks the question that decides the business: when it first pays for the writer’s time, and which assumption moves that month most.
Every sheet below, in your browser, with its outputs and checks
Starter workbook free with an account. Worked model: one a month with Plus, every one with Pro. Downloads
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | AB | AC | AD | AE | AF | AG | AH | AI | AJ | AK | AL | AM | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Assumptions | ||||||||||||||||||||||||||||||||||||||
| 2 | Dollars and subscribers. Monthly periods, M1 to M36. Every figure is invented for illustration; the Sources sheet gives the basis of each. | ||||||||||||||||||||||||||||||||||||||
| 4 | Month | Unit | M1 | M2 | M3 | M4 | M5 | M6 | M7 | M8 | M9 | M10 | M11 | M12 | M13 | M14 | M15 | M16 | M17 | M18 | M19 | M20 | M21 | M22 | M23 | M24 | M25 | M26 | M27 | M28 | M29 | M30 | M31 | M32 | M33 | M34 | M35 | M36 | |
| 5 | Timeline | ||||||||||||||||||||||||||||||||||||||
| 6 | Month | # | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | 21 | 22 | 23 | 24 | 25 | 26 | 27 | 28 | 29 | 30 | 31 | 32 | 33 | 34 | 35 | 36 | |
| 8 | Scenario and platform | ||||||||||||||||||||||||||||||||||||||
| 9 | Scenario | # | 1 | 1 = base, 2 = upside, 3 = downside. Sets organic growth, free-to-paid conversion and paid churn. | |||||||||||||||||||||||||||||||||||
| 10 | Platform fee model | # | 1 | 1 = per subscriber, 2 = revenue share. The two structures newsletter platforms publish; the amounts are below. | |||||||||||||||||||||||||||||||||||
| 12 | Opening position | ||||||||||||||||||||||||||||||||||||||
| 13 | Free subscribers at the start | subscribers | 3000 | ||||||||||||||||||||||||||||||||||||
| 14 | Paid subscribers at the start | subscribers | 60 | ||||||||||||||||||||||||||||||||||||
| 15 | Cash at the start | $ | 10,000.00 | ||||||||||||||||||||||||||||||||||||
| 17 | Sign-ups by channel | ||||||||||||||||||||||||||||||||||||||
| 18 | Organic sign-ups, month 1 | subscribers | 250 | ||||||||||||||||||||||||||||||||||||
| 19 | Growth in organic sign-ups a month, base | % | 2.0% | ||||||||||||||||||||||||||||||||||||
| 20 | Growth in organic sign-ups a month, upside | % | 3.5% | ||||||||||||||||||||||||||||||||||||
| 21 | Growth in organic sign-ups a month, downside | % | 0.5% | ||||||||||||||||||||||||||||||||||||
| 22 | Growth in organic sign-ups, selected scenario | % | 2.0% | ||||||||||||||||||||||||||||||||||||
| 23 | Referral sign-ups a month, per opening subscriber | % | 0.8% | Readers bringing readers, on the whole opening list. | |||||||||||||||||||||||||||||||||||
| 24 | Paid acquisition spend a month | $ | 400.00 | ||||||||||||||||||||||||||||||||||||
| 25 | Cost per subscriber acquired | $ | 2.50 | ||||||||||||||||||||||||||||||||||||
| 26 | Cross-promotion sign-ups a month | subscribers | 120 | Recommendations and swaps with other newsletters. | |||||||||||||||||||||||||||||||||||
| 28 | Churn, cleaning and conversion | ||||||||||||||||||||||||||||||||||||||
| 29 | Unsubscribes a month, % of the list | % | 1.5% | ||||||||||||||||||||||||||||||||||||
| 30 | List cleaning a month, % of the list | % | 1.0% | Inactive addresses removed to protect deliverability. | |||||||||||||||||||||||||||||||||||
| 31 | Free-to-paid conversion a month, base | % | 0.4% | % of opening free subscribers. | |||||||||||||||||||||||||||||||||||
| 32 | Free-to-paid conversion a month, upside | % | 0.6% | ||||||||||||||||||||||||||||||||||||
| 33 | Free-to-paid conversion a month, downside | % | 0.3% | ||||||||||||||||||||||||||||||||||||
| 34 | Free-to-paid conversion, selected scenario | % | 0.4% | ||||||||||||||||||||||||||||||||||||
| 35 | Paid churn a month, base | % | 4.0% | % of opening paid subscribers who cancel. Includes paid readers who leave the list. | |||||||||||||||||||||||||||||||||||
| 36 | Paid churn a month, upside | % | 3.0% | ||||||||||||||||||||||||||||||||||||
| 37 | Paid churn a month, downside | % | 5.5% | ||||||||||||||||||||||||||||||||||||
| 38 | Paid churn, selected scenario | % | 4.0% | ||||||||||||||||||||||||||||||||||||
| 40 | Prices and fees | ||||||||||||||||||||||||||||||||||||||
| 41 | Paid price, monthly plan | $ a month | 8.00 | ||||||||||||||||||||||||||||||||||||
| 42 | Paid subscribers on the annual plan | % | 40.0% | ||||||||||||||||||||||||||||||||||||
| 43 | Annual plan discount to twelve monthly payments | % | 20.0% | ||||||||||||||||||||||||||||||||||||
| 44 | Annual plan price | $ a year | 76.80 | ||||||||||||||||||||||||||||||||||||
| 45 | Revenue per paid subscriber a month | $ a month | 7.36 | Monthly price on the monthly share; a twelfth of the annual price on the rest. | |||||||||||||||||||||||||||||||||||
| 46 | Platform fee per 1,000 subscribers a month (model 1) | $ | 15.00 | On the whole list, free and paid. No share of subscription revenue. | |||||||||||||||||||||||||||||||||||
| 47 | Platform fee, % of subscription revenue (model 2) | % | 10.0% | Nothing for the free list. | |||||||||||||||||||||||||||||||||||
| 48 | Card processing, % of each payment | % | 2.9% | ||||||||||||||||||||||||||||||||||||
| 49 | Card processing, fixed fee per payment | $ | 0.30 | ||||||||||||||||||||||||||||||||||||
| 50 | Payments a month per paid subscriber | # | 0.63 | One a month on the monthly plan, one a year on the annual. | |||||||||||||||||||||||||||||||||||
| 52 | Sponsorship and other revenue | ||||||||||||||||||||||||||||||||||||||
| 53 | Sends a month | sends | 4 | ||||||||||||||||||||||||||||||||||||
| 54 | Open rate | % | 45.0% | ||||||||||||||||||||||||||||||||||||
| 55 | Sell-through: sends carrying a sponsor | % | 50.0% | ||||||||||||||||||||||||||||||||||||
| 56 | Sponsorship CPM, per 1,000 opens | $ | 30.00 | ||||||||||||||||||||||||||||||||||||
| 57 | Other revenue a month: courses and affiliate | $ | 150.00 | ||||||||||||||||||||||||||||||||||||
| 59 | Costs | ||||||||||||||||||||||||||||||||||||||
| 60 | Owner’s draw: the writer’s time | $ a month | 2,000.00 | Paid every month. What the owner takes to live on, not a measure of what anyone earns. | |||||||||||||||||||||||||||||||||||
| 61 | Editing and design | $ a month | 300.00 | ||||||||||||||||||||||||||||||||||||
| 62 | Tools | $ a month | 80.00 | ||||||||||||||||||||||||||||||||||||
Select a cell to see its row, its schedule and the type of cell. Font colours: blue for inputs, black for formulas, green for links to other sheets. Scroll sideways to see every period.
Download
Creator and newsletter model: the workbook
An Excel workbook with working formulas, no macros and no external links: the same model you can work with on this page.
- Browse without an accountPreview every model online, sheet by sheet, with its outputs and checks.
- Free accountWork with every model online: change the inputs, switch the case, see every formula. And every starter workbook, with its key schedules left blank to build.
- L3VLUP Plus$4.99/monthEverything in Free, and one worked model each billing month as an Excel file: every schedule built, formulas live, checks at zero, yours to keep.
- L3VLUP Pro$25/monthEverything in Plus, every worked model in the library, and the Pro applications.
- The worked model
- 10 sheets and 2,431 live formulas, all 5 schedules built and linked, every check reading zero. Change an input in Excel and the whole model moves.
- The starter
- The same inputs and layout with the subscriber funnel left blank for you to build, each blank cell marked. The worked model is its answer.
What the base case says
- Paid subscribers, month 36
- 970
- Total list, month 36
- 19,375
- Revenue, month 36 ($)
- 7,688
- Contribution margin, month 36
- 86.0%
- First month the result covers the owner’s draw
- 17
- Cash at month 36 ($)
- 31,134
- Lifetime value of a paid subscriber ($)
- 173.54
- CAC payback (months)
- 2.61
- LTV / CAC
- 9.57
Read from the workbook as served, every input at its default. Periods: M1, M2, M3, M4, M5, M6, M7, M8, M9, M10, M11, M12, M13, M14, M15, M16, M17, M18, M19, M20, M21, M22, M23, M24, M25, M26, M27, M28, M29, M30, M31, M32, M33, M34, M35, M36. The figures are invented and move with whatever you type in.
What this model is
A monthly model of a newsletter with a paid tier and sponsorship over three years, M1 to M36: sign-ups by channel, a free list and a paid list rolled forward, subscription revenue by plan net of a platform’s fee and card processing, sponsorship sold on opens, and other revenue.
Costs are the writer’s time as an owner’s draw, editing and design, tools and paid acquisition. A cash roll-forward shows when the business first covers its writer, and the unit economics give the lifetime value of a paid subscriber and the months it takes to earn back what one cost to acquire.
Uses: seeing which of conversion, churn, growth and sponsorship moves a newsletter’s economics most, and comparing a platform that charges per subscriber with one that takes a share of revenue.
Seats: Product and technology, Venture capital, Every seat.
How the schedules connect
Every row in the workbook is tagged with the schedule it belongs to; the inspector above shows the tag when you click a row. These are the schedules this model is made of and where each one lives.
Subscriber funnel
Sign-ups by channel, the free list and the paid list
Assumptions, rows 13–15 · Assumptions, rows 18–26 · Assumptions, rows 29–38 · Subscribers, rows 6–10 · Subscribers, rows 13–15 · Subscribers, rows 18–23 · Subscribers, rows 26–29
Subscription revenue and fees
Monthly and annual plans, platform and card fees
Assumptions, rows 41–50 · Revenue, rows 6–11
Sponsorship revenue
Opens sold, CPM and other revenue
Assumptions, rows 53–57 · Revenue, rows 14–16 · Revenue, rows 19–20
Creator unit economics and breakeven
Contribution, cash, LTV, CAC payback, breakeven
Assumptions, rows 60–62 · Cash, rows 6–10 · Cash, rows 13–16 · Cash, rows 19–23 · Unit Economics, rows 5–7 · Unit Economics, rows 10–14
Sensitivity tables
Two assumptions at once, without a data table
Sensitivity, rows 5–8 · Sensitivity, rows 11–14 · Sensitivity, rows 18–23 · Workings, rows 6–20 · Workings, rows 23–32 · Workings, rows 35–38
What you should be able to explain
- Why the free list and the paid list are separate stocks with their own flows, and why free plus paid must equal the list every month.
- How conversion and paid churn set the paid list together: the paid list settles where conversions equal cancellations.
- Why a platform that charges per subscriber costs more while the list is large and the paid tier small, and a revenue share costs more once the paid tier earns.
- How sponsorship revenue is built from the list, the open rate, the sends, the share sold and the CPM, and why it is worth a free reader as much as a paid one.
- How lifetime value, the chance a free subscriber ever pays and CAC payback follow from churn, conversion and net revenue per subscriber.
What a reviewer looks for
- Paid subscribers as a fixed share of the list, so conversion and churn disappear from the model.
- Gross subscription revenue counted as income, before the platform’s share and card processing.
- Sponsorship priced on the whole list rather than on opens actually sold.
- Acquisition cost per free subscriber compared with the lifetime value of a paid one, which makes paid acquisition look several times cheaper than it is.
- The writer’s time left out of the costs, so a newsletter that does not pay its writer looks profitable.
Conventions this workbook uses
Stated on the cover sheet too. A model is only as trustworthy as the decisions it tells you it made.
- Monthly periods. Every flow reads the opening balance of the month: sign-ups by referral, unsubscribes and cleaning on the opening list, conversion on opening free subscribers, cancellation on opening paid subscribers.
- The total list (free and paid) grows by sign-ups and loses unsubscribes and cleaned addresses. Paid readers leave the list at the same rates as free ones, and the rest of paid churn returns to the free list; paid churn below the list’s exit rates is flagged as a warning.
- Subscription revenue is earned on the average paid list of the month: the monthly price on the monthly share and a twelfth of the annual price on the annual share. Annual plans are recognised and collected a twelfth a month; collecting them up front would improve cash, not profit.
- Platform fee model 1 charges per 1,000 subscribers on the closing list and takes nothing from subscriptions; model 2 takes a share of subscription revenue and charges nothing for free readers. Card processing is a percentage of subscription revenue plus a fixed fee per payment, one a month on the monthly plan and one a year on the annual.
- Sponsorship revenue = closing list x open rate x sends x sell-through / 1,000 x CPM. Other revenue is a flat amount a month.
- Contribution = revenue less platform and processing fees and paid acquisition. The result after the owner’s draw also deducts editing, design and tools, and is the month’s cash flow. All figures are before the owner’s personal income tax.
- Lifetime value = net revenue per paid subscriber a month / paid churn, undiscounted. The chance a new free subscriber ever pays = conversion / (conversion + unsubscribes + cleaning); acquisition cost per paid subscriber = cost per subscriber / that chance; CAC payback = that cost / net revenue a month.
- The revenue grid runs one paid row per cell against the model’s own list, which does not depend on conversion or churn. The cash grid runs a list and a paid row per organic growth column; cash is linear in the CPM, so each row of that grid needs no rows of its own.
Build it yourself
The starter workbook
The Subscribers sheet has been cleared, with the selected growth, conversion and churn rates on the Assumptions sheet: sign-ups by channel, the total list, and the free and paid lists each rolled forward. Build them from the opening position and the rates so the Revenue and Cash sheets calculate again. The Checks sheet shows when free plus paid equals the list in every month and both rolls reconcile over the three years.
Blanks: Subscriber funnel. Download it beside the worked model.
The path around this model
Understand it, drill it, read the build, then apply it to a real company.
Build · Lab · ~12 min
Unit Economics
Build an LTV:CAC you could defend under questioning, and answer payback as its own question.
Read · Guide · 11 min
SaaS Operating Models: ARR, Sales Capacity, Billings and the Metrics That Follow
Read · Guide · 10 min
Scenario and Sensitivity Architecture: Building a Model That Can Be Stress-Tested
Read · Guide · 11 min
Financial Modelling Best Practices: The Conventions That Make a Model Auditable
Apply · Skill
Model Audit
Find the errors in a financial model before someone senior does.
Vocabulary: CAC Payback, LTV to CAC, Contribution Margin, Burn Rate and Runway.
Questions about this model
What does the base case show?
From 3,000 free and 60 paid subscribers, the list grows to about 19,400 by month 36 and the paid tier to about 970. Revenue in month 36 is about $7,700, most of it subscriptions, at a contribution margin of 86% on the per-subscriber platform. The result first covers the $2,000 owner’s draw in month 17, and cash ends the three years at about $31,100. Every figure is invented; it is a worked example of the arithmetic, not a forecast for any newsletter.
Which is cheaper, a per-subscriber platform fee or a revenue share?
It depends on the paid tier’s share of the list. Switch the platform fee model to 2 and the same newsletter pays 10% of subscription revenue instead of $15 per 1,000 subscribers a month: the margin in month 36 falls from 86% to 81%, the draw is first covered a month later, and cash at month 36 is about $5,500 lower. With a smaller paid tier the comparison reverses. The two structures follow what beehiiv and Substack publish, dated on the Sources sheet; fees change, so check the platforms’ own pages.
How is the lifetime value of a paid subscriber worked out?
Net revenue per paid subscriber a month, after card processing and the platform fee, divided by monthly paid churn. At $7.36 of revenue a month and 4% churn that is about $174, undiscounted. The acquisition cost per paid subscriber is the cost of a free one, $2.50, divided by the chance a free subscriber ever pays (about 14%), so about $18, earned back in under three months.
What moves the result most?
Conversion and paid churn, which is why they are the axes of the revenue grid: at 0.6% conversion and 3% churn, revenue in month 36 is about $12,100; at 0.2% and 5% it is about $3,900. Sponsorship matters less while the list is small; the cash grid shows a $10 change in CPM moving cash at month 36 by about $3,700 at the base growth rate.
Related models
Technology · Core
SaaS operating model
Sales capacity, ARR roll-forward, billings, unit economics
Technology · Core
Marketplace model: GMV, take rate and contribution
Buyer cohorts, GMV, take rate, contribution per order
Core modelling · Core
Independent trading business model
Risk budget, costs, capital roll-forward, break-even Sharpe
Questions about this model
Discussions open here soon.