Prompt to build an Unit Economics Tool
LTV, LTV to CAC and payback for one customer, across recurring, one-time and transaction revenue. Paste the prompt into Claude, ChatGPT or Gemini, answer its questions, and it builds the workbook with the same structure as my Unit Economics Tool.
Or get the finished model

Unit Economics Tool
Built and tested, for Excel and Google Sheets and in your browser. $20 suggested, pay what you want.
How to use it
You need an AI that hands back a real spreadsheet file, not a description of one. Here’s where that lives in each:
- Claude creates .xlsx files on every plan, free included, and it’s on by default. If it only describes the workbook, open Settings, then Capabilities, and turn on “Code execution and file creation” (called “Create and edit files” in newer versions). On a Team or Enterprise plan your admin controls it.
- ChatGPT has no switch to find. Ask for the workbook as an .xlsx file and it gives you a download link. A paid plan copes better with a model this size.
- Gemini builds working spreadsheets inside Google Sheets: open a blank sheet, click the Gemini icon in the side panel and paste the prompt there. That needs a Google AI Pro or Ultra plan or a Workspace plan with Gemini.
The prompt opens with a few questions about your business. Answer them, or tell it to use the defaults and see the model working before you put your own numbers in.
When it’s done, open the workbook and read the model checks before you trust a number. An AI that builds a model from a prompt still makes mistakes, and the checks are there to catch them. If you want a second pair of eyes on it, that’s what a working session is for.
The prompt
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.