Retainer Revenue Planner
Models monthly recurring revenue from retainer clients, tracks utilization against contracted hours, and flags scope deviations. Provides a 12-month revenue forecast based on current retainer commitments and renewal probabilities.
Freelance - Retainer Revenue Planner.xlsx
Excel (.xlsx) — No macros — Works in Excel, Google Sheets, LibreOffice
What This Spreadsheet Solves
- Unpredictable monthly income despite having retainer clients
- Over-delivering on retainer hours without tracking the gap
- No visibility into which retainers are under-utilized or over-scoped
- Difficulty forecasting revenue when retainer renewals are uncertain
- Scope creep on retainers eroding the effective hourly rate
Who This Is For
- Freelancers with two or more retainer clients
- Consultants transitioning from project-based to recurring revenue
- Virtual assistants managing multiple ongoing engagements
- Freelance CFOs advising clients on revenue stabilization
Inputs
- textClient name
- $Monthly retainer fee
- #Contracted hours per month
- #Actual hours used this month
- %Retainer renewal probability
Outputs
- Total monthly retainer revenue
- Utilization rate per retainer
- Effective hourly rate per retainer
- 12-month revenue forecast (probability-weighted)
- Over/under delivery hours per client
How Calculations Work
The planner sums all retainer fees for guaranteed monthly revenue, then compares actual hours against contracted hours to flag over- or under-delivery. Effective hourly rate is computed as fee divided by actual hours. The 12-month forecast multiplies each retainer fee by its renewal probability and projects forward, showing best-case, expected, and worst-case revenue scenarios.
Example Use Case
Scenario: A marketing freelancer has three retainers: Client A ($4,000/mo, 20 hrs contracted, 25 hrs actual, 90% renewal), Client B ($2,500/mo, 15 hrs contracted, 12 hrs actual, 70% renewal), Client C ($1,500/mo, 10 hrs contracted, 10 hrs actual, 95% renewal).
Result: Monthly revenue: $8,000. Client A is over-delivering by 5 hours ($160 effective vs. $200 contracted rate). 12-month expected forecast: $86,400. Worst-case (all churn risk realized): $69,600.
What You Get — 5 Sheets
Technical Details
Frequently Asked Questions
What counts as over-delivery?
Any hours worked beyond the contracted amount. If a retainer covers 20 hours and you work 25, that is 5 hours of over-delivery reducing your effective rate.
How should I set renewal probability?
Base it on contract length remaining, client satisfaction signals, and historical renewal rates. New clients with no track record: use 60-70%.
Can I track retainers with different billing cycles?
Yes. Normalize everything to monthly in the INPUT sheet. A quarterly retainer of $12,000 becomes $4,000/month.
What if a client consistently under-utilizes their retainer?
This is a positive outcome for your effective rate but a churn risk. Proactively offer additional services to demonstrate value.
Does the forecast account for new client acquisition?
No. It projects based on current retainers only. Add prospective clients manually with a low renewal probability to model pipeline impact.
Download Retainer Revenue Planner
Ready to use immediately. Enter your data in the INPUT sheet, see results in OUTPUT.