How to Calculate ARR: Annual Recurring Revenue Formula

How to calculate ARR from monthly and annual plans with one Excel SUMPRODUCT formula, what to exclude, and how ARR differs from revenue and bookings.

Summary

ARR = annualised value of active recurring subscriptions: monthly plans × 12 plus annual plans at their yearly price. One-time fees, services and usage overages are excluded. A SaaS company with 685 active subscriptions across monthly and annual plans has an ARR of $643,500.

On this page
  1. Annual recurring revenue (ARR) formula
  2. How to calculate annual recurring revenue (ARR) in Excel and Google Sheets
  3. Worked example
  4. How to interpret annual recurring revenue (ARR)
  5. Common mistakes
  6. Calculate annual recurring revenue (ARR) with Better Analyst
  7. Further reading
  8. FAQ

Annual recurring revenue (ARR) formula

Formula
ARR = Σ (Monthly subscription price × 12) + Σ (Annual subscription price), for active subscriptions only
  • Active subscriptions: Customers with a current, paying recurring contract on the measurement date. Exclude trials and cancelled-but-not-yet-expired accounts if you report committed ARR.
  • Recurring price: Contracted recurring price after discounts. Exclude one-time onboarding, implementation and services fees.

How to calculate annual recurring revenue (ARR) in Excel and Google Sheets

Excel, using the cells in the worked example below:

Excel
=SUMPRODUCT(C2:C5,D2:D5,(B2:B5="Monthly")*11+1)

Worked example

A B2B scheduling-software company prices two plans with monthly or annual billing and also charges a one-time onboarding fee. The founder needs ARR for an investor update.

Active subscriptions on Oct 1 (table starts in cell A1)
PlanBillingCustomersPrice per billing period
StarterMonthly410$49
StarterAnnual120$468
GrowthMonthly95$199
GrowthAnnual60$1,990
Onboarding feeOne-time30$1,500
  1. Monthly plans: 410 × 49 × 12 = $241,080 and 95 × 199 × 12 = $226,860.
  2. Annual plans: 120 × 468 = $56,160 and 60 × 1,990 = $119,400.
  3. ARR = 241,080 + 56,160 + 226,860 + 119,400 = $643,500. The $45,000 of onboarding fees in row 6 is left out because it doesn't recur.
  4. The Excel formula multiplies customers × price × 12 when Billing is "Monthly" and × 1 otherwise, over rows 2–5 only.
  5. MRR is ARR ÷ 12 = $53,625.

Result: $643,500 ARR (MRR $53,625)

How to interpret annual recurring revenue (ARR)

ARR is the headline size metric for subscription businesses because it ignores billing timing: an annual customer paying $1,990 upfront and a monthly customer paying $166 every month both count about the same. It is not GAAP revenue and not cash. Track how it changes through new, expansion, contraction and churned ARR (an ARR bridge) rather than just the total; two companies with the same ARR growth can have very different retention underneath.

Common mistakes

  • Including one-time fees, professional services or hardware. They inflate ARR and won't repeat next year.
  • Annualising usage-based overages from a single strong month. Report committed ARR and usage separately, or use a trailing average.
  • Counting signed contracts that haven't started. Most teams report those as contracted or 'committed' ARR, separate from live ARR.
  • Ignoring discounts. Use the contracted price after discounts, not list price.

Calculate annual recurring revenue (ARR) with Better Analyst

Connect your data or upload a spreadsheet, then ask in plain English. Better Analyst writes and runs the calculation and shows the steps, so you can check the number.

  • “Calculate ARR and MRR on the first of each month from our subscriptions table.”
  • “Build an ARR bridge for this quarter: new, expansion, contraction and churned ARR.”
  • “What share of ARR comes from annual plans versus monthly plans?”

Usually calculated from: PostgreSQL, Google BigQuery, Snowflake, Supabase, MySQL, Google Sheets.

Try Better Analyst free

Further reading

Frequently asked questions

What is the difference between ARR and MRR?
They measure the same recurring base on different time scales: ARR = MRR × 12. Monthly-billed businesses usually report MRR; companies with annual contracts usually report ARR.
Is ARR the same as annual revenue?
No. Revenue is what you recognised in a past period, including one-time fees. ARR is a forward-looking snapshot of recurring contracts active today.
How do I calculate ARR in Google Sheets?
The same SUMPRODUCT formula works in Sheets: =SUMPRODUCT(C2:C5,D2:D5,(B2:B5="Monthly")*11+1). The comparison returns TRUE/FALSE, which the arithmetic turns into 12 for monthly rows and 1 for annual rows.