What this method gives you
A normalised monthly figure, a movement breakdown that explains why it changed, and a list of customers who need a phone call this week. Not a dashboard — three outputs, produced in fifteen minutes a month once the setup is done.
It is deliberately a spreadsheet method. At this stage a spreadsheet is not a compromise: it is the right tool, because you can see every assumption and change any of them. A tool that computes MRR for you also decides what counts, and those decisions are the interesting part.
What is worth automating is the boring half — getting two years of statements from PDFs into rows. That is where the hours go and where no judgement is involved, which is exactly the right thing to hand off.
Three decisions before you start
Make these now and write them at the top of the sheet. Every one of them will otherwise get decided differently in month four, and the comparison to month one silently stops meaning anything.
Gross or net. If you are VAT registered, MRR should be net — it is a measure of the business, not of what passes through the account. Bank receipts are gross, so this is a subtraction you apply consistently or not at all.
Cash date or service period. This guide uses the service period: an annual payment counts a twelfth in each of the twelve months it covers. Using the cash date is simpler and produces a figure that swings for reasons unrelated to the business.
What counts as a customer. Two subsidiaries paying separately — one customer or two? A reseller with five end clients? There is no correct answer, only a consistent one, and your retention numbers depend on it entirely.
The six steps
1 · Gather
Every account, two years, series checked for gaps.
2 · Group payers
Name variants merged once per customer, not monthly.
3 · Decide what counts
Recurring, one-off, refund, unrelated.
4 · Normalise
Annual and quarterly spread across the months they cover.
5 · Build the grid
Customers down, months across — gaps become visible.
6 · Read it
New, expansion, contraction, churn — before any conclusion.
Step 1 · Gather the data
Two years, every account. The second year is not optional: without it you cannot see annual patterns, and annual customers are usually the largest ones you have.
List the accounts before you list the statements. The main current account is obvious; the euro account used by two overseas customers and the old account you kept open for one legacy client are the ones that get forgotten, and they are disproportionately likely to hold your longest relationships.
Check the series before proceeding.Each statement’s closing balance should be the next one’s opening balance. A missing month in a revenue baseline looks exactly like a month where nobody paid, and it will be read as churn by anyone who did not know the statement was missing. The conversion side is on bank statement to Excel, and Smart Merge turns fifty files into one sheet.
Step 2 · Group the payers
The bulk of the setup work, and the part that determines whether everything afterwards is trustworthy.
The same customer appears with different descriptions — trading name one month, registered name the next, sometimes with an invoice reference appended, sometimes truncated. Each variant is a separate payer until you say otherwise.
Work through them by value, largest first. The top twenty customers usually settle in half an hour and account for most of the revenue; the long tail of small irregular payers matters much less and can be handled roughly without affecting any conclusion.
Record each decision so it holds next month. This is the difference between a half-day setup followed by fifteen-minute months, and a half-day repeated twelve times a year. The mechanics are covered on subscription detection.
Step 3 · Decide what counts
Every receipt falls into one of four buckets, and only the first belongs in MRR.
| Bucket | Examples | In MRR? |
|---|---|---|
| Recurring | Subscription, retainer, membership, rent | Yes |
| One-off | Setup fee, project, training, hardware | No — track separately |
| Negative | Refund, credit, chargeback | Reduces the month it relates to |
| Unrelated | Loan proceeds, grant, own transfer, interest | No |
The last row deserves emphasis. A transfer from your own savings account is not revenue, and it is the single most common way a home-made MRR figure gets accidentally inflated — because it looks like a large receipt from an unfamiliar payer.
The test for the second row is whether you expect it again next period. It is a judgement, made once per transaction type, and it should be recorded so that next month’s comparison is not distorted by a decision nobody remembers making.
Step 4 · Normalise to monthly
The step that turns cash into something comparable, and the one people most often skip because it feels like extra work.
Every recurring payment is converted to a monthly contribution and spread across the months it covers. An annual plan is divided by twelve and appears in each of those twelve months. A quarterly retainer is divided by three. A four-weekly payment is multiplied by thirteen and divided by twelve.
Do this and the cash-date distortion disappears. A month with two annual renewals stops looking like a boom, and the eleven months around it stop looking like a slump. What remains is the shape of the actual customer base.
Skip it and every conclusion is unreliable. This is the difference between recurring cash — which is what the bank shows — and recurring revenue, which is what you are trying to measure, and the whole reason the two are not the same.
Step 5 · Build the payer grid
Customers down the side, months across the top, normalised monthly amounts in the cells. A pivot table, and the single most valuable object in this whole method.
Its power is not in the totals at the bottom. It is that a customer who stopped paying becomes a blank cell, and blank cells in a grid are visible instantly — whereas the absence of a payment in a date-ordered statement is visible to nobody, because nothing appears when something does not happen.
Sort by the current month descending and your largest customers are at the top, which is where a gap matters most. Add a column for the first month each customer appeared and you can read the age of the base at a glance.
Keep the layout identical every month. Consistency is what lets you compare against last month in seconds instead of re-reading the whole sheet, and it is the same discipline described on period comparison.
Step 6 · Read it honestly
A single MRR figure that moved tells you almost nothing. The same increase can mean four completely different things, and only the breakdown distinguishes them.
Compute the four movements every month — new, expansion, contraction, churn — and check that they reconcile: last month plus new plus expansion minus contraction minus churn equals this month. If it does not, something is misclassified, and finding out now is much cheaper than discovering it in a board pack.
Resist the urge to add more metrics. Four movements, a total, and a list of customers who dropped out is a complete picture at this stage. Lifetime value and cohort retention are meaningful once you have years of clean data, and misleading before that.
A worked example
A consultancy with retainer clients, no billing system, invoices from a template. Two years of statements, one afternoon.
| Stage | What happened |
|---|---|
| Raw receipts | Everything that arrived, across three accounts |
| Less unrelated | A loan drawdown and two transfers from savings removed |
| Less one-off | Four project fees and a training day separated out |
| Grouped | Sixty-one payer descriptions collapsed to thirty-four customers |
| Normalised | Three annual retainers spread; one four-weekly client corrected |
| Found: silent churn | Two annual clients had not renewed — unnoticed for months |
| Found: contraction | One client had reduced twice without any conversation |
The last two rows are the point. Neither was visible in a date-ordered statement, and both were months old by the time the grid revealed them. The annual non-renewals are the more expensive kind: nothing appears when an annual payment fails to happen, and eleven months of silence look identical whether the customer is still there or not.
The four movements
| Movement | Definition | What it prompts |
|---|---|---|
| New | A customer paying for the first time | Which channel produced them |
| Expansion | An existing customer paying more | What caused it — repeat it |
| Contraction | An existing customer paying less | A call, while there is still a relationship |
| Churn | A customer who paid before and no longer does | A call, and a reason recorded |
Contraction is the movement that earns the whole exercise. It is the only early warning you get: a customer reducing their spend is telling you something while there is still a relationship to save, and it happens on average several months before a cancellation.
Most home-made trackers never compute it, because it requires comparing amounts rather than counting customers. It is one extra column and it is worth more than the other three combined.
Churn, carefully
Churn is easy to calculate and easy to calculate wrongly, and a wrong churn figure is worse than none because decisions get made on it.
Exclude annual payers from the monthly figure. They are absent for eleven months by design. Including them produces a churn rate of nearly ninety percent, which is obviously wrong — and a subtler version of the same error occurs with quarterly customers, where it is not obvious at all.
Apply a grace period. A customer who pays on the 3rd instead of the 28th has not churned. Two to four weeks is common; the number matters less than applying it identically every month.
Distinguish involuntary churn. A failed card or an expired mandate is not a customer leaving — it is a customer whose payment broke, and it is recoverable if caught early. Treating both the same conceals a problem you could fix; the distinction is covered on failed payment and churn signals.
Report by revenue as well as by count. Losing one large customer and one small one is the same count and a very different month.
The annual plan problem
Annual customers break almost every simple approach, and they deserve their own treatment because they are usually the most valuable ones.
They distort cash. One large payment in one month. Normalisation handles this, which is why step four is not optional.
They hide churn. An annual customer who does not renew produces no signal at all — there was never a monthly payment whose absence you could notice. They simply are not there next March, and eleven months of silence look exactly the same as a healthy relationship.
The fix is a renewal list: every annual customer with the month they are due. Check it monthly against actual receipts. It takes two minutes and it is the only mechanism that catches a non-renewal within weeks rather than a year.
They complicate contraction. A customer who renews at a lower amount contracted twelve months ago in cash terms but only once in the grid. Note the renewal amount against the previous one; the change is real even though it appears once.
Cadence and discipline
Monthly, in the first week, on the same working day. The consistency matters more than the speed — a figure produced on the fourth working day every month becomes part of how the business is run.
Fifteen minutes is the realistic steady state: convert the month, assign any new payers, check the grid for blanks, compute the movements. If it is taking an hour, the payer grouping decisions are not being recorded.
Keep a change log at the bottom of the sheet. When you reclassify a customer or change the grace period, write the date and the reason. Six months later, an unexplained step in the history is indistinguishable from a real business event.
Never restate history to make a trend look better. If a definition changes, restate consistently and say so — a number that moves because the definition moved is worse than no number, because someone will act on it.
Four sanity checks
Before the number leaves the sheet, four checks catch nearly everything that goes wrong. Each takes under a minute and each has caught a real error often enough to be worth the habit.
Does the movement equation close? Last month plus new plus expansion minus contraction minus churn should equal this month exactly. When it does not, a customer has been classified two ways at once — usually a price change recorded as both a churn and a new sale.
Does annualised MRR resemble the year’s receipts? Twelve times MRR should land in the same neighbourhood as recurring cash actually collected. A large gap means either normalisation is wrong or one-off income has crept in.
Is the customer count plausible? Count the rows with a value this month and compare it to what you believe. Founders are usually within a couple; being out by ten means payer grouping has split someone or merged two.
Is any single customer dominating? Not an error, but worth knowing before someone else points it out. If one customer is a large share of MRR, that is the first thing a reader will notice and you should be the one to say it.
Run all four every month and record the results next to the figure. It takes four minutes and it is the difference between a number you present confidently and one you hope nobody examines.
Why the number is worth having at all
It is fair to ask whether a small business needs a formal recurring revenue figure. Plenty operate for years without one and are perfectly healthy.
The argument is not that the number itself is magic. It is that computing it forces four questions you would otherwise never ask: which customers actually pay us regularly, which of them changed, which of them stopped, and how much of what we call revenue will still be here next month. Most people running a business believe they know all four and are wrong about at least one.
The second argument is timing. The value of noticing a lost customer decays sharply — within weeks it is a conversation, within months it is a write-off. A monthly routine converts a discovery you would have made eventually into one you make while it is still actionable.
And the third is that the discipline compounds. A year of consistent measurement is worth vastly more than three years of reconstruction, because only the first can show you a trend you can trust.
When to graduate to a real system
This method is honest about its own ceiling. Four signs say you have reached it.
Mid-month changes. Once customers upgrade partway through a period and proration appears, the spreadsheet stops approximating and starts being wrong.
Usage components. A base fee plus variable usage cannot be normalised from a payment total, because the split is not in the bank data.
Multiple currencies. Exchange movement changes the figure without anything happening in the business, and separating the two by hand is genuinely difficult.
Volume. Past a few hundred customers the grouping work stops being fifteen minutes, and the error rate rises faster than the time cost.
Until then, the spreadsheet is not a stopgap. It is a method whose assumptions you can see, which is more than can be said for most dashboards.
Common mistakes
Counting cash on the date it arrived
One month with two annual renewals looks like a boom, and the eleven around it look like decline.
Including one-off fees
MRR is a statement about what recurs; a setup fee is a promise about nothing.
Counting own transfers as revenue
A movement from savings looks like a large receipt from an unfamiliar payer — the most common accidental inflation.
Including annual payers in monthly churn
Produces a churn rate near ninety percent, and a subtler wrong number for quarterly payers.
Treating a price rise as churn plus new
Flatters both new business and retention at once, and misstates the base.
Never computing contraction
Discards the only early warning available, months before a cancellation.
Changing definitions without a note
A step in the history becomes indistinguishable from a real event.
Adding metrics before the basics reconcile
Lifetime value on unreliable inputs is a confident wrong answer.
Best practices
Write the definitions on the sheet
Gross or net, cash date or service period, what counts as one customer. Three lines that keep month twelve comparable to month one.
Record payer groupings once
The difference between a half-day setup and a half-day repeated monthly.
Keep a renewal list
Every annual customer with their due month, checked monthly. The only way a non-renewal is caught in weeks.
Reconcile the movements
Last month plus new plus expansion minus contraction minus churn should equal this month. When it does not, something is misclassified.
Report count and revenue
Losing one large and one small customer is the same count and a very different month.
Log every definition change
With the date and the reason, at the bottom of the sheet.
For the founder’s view of the same numbers — runway, burn and what investors ask for — see revenue tracking for SaaS founders, and for the outgoing side of recurring payments, the subscriptions you forgot you were paying for.
One closing note on tone, because it affects whether the habit survives. The first month of doing this usually produces a figure lower than the one you had in your head, and the instinct is to look for the definition that restores it. Resist that, and not on principle — a number chosen to be comfortable stops being useful for exactly the decisions it exists to support, and you will know it. The conservative version is the one you can quote without hedging, defend without preparation, and compare against next month without wondering whether the two were built the same way. That is the entire value, and it survives only if the definitions are set before the numbers are seen.