FormulaCraft

How to calculate the SaaS magic number in Excel and Google Sheets

Topic:SaaS metrics
Excel & Google Sheets
=((B3-B2)*4)/C2
✓

Verified example

Computed by a real spreadsheet engine on the sample data below.

QuarterARR ($)S&M Spend ($)
Q11000000150000
Q21240000160000

=((B3-B2)*4)/C2→6.4

Try it with your data

Edit the grid or formula, then run it through a real spreadsheet engine — no signup.

Sample data — click any cell to edit

Runs server-side · free · no signup

Step by step

  1. 1Build a table with columns: Quarter (A), ARR at Quarter End ($) (B), S&M Spend that Quarter ($) (C).
  2. 2Enter two rows of data — the prior quarter (row 2) and the current quarter (row 3).
  3. 3The new ARR generated is the difference between current and prior quarter ARR: (B3−B2).
  4. 4Annualize the quarterly ARR gain by multiplying by 4: (B3−B2)*4.
  5. 5Divide by the prior quarter's S&M spend (C2) to get the magic number: =((B3-B2)*4)/C2.

Tips

Need it for your exact data?

Describe your columns in plain English and get the precise formula for your sheet, with the right Excel or Sheets syntax.

Frequently asked

What counts as S&M spend in the magic number denominator?

Sales salaries (including commission), marketing salaries, advertising, tools, events, and any direct cost attributed to customer acquisition. Use the same definition consistently quarter over quarter.

Can I calculate the magic number on a monthly basis?

Yes — use monthly MRR changes and monthly S&M spend with a one-month lag. Multiply MRR gain by 12 instead of 4 to annualize: ((MRR_current − MRR_prior) × 12) / Prior_Month_SM_Spend.

How does the magic number relate to CAC payback?

They are related inversions: a magic number of 1.0 corresponds to a roughly 12-month CAC payback at 100% gross margin. At 75% gross margin, a magic number of 1.0 implies ~16 months payback.

More on SaaS metrics

See all →
Reference

RANDARRAY

Spills a grid of random numbers with specified rows, columns, min, max, and integer/decimal options. Excel 365 and Google Sheets only.

How-to

How to calculate churn rate

Calculate monthly or annual customer churn rate as lost customers divided by starting customers, expressed as a percentage, in Excel and Google Sheets.

Error fix

Fix #####

Fix ##### in Excel — the column is too narrow to display the number or date. Widen the column or change the format to resolve it.

Reference

ARRAYTOTEXT

Converts an array or range into a single text string, with concise or strict formatting options.

How-to

How to calculate monthly recurring revenue (MRR)

Learn how to calculate MRR in Excel and Google Sheets using SUMIF to sum active subscription revenue by billing period, normalized to a monthly figure.

Error fix

Fix Array result

Array result was not expanded in Google Sheets means output cells are blocked. Clear adjacent cells or use a blank area.

Formulas used

SUMIFROUND / ROUNDUP / ROUNDDOWNAVERAGE

Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.

Last reviewed: