Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to calculate monthly recurring revenue (MRR) in Excel and Google Sheets
Topic:SaaS metricsExcel & Google Sheets
=SUMIF(C2:C6,"Monthly",B2:B6)+SUMIF(C2:C6,"Annual",B2:B6)/12✓
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)+SUMIF(C2:C6,"Annual",B2:B6)/12→2500
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
- 1Set up a table with columns: Customer (A), Contract Value (B), Billing Cycle (C). Enter one row per active subscription.
- 2For monthly subscribers, the monthly value equals the contract value. For annual subscribers, divide the annual value by 12.
- 3Use SUMIF to total monthly contracts: =SUMIF(C2:C6,"Monthly",B2:B6).
- 4Add a second SUMIF for annual contracts divided by 12: +SUMIF(C2:C6,"Annual",B2:B6)/12.
- 5Combine both terms in one cell to get total MRR. Add additional SUMIF terms for quarterly (÷3) or semi-annual (÷6) billing as needed.
Tips
- Exclude one-time fees, professional services revenue, and setup fees — MRR covers only recurring subscription charges.
- Track MRR movement (new MRR, expansion MRR, churned MRR, contraction MRR) in separate columns so you can reconcile month-over-month changes.
- If all customers are billed monthly, a plain =SUM(B2:B6) is sufficient — the SUMIF approach is only needed for mixed billing cycles.
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
Should I include trials in MRR?
No. Free trials produce no committed revenue. Include a customer only when they convert to a paid plan.
How do I handle metered or usage-based billing?
Use the average recognized revenue over the last 3 months for each metered customer and include that average in the MRR figure.
Is MRR the same as monthly revenue on the income statement?
No. MRR is a forward-looking metric representing contracted recurring revenue. GAAP revenue recognizes revenue as it is earned, which may differ due to timing, refunds, and non-recurring items.
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 annual recurring revenue (ARR)
Calculate ARR in Excel and Google Sheets by summing normalized annual subscription revenue across all active accounts, converting monthly contracts to their annual equivalent.
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.