How to Build an MRR Guide in Google Sheets & Excel

If you run a subscription business, your Monthly Recurring Revenue is the heartbeat of your company. Let’s walk through practical ways to build an MRR calculator—from fully manual to fully automated with an AI agent—so you can stop scrambling at month-end.

‍

1. Manual MRR workflows in Google Sheets and Excel

‍

Method 1: Simple single-plan MRR in Google Sheets

1. Open a new Sheet.
2. In row 1, add headers: Month, Active Customers, ARPA, MRR.
3. In ARPA (Average Revenue Per Account), enter your subscription price (e.g., $49).
4. In Active Customers, enter the customer count for each month.
5. In the first MRR cell, use the formula:
   • =B2*C2 (assuming B is customers, C is ARPA).
6. Drag the formula down for future months.
7. Insert a line chart for MRR to see growth over time.

Docs: Google Sheets formulas guide — https://support.google.com/docs/answer/3093480

‍

Method 2: Multi-plan MRR with SUMPRODUCT in Google Sheets

1. Create headers: Plan, Price, Customers.
2. List each plan on its own row.
3. In a Total MRR cell, use:
   • =SUMPRODUCT(B2:B5, C2:C5) (B = prices, C = customers).
4. To track churn, add a second table with Churned Customers by plan and compute Churned MRR using another SUMPRODUCT.
5. Build a summary section showing New MRR, Churned MRR, Net New MRR, Total MRR.

‍

Method 3: Excel table-based MRR model

1. In Excel, paste your subscription data (customer, plan, price, start date, cancel date) into a sheet.
2. Select the range and insert a Table (Insert > Table).
3. Add a Status column with a formula to check if the customer was active in a given month (e.g., using IF with start/cancel dates).
4. Use a PivotTable to sum Price for active customers by month. This gives you MRR by month directly from transactional data.
5. Format the MRR column with currency and plot a chart.

Docs: Excel formulas overview — https://support.microsoft.com/en-us/office/create-a-formula-in-excel-ecfdc708-9162-49e8-b993-c311f47ca173

‍

Pros of manual methods

• Full control and visibility over every assumption.
• Great for early-stage SaaS or agencies with a handful of clients.
• Easy to tweak formulas during experimentation.

‍

Cons of manual methods

• Error-prone: one bad reference can break your MRR.
• Time-consuming every month to update by hand.
• Doesn’t scale when you have multiple tools, currencies, or segments.

‍

2. No-code MRR automation with Sheets and Excel

‍

Method 4: Forms or CRM into Google Sheets

1. Use a form tool (or your CRM export) to capture new subscriptions and cancellations.
2. Connect it to Google Sheets with a no-code tool (e.g., via a form’s "Send responses to Sheets" feature or an automation platform).
3. Map fields like Customer ID, Plan, MRR, Status, Created At, Cancelled At to columns.
4. On a separate tab, use formulas (SUMIFS, FILTER) to compute:
   • New MRR (customers created this month).
   • Churned MRR (customers cancelled this month).
   • Expansion MRR (upgrades).
5. Reference these in a summary dashboard that updates whenever new rows arrive.

Docs: Connect forms to Sheets — https://support.google.com/docs/answer/2917686

‍

Method 5: Excel + data connectors

1. In Excel, enable Get & Transform Data (Data > Get Data).
2. Connect to CSV exports from your billing system stored in OneDrive/SharePoint.
3. Define steps in Power Query to clean columns (rename, filter, change types).
4. Load the result into a table that powers your MRR PivotTable.
5. Click Refresh All each month (or schedule refresh if using Excel with Power BI / online).

Docs: Excel data import basics — https://support.microsoft.com/en-us/office/import-and-analyze-data-66bf7ee6-2eaf-4d10-9d52-5b53b83bb8a6

‍

Pros of no-code automation

• Reduces manual copying and pasting.
• Keeps your MRR model in familiar tools (Sheets/Excel).
• Fast to set up for non-technical founders and marketers.

‍

Cons of no-code automation

• Still fragile if schemas change (new columns, renamed fields).
• Harder to orchestrate multi-step workflows across many apps.
• Someone must maintain the automations as tools evolve.

‍

3. Scaling MRR calculations with an AI agent

Now imagine an AI agent that uses your computer like a smart assistant—opening the browser, logging into billing tools, exporting reports, and updating Sheets or Excel for you.

‍

Method 6: AI agent-driven monthly MRR close

Story: Each month, instead of your ops lead doing the “MRR dance,” your Simular AI agent does it.

‍

Step-by-step:

1. You define a playbook: which billing dashboard to open, what date range to select, which CSV to export.
2. The agent opens the browser, navigates to Stripe (or your gateway), downloads the report, and saves it.
3. It opens Google Sheets, finds your MRR workbook, and imports the latest CSV into a raw data tab.
4. It triggers your existing formulas and pivot tables to recompute MRR, churn, and expansion.
5. Finally, it exports a PDF snapshot or posts a link into your team’s Slack/Email.

‍

Pros:

• Zero manual clicks after initial setup.
• Works across desktop, browser, and cloud apps.
• Transparent execution—you can inspect every step.

‍

Cons:

• Requires a clear, well-documented workflow the agent can follow.
• First-time setup takes more thought than a quick spreadsheet hack.

‍

Method 7: Daily MRR monitoring across Sheets and Excel

1. Configure the Simular AI agent to run daily.
2. The agent checks multiple tools (billing system, CRM, subscription management) and reconciles active customers.
3. For marketing and sales, it updates a Google Sheets dashboard by segment (SMB, mid-market, enterprise).
4. For finance, it updates an Excel workbook with more detailed breakdowns and scenario tabs.
5. The agent highlights anomalies—like a sudden spike in churn—or comments directly inside Sheets/Excel with findings.

‍

Pros:

• Near real-time revenue visibility without adding headcount.
• Tailored views for different teams, all powered by one automated workflow.

‍

Cons:

• Needs good access management since the agent touches financial and customer data.
• You must review alerts and decide which to act on—this is still your judgment call.

‍

Method 8: Scenario planning with AI support

1. Keep your core MRR logic in Excel or Google Sheets.
2. Ask the AI agent to duplicate the model, adjust inputs (pricing, churn rates, upgrades), and generate multiple scenarios.
3. It exports charts and a short narrative: “If we increase ARPA 10% and reduce churn 1 point, ARR grows by X%.”

‍

Pros:

• Turns complex what-if analysis into a repeatable workflow.
• Great for board prep or campaign ROI planning.

‍

Cons:

• Still depends on the quality of your underlying MRR model.

When you combine solid spreadsheet models with an AI agent that can click, type, and calculate on your behalf, MRR moves from a painful monthly ritual to a continuous, reliable signal for every decision you make.