If you bill clients monthly, revenue looks stable right up until the week it isn’t. A retainer book hides its own risk: the money arrives on schedule, so nothing forces you to look closely until a client sends a two-line email asking to pause for a quarter. You can forecast retainer revenue accurately enough to see that email coming weeks in advance, and you can build the whole thing in a blank spreadsheet this afternoon.
This is an operational walkthrough. Steps, thresholds, one worked example, and an honest note about where a spreadsheet runs out of road.
Why a Retainer Book Breaks Without Warning
Most retainer forecasts are a single number: this month’s recurring revenue, summed. That number describes the past. It tells you nothing about which of those dollars will still be there in ninety days.
Three things usually cause the surprise.
- Silent expiry. A twelve-month agreement has a notice window — often thirty or sixty days before the term ends. Nobody tracks the date, the window opens, the client uses it. You find out when the notice arrives.
- Usage drift. A client on a 40-hour retainer who uses 14 hours a month is not a happy client. They are a client building a case that they are overpaying. The opposite drift is just as dangerous: a client consistently pulling 55 hours out of a 40-hour package is profitable for them and quietly unprofitable for you, and the renewal conversation will be about price.
- Relationship decay. Your champion moves to another company. The new contact never signed the agreement, never sat in the kickoff, and has their own preferred vendors.
None of those are invisible. They just aren’t in your accounting software, because accounting software records what happened, not what is about to.
The Six Columns You Need to Forecast Retainer Revenue
Open a blank sheet. One row per client. You need six columns before you need anything clever.
- Contract type and end date. Month-to-month, or a fixed term with a specific end date. Write the date, not „annual.“
- Notice date. The end date minus the notice period in the agreement. This is the column that saves you. If notice is 60 days and the term ends March 31, your real deadline is January 30.
- Monthly fee and last price change. The fee, plus the month you last changed it. If that cell is blank or more than 18 months old, you have found your next pricing conversation.
- Included usage vs. actual. Whatever the unit is — hours, deliverables, posts, tickets. Two columns: what they bought, what they used last month. Then a simple ratio.
- Churn risk score. A number from 0 to 7, built from the signals below.
- Expansion note. One sentence about the work you know they need and aren’t buying yet. Dated.
Add a seventh calculated column: weighted revenue. That is the monthly fee multiplied by a retention probability you assign from the risk band. This is the number you should plan around.
Score Churn Risk With Signals, Not Gut Feel
Gut feel is systematically optimistic about clients you like and pessimistic about clients who email a lot. Use binary signals instead. Score one point for each that is true this month:
- Usage below 60% of included scope for two consecutive months.
- Usage above 100% of included scope for two consecutive months, unbilled.
- Your primary contact changed in the last 90 days.
- No scheduled call or meeting in the last 30 days.
- The most recent invoice was paid more than 10 days late.
- No new scope, project or idea discussed in 60 days.
- A known budget, funding or leadership change on the client side.
Then band it: 0–1 is green, 2–3 is amber, 4 or more is red.
Assign a retention assumption to each band and write it at the top of the sheet so it is visible — for example 95% green, 70% amber, 40% red. Those starting figures are yours to calibrate. After two or three renewal cycles, compare what your bands predicted against what actually renewed and adjust. A forecast you correct is worth more than a forecast you got right by accident.
A Worked Example You Can Copy
Say you run five retainers totaling $17,800 a month. Contracted revenue says $17,800. Here is what the scored sheet says.
- Bellwether Legal — $2,500, term ends Jan 31, risk score 1, green.
- Orchard Foods — $3,000, term ends Aug 31, risk score 0, green.
- Vance Partners — $1,800, term ends Jun 30, risk score 1, green.
- Northgate Dental — $4,000, term ends Mar 31, risk score 3, amber. They used 41 of 40 included hours twice running and you absorbed it.
- Larkspur Software — $6,500, month-to-month, risk score 5, red. New marketing director in month two, 22 of 35 hours used, last invoice nine days late, no roadmap conversation since spring.
Weighted: greens $7,300 × 0.95 = $6,935. Amber $4,000 × 0.70 = $2,800. Red $6,500 × 0.40 = $2,600. Your planning number is $12,335, not $17,800.
That $5,465 gap is the whole point. It tells you your largest client is also your least secure one, and that a single logo carries more than a third of the exposure. That is a pipeline decision, a pricing decision and a staffing decision — and you made it in October instead of finding out in December. If you want the upstream half of that problem, replacement pipeline, there are other operational walkthroughs on the Cursiqa blog covering how to keep new business moving while you defend the book you have.
The 90/60/30 Renewal Rule
Work backward from the notice date, not the end date.
- 90 days out: review the usage ratio and risk score internally. Decide three things — renew as-is, renew at a new price, or let it go. Write the decision down.
- 60 days out: have the conversation. Bring a one-page summary of what was delivered over the term. If a price change is coming, this is where it is said, in writing, attached to scope. Never introduce a price change inside the notice window.
- 30 days out: paperwork signed, or the client is formally in your churn forecast and the replacement work starts now.
Two standing rules make this hold up. First, any client at red for two consecutive months gets a direct conversation that month, regardless of how far the renewal is. Second, any client whose usage exceeds 110% of included scope for three months gets a scope reset — either they buy the larger tier or the overage gets billed. Quietly eating overage does not buy loyalty; it buys a renewal negotiation you will lose.
Where a Spreadsheet Stops Being Enough
Be honest about the ceiling. A manual sheet works well up to roughly 20 to 30 active retainers maintained by one person on a fixed weekly rhythm. Past that, three things break: the usage numbers go stale because nobody wants to re-key them from the time tracker, two people edit the same file and the version splits, and multi-year or multi-currency terms make the weighted math harder to audit than to run.
At that point you want real integrations — time tracking feeding usage automatically, your billing system feeding payment dates. What does not change is the model. Every subscription analytics tool sells you the same six columns with a chart on top. Build the discipline first in a sheet you understand; buy the automation later, when the cost is data entry rather than thinking. And treat all of this as operational planning, not accounting or tax guidance — your bookkeeper owns the books, this owns the decisions.
If you would rather start from a structure than a blank grid, the Retainer Revenue & Renewal Planner sets up the renewal calendar, usage tracking and risk scoring as a working file, and the rest of the Cursiqa shop covers the neighboring problems — pricing, pipeline and cash flow. Either way, build the sheet this week. The renewal you can see is the one you can still do something about.