Construction Retention Tracker Spreadsheet
This spreadsheet works out how much retention has been held off your applications, when each half of it falls due back, and how much of it is already late.
Retention comes off the bottom of every application automatically, so nobody ever invoices for it: it never reaches an aged debtor report and nothing about it ever goes overdue. Half is due back at practical completion and half at the end of the defects period, and in between it simply sits in somebody else’s account until you remember to ask for it.
What is in it
- Seven linked tabs, from Start Here through to a one-screen Overview.
- Setup — your usual retention percentage, defects period, release split and payment terms, plus the two figures that put a price on the waiting.
- Jobs — 40 rows, one per contract, each with its own retention percentage and defects period.
- Applications — 400 rows, one per application for payment.
- Retention — both release dates worked out, how much was due back by today, how much arrived, how many days late the rest is, and a verdict in words: still on site, in the defects period, due back, or chase this one first.
- Getting Paid — the same money arranged by which main contractor is holding it, with the days they really take to pay.
Who it is for
Subcontractors working under main contractors where money is held back on every application: groundworks, M&E, joinery, plastering, roofing, fit-out. It is for the trade that has finished the job and is still waiting on the last few per cent of it.
The worked example
The file comes with a position already worked through. $1,128,300 certified gross, $51,515 held back in retention and $19,892.50 given back so far — which leaves $31,622.50 still held by other people: $19,600 of it not yet due, and $12,022.50 due back and not paid.
Priced against the margin, it stops looking like a technicality. Five per cent retention against a six and a half per cent net margin means the retention still held is 43.1% of the $73,339 of profit earned on everything certified. The worst job reached practical completion in September 2023 and its second release was due 639 days ago.
How it works
Compatible with Microsoft Excel 2016 or newer on Windows or Mac, Google Sheets, and LibreOffice. No macros, no add-ons, no account to create, nothing to install.
The download has two workbooks: one with the example above already filled in, so you can see what every column expects, and the same file completely blank for your own figures. A plain-English guide comes as a PDF. Order on WhatsApp — $9
Orders are taken on WhatsApp: message me and I’ll send the payment details, then the files as soon as the payment is confirmed.
A look inside the file
Questions people actually ask
How do I work out when my retention is due back and how much of it is late?
You type the practical completion date and the number of months in the defects period, and the file works out both release dates, how much was due back by today, how much has actually arrived, how many days late the rest is, and what the waiting has cost you at your own borrowing rate. In the worked position, $1,128,300 certified produced $51,515 held, $19,892.50 released and $31,622.50 still held — of which $12,022.50 is due back and unpaid. The worst job's second release was due 639 days ago.
My contracts are not all at 5% retention. Does that break it?
No — retention percentage and defects period are set per job, not globally, so 3% and 5% contracts sit in the same list and every calculation uses that job's own figures. The file holds 40 jobs and 400 applications for payment. It also ranks every main contractor by what they hold, what is overdue, how many days they really take to pay and how many applications went past your terms.
What do I put in if a contract has no defects period?
Put zero months in and both releases fall on practical completion — or put the whole retention into the first release share and the second goes to nothing. The same applies to anything unusual in your contract: the file uses the numbers you give it per job, so you never have to sort contracts by hand.
Do I need a subscription or any add-ons to run it?
No — it is a plain .xlsx workbook with no macros, no add-ons, no account to create and no subscription. It opens in Excel 2016 or newer on Windows and Mac, in Google Sheets through File then Import then Upload, and in LibreOffice. You download the file and it is yours.
Will it tell me my legal rights to the money?
No, and it does not pretend to — it is the arithmetic your own contract already sets out, with today's date put into it. It does not raise applications, valuations or invoices, it is not a variations, dayworks or loss-and-expense claim, and it is not a CIS, VAT or payroll calculator. It connects to no bank or accounting system: nothing syncs and nothing logs in.
Need it built around your business?
This file assumes retention held off applications at a percentage set per job, with a two-stage release around a defects period. If your contracts run differently — a retention bond instead of cash held, retention you hold from your own subcontractors as well as retention held from you, or a main contractor who releases in one payment rather than two — I can build the same arithmetic around your own contract terms.
All other templates are listed on the Excel & Google Sheets Templates page.