Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to calculate annual recurring revenue (ARR) in Excel and Google Sheets
Topic:SaaS metricsExcel & Google Sheets
=SUMIF(C2:C6,"Monthly",B2:B6)*12+SUMIF(C2:C6,"Annual",B2:B6)✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| Customer | Contract Value | Billing Cycle |
| Acme Corp | 500 | Monthly |
| Beta LLC | 12000 | Annual |
| Gamma Inc | 300 | Monthly |
| Delta Co | 6000 | Annual |
| Epsilon Ltd | 200 | Monthly |
=SUMIF(C2:C6,"Monthly",B2:B6)*12+SUMIF(C2:C6,"Annual",B2:B6)→30000
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
- 1Build a table with columns: Customer (A), Contract Value (B), Billing Cycle (C — either 'Monthly' or 'Annual').
- 2For annual subscribers, the ARR contribution equals the contract value directly.
- 3For monthly subscribers, multiply the monthly value by 12 to annualize it.
- 4Use =SUMIF(C2:C6,"Monthly",B2:B6)*12 to total annualized monthly contracts.
- 5Add =SUMIF(C2:C6,"Annual",B2:B6) for annual contracts. Combine both in one formula for total ARR.
Tips
- ARR = MRR × 12 is the fastest shortcut once you already have MRR calculated; reference that cell rather than recalculating from scratch.
- Do not include non-recurring revenue (implementation fees, one-time add-ons) in ARR — it inflates the metric and misleads forecasts.
- Segment ARR by plan tier, cohort, or geography in additional columns so you can spot growth drivers without losing the top-line number.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
What is the difference between ARR and ACV?
ARR is total annual recurring revenue across all customers. ACV (Annual Contract Value) is the annualized value of a single contract, often used in sales reporting.
Should ARR include multi-year contracts at full value?
No. For a 3-year $36,000 contract, ARR is $12,000/year, not $36,000. ARR normalizes to the annual run rate, not the total contract value (TCV).
How do I track ARR growth month over month?
Calculate ARR at the start and end of each month, then compute (End ARR − Start ARR) / Start ARR to get the growth rate. A waterfall chart of new, expansion, contraction, and churn ARR shows the composition.
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-toHow 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 fixFix #####
Fix ##### in Excel — the column is too narrow to display the number or date. Widen the column or change the format to resolve it.
ReferenceARRAYTOTEXT
Converts an array or range into a single text string, with concise or strict formatting options.
How-toHow 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 fixFix Array result
Array result was not expanded in Google Sheets means output cells are blocked. Clear adjacent cells or use a blank area.