How to calculate MRR (monthly recurring revenue) in Excel

Step 1: One row per subscription, with a monthly amount

Start from a list of subscriptions: customer, plan, how they're billed, the amount on the invoice, a start date and a cancel date that stays blank while they're active. That's usually an export from your billing tool, or a sheet you keep by hand.

MRR counts what a subscription is worth per month, not what was charged this month. Birch Dental paid $1,068 for a year in May. Their MRR is $89 every month of that year, not $1,068 in May and $0 after. Column H does the conversion: annual amounts divided by 12, monthly amounts as they are. Leave out one-off fees like setup or training. They're real revenue, but not recurring.

If you add every annual invoice in the month it was paid, Kite Labs' $468 payment would make October $429 too high, the next 11 months would show nothing for them, and the chart would jump around with your billing calendar. That's cash, which is worth tracking too, just not as MRR.

Step 2: MRR on any date

A subscription counts on a date if it started on or before that date and has either no cancel date or one after it. SUMIFS can't test "blank OR later", so add two SUMIFS: one for the blank cancel dates, one for cancel dates after the date. K3 holds the date you want, October 31 here.

Use the date access ends as the cancel date, not the day the customer clicked cancel. Someone who cancels on the 3rd but has paid through the 31st is still in October's MRR.

Step 3: Upgrades and downgrades as their own rows

When Elm Legal moved from Basic ($19) to Pro ($49) on September 10, the sheet keeps the original row and adds an Upgrade row worth the difference, $30, starting that day. The two rows together make $49, and the history is still there. A downgrade works the same way with a negative amount (type it Downgrade or Contraction).

The catch: when that customer leaves, put the cancel date on every one of their rows. Iris Yoga's Pro row and her Upgrade row both end on October 24, so all $99 leaves MRR together. Miss one and a ghost $50 stays in your numbers.

To count customers instead of dollars, use the same logic with COUNTIFS and add G2:G15,"New" so upgrade rows don't count as extra customers. On October 31 that's 8 active customers.

Step 4: New, expansion and churned MRR for the month

The change in MRR across a month splits into three parts. New MRR is New rows that started in the month. Expansion MRR is Upgrade rows that started in the month: $50 from Acme Studio. Churned MRR is every row whose cancel date falls in the month: Gale Tutors ($19) and Iris Yoga ($49 + $50), so $118.

Change the "New" in this formula to "Upgrade" for expansion, and use =SUMIFS(H2:H15,F2:F15,">="&K2,F2:F15,"<="&K3) for churn. Then check your work: September's $492, plus $58 new, plus $50 expansion, minus $118 churned, is $482. That matches step 2. If it doesn't add up, a row has a missing or mistyped date.

Net new MRR for October is -$10. It's the kind of month that hides inside a single total: sign-ups were fine, and one upgraded customer leaving cost more than the new business brought in.

Step 5: An MRR-by-month table and ARR

List month-end dates in M2:M5 and put the step 2 formula next to each one, pointing at its own date (M2, M3...) with the ranges locked with $. July shows $264, August $382, September $492 and October $482. Add a line chart and you have the MRR trend.

ARR (annual recurring revenue) is MRR x 12 at a point in time. It isn't a forecast of next year's revenue, since it assumes nobody joins or leaves. Keep MRR and ARR on the same sheet, so nobody reads one as the other.