Monthly Retainer Forecast
Forecasts monthly recurring revenue from retainer clients over a 12-month horizon. Incorporates growth rates, churn probability, and renewal timing to produce expected, optimistic, and pessimistic revenue scenarios.
Agency - Monthly Retainer Forecast.xlsx
Excel (.xlsx) — No macros — Works in Excel, Google Sheets, LibreOffice
What This Spreadsheet Solves
- No forward visibility into recurring revenue beyond the current month
- Inability to model the impact of losing one or two key retainer clients
- Difficulty planning hiring or investments without reliable revenue forecasts
- Churn risk not quantified or tracked systematically
- Growth assumptions not grounded in per-client trend data
Who This Is For
- Agency founders planning quarterly and annual budgets
- Revenue operations managers building financial models
- Account managers tracking renewal pipelines
- Agency CFOs reporting to partners or investors
Inputs
- textClient name
- $Current monthly retainer value
- %Monthly growth rate
- %Churn probability (next 12 months)
- dateContract renewal date
Outputs
- Total monthly recurring revenue (current)
- 12-month expected revenue forecast
- Optimistic scenario (low churn, high growth)
- Pessimistic scenario (high churn, no growth)
- Revenue at risk from top 3 churn candidates
How Calculations Work
Each retainer is projected forward month by month, applying the growth rate compounding and discounting by churn probability starting at the renewal date. The expected scenario uses stated probabilities. Optimistic halves churn and doubles growth. Pessimistic doubles churn and zeroes growth. Revenue at risk is the sum of retainer values for clients with churn probability above the threshold.
Example Use Case
Scenario: An agency has 8 retainer clients totaling $120,000/month. Average growth rate is 2%/month. Average churn probability is 15%. Two clients with $35,000 combined have contracts renewing in 3 months.
Result: Expected 12-month total: $1,524,000. Optimistic: $1,620,000. Pessimistic: $1,296,000. Revenue at risk in month 3: $35,000 (29% of monthly total).
What You Get — 5 Sheets
Technical Details
Frequently Asked Questions
How do I estimate churn probability?
Use historical data if available. Otherwise, estimate based on contract length, client satisfaction scores, and competitive risk. New clients without history: use 20-30%.
Does the forecast account for new business?
No. It models existing retainers only. Add pipeline clients with high churn probability to see their potential impact.
What is the growth rate based on?
Historical month-over-month change in retainer value per client. Include scope expansions and rate increases.
How should I handle quarterly retainers?
Convert to monthly values in the INPUT sheet. A $30,000 quarterly retainer becomes $10,000/month.
Can I model a specific client leaving?
Yes. Set that client's churn probability to 100% to see the impact on total revenue.
Download Monthly Retainer Forecast
Ready to use immediately. Enter your data in the INPUT sheet, see results in OUTPUT.