# Build a Unit Economics model

Source: https://www.hemrock.com/unit-economics-tool/prompt

Paste everything below into Claude, ChatGPT or Gemini.

---

You are an experienced startup finance modeler. Build me a unit economics model in a spreadsheet: the lifetime value of one customer, what it costs to acquire them, and how long it takes to earn that cost back. It should be a working model I can edit, not a one-off calculation.

## Before you build

Ask me these questions first, in one message, and wait for my answers. If I say "use the defaults", use the defaults in the inputs table below.

1. What do you sell, and how does a customer pay you: a recurring subscription, one-time purchases, repeat transactions, or a mix?
2. What does a customer pay per billing period, per one-time purchase and per transaction?
3. What does it cost you to deliver each of those, as a percent of revenue?
4. How often do customers renew or come back, and what share leave each time?
5. What does it cost to acquire a customer (CAC), and do you spend anything to retain or re-engage them?

## Workbook structure

One working sheet called "Unit Economics", laid out top to bottom: inputs, then headline outputs, then a month-by-month detail section for one customer. Add a "Key Reports" sheet with charts only (no numbers) that plot rows from the detail section.

## Inputs

Each input is one row with a label, a note and the value. Group them under bold section labels.

| Section | Input | Default |
| --- | --- | --- |
| Revenues | Revenue line 1, per unit, and its type (Recurring, One time or Transaction, as a dropdown) | $25, Recurring |
| | Revenue line 2, per unit, and its type | $0, Recurring |
| | Revenue line 3, per unit, and its type | $0, Transaction |
| Cost of sales | Cost of sales line 1, % of revenue, and its type | 10%, Recurring |
| | Cost of sales line 2, % of revenue, and its type | 0%, One time |
| | Cost of sales line 3, % of revenue, and its type | 0%, Transaction |
| Discounts | Discount on the first recurring billing, $ | $0 |
| | Discount on the first transaction, $ | $0 |
| Recurring | Billing period, months | 1 |
| | Churn period, months (how often a customer can leave) | 1 |
| | Churn per churn period, % | 10% |
| | Annual growth in gross margin per unit, % (price increases, expansion) | 5% |
| One time | Month after acquisition the one-time revenue lands | 0 |
| Transaction | Months between repeat transactions | 0 |
| | Churn per transaction period, % | 0% |
| | Annual growth in gross margin per transaction, % | 0% |
| Valuation | Discount rate, annual | 10% |
| | Months of customer lifetime counted in LTV (a cap, so LTV can't run forever) | 60 |
| | Months of cash flow to show in the charts | 48 |
| Acquisition | Customer acquisition cost (CAC), $ | $65 |
| | Retention cost per renewal, $ | $0 |
| | Repeat cost per repeat transaction, $ | $0 |

## Calculations

Sum the revenue lines and cost of sales lines by type, so each type (recurring, one-time, transaction) has one revenue per unit and one cost of sales percent.

Gross margin per unit, by type:
- Recurring: revenue × (1 − cost of sales %), less the retention cost per renewal.
- One time: revenue × (1 − cost of sales %).
- Transaction: revenue × (1 − cost of sales %), less the repeat cost per transaction.

Supporting metrics:
- Average customer lifetime, months = churn period ÷ churn per churn period.
- Repeat transactions per year = 12 ÷ months between transactions (0 if that input is 0).
- Lifetime transactions = 1 ÷ churn per transaction period (0 if churn is 0).

The detail section runs one column per month after acquisition, month 0 through month 120, so it always covers the LTV cap. Rows, for each revenue type:
- Month number, a renewal counter that steps up every churn period, a billing counter that steps up every billing period, and a transaction counter that steps up every transaction period.
- Share of customers still active: (1 − churn per period) raised to the number of churn periods passed. Do the same for transactions with their own churn.
- Recurring revenue per month: the recurring revenue per unit, grown at each renewal by the annual margin growth compounded over the churn period ((1 + growth) raised to churn period ÷ 12, per renewal), and multiplied by the share still active.
- One-time revenue: lands only in the month set by the input.
- Transaction revenue: lands in the months a repeat transaction happens, grown the same way at each transaction ((1 + growth) raised to months between transactions ÷ 12) and multiplied by the share still active.
- Cost of sales per type, as the cost percent of that revenue.
- Billings: recurring revenue is billed in full at the start of each billing period, less the first-billing discount in month 0.
- Acquisition and retention costs: CAC in month 0, retention cost at each renewal and repeat cost at each repeat transaction, each weighted by the share still active.
- Gross margin per month across all types: revenue less cost of sales. Revenue already carries the share still active, so don't multiply by it again. Then the discount factor (1 + annual discount rate ÷ 12) raised to the month number, discounted gross margin, and cumulative discounted gross margin.
- Net cash flow (billings less cost of sales less acquisition and retention costs), cumulative net cash flow, and the discounted versions of both.

## Outputs

Put these right under the inputs, each a formula:
- LTV, recurring: the sum over months 0 to LTV months − 1 of (recurring revenue − recurring cost of sales − retention costs) ÷ the discount factor, less the first-billing discount.
- LTV, one time: one-time gross margin discounted back from the month it lands.
- LTV, transaction: the same sum for transaction revenue, its cost of sales and repeat costs, less the first-transaction discount.
- LTV, total.
- LTV ÷ CAC.
- CAC payback, months: CAC ÷ monthly gross margin per unit across types.
- Breakeven month: the first month cumulative net cash flow turns positive (blank if it never does within the detail).
- Share of LTV realized by month 12, 24 and 36.

## Key Reports

Three charts, each covering the months set by the charts input: cumulative net cash flow by month (with the zero line, so the payback month is visible), monthly gross margin by revenue type, and cumulative discounted gross margin as a percent of LTV.

## Model checks

A small block that returns TRUE or FALSE: total LTV equals the sum of the three types; churn inputs are between 0% and 100%; the LTV months are 120 or fewer; CAC is not zero.

## How the workbook should look and behave

These are my house rules for spreadsheets. Follow all of them.

- Use Arial 12 everywhere, including the workbook's default style. Never Calibri.
- Inputs are blue text (#0000FF) on a light grey fill (#F2F2F2), so they read like form fields. Calculations are black text with no fill. Never use grey text anywhere.
- Every sheet leaves column A blank and narrow. The model starts in column B.
- Column B holds row labels. Column C is a narrow Notes column, about half the width of the label column, holding a short note on what the row is or where it comes from. Let notes clip rather than wrap. Values, or the first period of a timeline, start after the notes column.
- No cell comments. Notes go in the notes column.
- Turn gridlines off on every sheet. Freeze panes on the header rows only, never on columns.
- No borders at all, not even header underlines or subtotal rules. Blank rows and bold text carry the structure.
- Every calculated number is a live formula that traces back to an input. Never type a number into a calculation cell, and never mix typed values and formulas in the same row.
- Keep the number of sheets low. Sheets that share a timeline use the same columns, so a given column is the same month on every sheet.
- Format currency with thousands separators and no decimals, percentages with one decimal place, and dates as "mmm yyyy".
- Name rows in plain words a founder or investor would use, with units in the label where it helps ("Churn per contract cycle, %").

## When you're done

- Recalculate and confirm there are no errors (#REF!, #DIV/0!, #VALUE!, #N/A) and no circular references unless I asked for one.
- Show me the results of the model checks and the headline outputs.
- List every assumption you made that I didn't give you, and the three inputs I should change first.
- If you can't produce an .xlsx file, give me each sheet as a table with the exact label, input value or formula in every cell, so I can rebuild it in Excel or Google Sheets without guessing.
