Excel Tutorial Series: How To Calculate Monthly Recurring Revenue (MRR)

Analyzing your revenue by month is crucial for a SaaS business. It helps you track growth, identify trends, forecast future earnings, and make informed business decisions. While many sophisticated tools such as KineticLoom exist, getting started with a basic calculation in Excel (or Google Sheets) is fast, effective, and accessible.

Here is a straightforward, step-by-step guide to calculating and visualizing your monthly recurring revenue (MRR) and total revenue in Excel.

1. Prepare Your Raw Data

Before you can calculate anything, you need a clean dataset. Your raw data should contain at least these two key columns:

• Date: The date the subscription started or the payment was received.
• Amount: The revenue generated from that transaction (this could be MRR, or total transaction value).

Tip: Ensure your “Date” column is formatted as a date in Excel.

2. Extract the Month and Year

You can’t group by date alone; you need a column that specifies the month and year for grouping.

In a new column (let’s call it “Year-Month”), use the following formula (assuming your first date is in cell A2):

=TEXT(A2, "yyyy-mm")

Why TEXT? This formula converts the date into a text string that looks like “2025-01”, which is perfect for sorting and grouping because it includes the year and maintains chronological order.

3. Calculate Monthly Revenue with SUMIFS

Now you can sum your revenue based on the Year-Month criteria. The most efficient way to do this is using the SUMIFS function.

A. Create a Summary Table

In a new area of your spreadsheet (or a new sheet), create a list of all the unique Year-Month values you want to analyze.

Assuming:

• “Year-Month” data is located in D:D

Input the following in the new area to create the list in the “Unique Year-Month” column.

=UNIQUE(D:D)

B. Apply the SUMIFS Formula

Assuming:

• Your raw data Year-Month column is D:D.
• Your raw data Subscription Amount column is C:C.
• The first cell in your Summary Table’s Year-Month column is G2 (e.g., “2025-01”).

In cell H2 (the first cell under “Total Revenue”), enter:

=SUMIFS(C:C, D:D, G2)

Where:

• C:C is the sum_range (what you want to add up).
• D:D is the criteria_range (where you look for the month).
• G2 is the criteria (the specific Year-Month you are looking for).

Drag this formula down for all months. This instantly calculates the total revenue for each month.

4. Visualize Your Growth

A list of numbers is useful, but a chart is helpful for spotting trends at a glance.

1. Select the entire Summary Table (both the “Year-Month” and “Total Revenue” columns).
2. Go to the Insert tab.
3. Choose a Line Chart or a Column Chart. The line chart is excellent for showing continuous growth over time.

This chart immediately highlights your growth trajectory, revealing which months performed best and showing your overall momentum.

5. SaaS-Specific Analysis: Beyond Basic Revenue

For your SaaS business, use this monthly revenue data to calculate and track key metrics:

• Monthly Recurring Revenue (MRR): If your “Subscription Amount” column only contains recurring charges, your monthly revenue calculation is your MRR. This is your primary health metric.
• Churn Rate: Compare the revenue lost from cancellations/downgrades each month to your starting MRR.
• Net Revenue Retention (NRR): Track how the revenue from your current customer base grows or shrinks each month due to upgrades, downgrades, and churn.

And to take your analysis up another level - try segmenting your data. This is a powerful technique that can help you uncover real actionable insights previously hidden within your aggregates. For example:

• Which products of ours are growing or churning?
• Are there distribution channels where the ratio between Customer Lifetime Value (CLV) and Cost of Acquistion (CAC) is unsustainable?

So, fire up Excel and start uncovering the hidden stories within your customer data today. Your future self (and your bottom line) will thank you.

Want help with the heavy lifting calculating your metrics? Try out KineticLoom!