What it's for
- Agencies and freelancers selling a set number of hours a month to a client.
- Seeing whether a retainer will last the month, while there is still time to talk to the client.
- A simple rollover rule (carry unused hours, with or without a cap) and overage worked out for you.
What's inside
- Settings
- The client, the first month, the hours a month, whether unused hours roll over, an optional cap, any hours carried in, and an optional overage rate.
- Time log
- Date, hours, person and task. 500 ready rows.
- Weeks
- One month, week by week: hours logged, hours used so far, hours left, where an even pace would be, and the gap.
- Months
- Twelve months: hours available, used, left, overage, carried out to next month and lapsed, with totals.
Preview
| Month | Available | Used | Overage | Carried to next month | Lapsed |
|---|---|---|---|---|---|
| March 2027 | 34 (30 + 4) | 30 | 0 | 4 | 0 |
| April 2027 | 34 (30 + 4) | 38 | 4 | 0 | 0 |
| May 2027 | 30 | 12 | 0 | 10 | 8 |
March starts with 4 hours carried in, so 34 are available. 30 are used, leaving 4, which are under the cap of 10 and carry to April. April uses 38 of its 34, so 4 hours are over and nothing carries. At an example overage rate of 150 an hour, those 4 hours are worth 600. May uses 12 of 30, leaving 18; only 10 carry (the cap) and 8 lapse. On the Weeks sheet, March shows 26 hours left after week 1, 17 after week 2, 10 after week 3 and 4 after week 4.
How to use it
- On Settings, type the client and the first month (any date in it), the hours a month, and whether unused hours roll over. Add a cap in hours if the agreement has one, and any hours carried in from before the first month.
- Log each piece of client work on Time log: the date and the hours. People and tasks are for your own reference.
- On Months, read each month's hours available, used, left and over. Hours left carry to the next month only if you set rollover to Yes, up to the cap.
- On Weeks, type the first day of the month you want to look at (or leave it blank for the first month) to see it week by week against an even pace.
- If you charge overage, type the rate on Settings and Months works out its value. Add a line chart of "Hours left" against "Even pace" on Weeks if you want a picture.
The formulas, explained
- Hours available
-
=allowance + hours carried inThe month's allowance plus what carried out of the month before.
- Hours used in a month
-
=SUMIFS(hours, date, ">="&month start, date, "<"&EDATE(month start,1))Hours logged from the 1st of the month to the end of it.
- Overage
-
=MAX(0, used − available)Hours beyond what was available. Overage does not reduce the next month.
- Carried to next month
-
=IF(rollover="Yes", MIN(cap, MAX(0, available − used)), 0)Unused hours, limited by the cap if you set one. With no cap, all unused hours carry.
- Even pace on Weeks
-
=available × (days in month − days gone) ÷ days in monthWhere the hours left would be if hours were used evenly across the calendar days of the month. Weeks are blocks of seven days from the 1st.
Every formula uses functions that Excel, Google Sheets and LibreOffice all have. There are no macros or scripts.
Questions
Does it work in Google Sheets?
Yes. Upload it with File > Import. It only uses functions both programs have, such as SUMIFS, INDEX, MATCH, EDATE, MIN and MAX.
How does the rollover work?
Unused hours carry into the next month, up to the cap if you set one. It does not expire carried hours after a number of months, so if your agreement says hours last for a set period, check the Months sheet yourself. Hours are not used oldest first, because the file only knows the carried total.
What if the allowance changes part-way through the year?
On Months, type over the Monthly allowance cell for the month it changes. The cells after it still read from Settings, so retype them too if the new figure stays.
How is overage worked out?
Hours used above hours available in the month, with no rollover effect. If you type an overage rate on Settings, the value is the overage hours times that rate. The file does not invoice, and the rate is yours to set.
Is this the same as how Jasper HQ counts retainer hours?
Not exactly. Jasper HQ draws hours from the projects linked to a retainer, uses the oldest hours first, and flags carried hours as expired after a rollover period of 1 to 24 months. This file uses a simpler carry-with-a-cap rule so it works anywhere.
Why is the percentage different from the one in Jasper HQ?
On the Months sheet, the percentage is hours used divided by hours available, which includes the hours carried in. Jasper HQ shows this month's utilisation: hours used this month divided by this month's allocation, with rollover left out. In the worked example, April is 38 of 34 available here (112%), and 38 of 30 allocated in Jasper HQ (127%).
What does a cap of 0 do, and what about hours outside the 12 months?
A cap of 0, or a blank cap, means no cap. To stop unused hours rolling over, set "Unused hours roll over?" to No. Hours dated before the first month or after the twelfth month are ignored, so move the first month on when a year ends.
About this template
Last reviewed 15 November 2026.
Changes: 15 November 2026: first published.
Free to use and change in your own business. Please don't resell the files or publish them as your own. More free templates.