LTV to CAC Ratio Formula: How to Calculate It in Excel Without Fooling Yourself

The LTV to CAC ratio compares what a customer is worth over their whole relationship with you to what it cost to win them. Investors ask for it because it answers one question fast: does spending another dollar on growth create value or destroy it. The formula is simple. The problem is that each half has a few inputs you get to choose, and nearly every choice makes the ratio look better. This guide walks the formulas, runs them on a sample B2B SaaS company, and then applies the two adjustments that separate a pitch-deck ratio from one that survives diligence.

The two formulas

Customer acquisition cost (CAC) is sales and marketing spend in a period divided by new customers won in that period.

CAC = Sales and marketing spend ÷ New customers added

Lifetime value (LTV) for a subscription business is the gross profit one customer produces per month, divided by the monthly churn rate.

LTV = (ARPU × Gross margin %) ÷ Monthly churn %

ARPU is average revenue per customer per month. Dividing by churn works because a business that loses 4% of its customers each month keeps the average customer for 1 ÷ 0.04 = 25 months.

Use gross margin, not revenue. A customer paying $500 a month who costs you $90 a month to host and support is worth $410 a month to you, not $500. Revenue-based LTV is the most common error in early decks and the easiest one for an investor to spot.

The ratio is then:

LTV:CAC = LTV ÷ CAC

A related number, CAC payback, tells you how many months of gross profit it takes to earn back the acquisition cost:

CAC payback (months) = CAC ÷ (ARPU × Gross margin %)

The common rules of thumb are an LTV:CAC of at least 3x and a payback under 12 months. They are rules of thumb, not laws, but they are what most people will hold your numbers against.

A worked example

Take a sample B2B SaaS company in its first year. It sells a $500 a month core subscription and a $180 a month add-on. Its marketing budget is 25% of revenue. In Year 1 it:

CAC is $242,951 ÷ 465 = $522.77.

Monthly gross profit per customer is $414.29 × 81.2% = $336.40.

LTV is $336.40 ÷ 4.4% = about $7,650. The model computes $7,662.45 because it carries churn to more decimal places than the rounded 4.4%.

LTV:CAC is $7,662.45 ÷ $522.77 = 14.66x, and payback is $522.77 ÷ $336.40 = 1.6 months.

That is five times the 3x benchmark with payback in under two months. If you saw this in a pitch deck you should be suspicious, and so should the founder who built it. Two things are inflating it.

Adjustment 1: fully load the CAC

The CAC above counts only the marketing budget. It leaves out the people whose job is to win customers. This company hires a sales lead in month 4 at a fully loaded $9,500 a month and a marketing manager in month 11 at $8,500. In Year 2 a sales rep joins in month 18 at $8,000.

Their Year 1 cost is:

Fully loaded CAC is ($242,951 + $102,500) ÷ 465 = $742.91, up 42%.

Run the same adjustment across three years and the picture changes:

| | Year 1 | Year 2 | Year 3 | |---|---|---|---| | Marketing-only CAC | $522.77 | $931.44 | $1,069.46 | | Sales and marketing payroll | $102,500 | $272,000 | $312,000 | | Fully loaded CAC | $742.91 | $1,230.58 | $1,244.90 | | LTV | $7,662.45 | $7,874.60 | $8,073.23 | | LTV:CAC, marketing only | 14.66x | 8.45x | 7.55x | | LTV:CAC, fully loaded | 10.31x | 6.40x | 6.49x | | Payback, fully loaded (months) | 2.2 | 3.6 | 3.6 |

Two things stand out. The fully loaded ratio is a third lower in every year. And the Year 1 ratio is the least trustworthy number in the table. In year one the sales team is only partly hired and the first customers often come from founder networks at close to zero cost. CAC rises as you exhaust the easy customers, which is exactly what Years 2 and 3 show. If you quote one ratio, quote a mature year, not the first.

A fair question is how much salary to allocate. Include anyone whose main job is acquiring new customers: sales, sales engineering, marketing, and the tools they use. Leave out customer success if it mainly serves existing accounts. Whatever you choose, write it down and apply it the same way every year.

Adjustment 2: cap the lifetime

The ARPU × margin ÷ churn formula assumes churn stays constant forever. At 4.4% monthly churn the implied average lifetime is 1 ÷ 0.044 = 22.7 months, which sounds modest. But the formula also counts the small tail of customers who stay for five and ten years. For a young company with two years of data, nobody knows if those customers exist.

A more conservative approach counts only the gross profit a customer produces in the first 36 months. The Excel formula for a capped LTV is:

= GP_per_month × (1 − (1 − churn)^36) ÷ churn

This is the sum of a geometric series. It adds up expected gross profit month by month, with the chance that the customer is still around shrinking each month, and then stops at month 36.

For Year 3 of the sample company, gross profit per customer is $428.85 × 81.3% = $348.66 a month and churn is 4.3%. The capped LTV is about $6,442 against $8,073 uncapped. Against the fully loaded Year 3 CAC of $1,244.90 the ratio is 5.17x.

So the same company, from the same model, can honestly report anything from 14.66x to 5.17x depending on the choices above. 5x is still a healthy business. The difference is that 5x survives a skeptical investor's own recalculation, and 14.66x does not.

Check the churn number itself

The ratio is more sensitive to churn than to anything else, because churn sits in the denominator of LTV. If Year 3 churn were 6% instead of 4.3%, uncapped LTV would fall to $348.66 ÷ 6% = $5,811 and the fully loaded ratio would drop to 4.67x.

Also translate monthly churn to annual before you present it. 4.4% a month sounds small, but (1 − 0.044)^12 = 58.3%, so the company keeps only about 58% of a starting group of customers after a year. That can be normal for small-business customers and alarming for enterprise ones. Know which you are before someone asks.

Measure churn on customers who have been around at least a few months. A single month in which three of your first twenty customers leave gives you a 15% churn rate that means almost nothing.

Building it in Excel

A clean unit economics tab needs only a few rows per year, all linked to your monthly model:

  1. Sales and marketing spend, including allocated payroll
  2. New customers added
  3. CAC, as row 1 ÷ row 2
  4. Average customers for the period, blended ARPU, and blended gross margin
  5. Monthly churn, as customers lost ÷ average customers, divided by 12 for an annual period
  6. LTV, uncapped and capped, side by side
  7. LTV:CAC and CAC payback for both versions

Wrap each division in IFERROR(..., 0) so a year with no new customers shows zero instead of an error. Keep churn, margin, and ARPU as formulas pulling from the monthly build, never typed in, so the ratio moves when the plan moves.

Checklist

Before an LTV:CAC ratio goes in a deck, confirm that:

The worked example above comes from the sample company in the Numbersmith startup financial model. Its Unit Economics tab computes CAC, LTV, LTV:CAC, and payback by year from a 36-month build. As shipped, its CAC counts the marketing budget only, so add your sales payroll using the steps above.

For education and planning only, not financial advice.