If you bill from Excel today, the worry about switching is the jobs in the middle. Six contracts half billed, each with an advance part recovered and retention held. Nobody wants to type two years of claims into a new system, and nobody should have to.
My view is simple: a contract needs its position on the day you switch, not its history. The history stays where it is. What moves is a handful of totals, checked once against the last certificate the main contractor signed.
The figures that carry a contract across
Take these from the last signed certificate and the old advance tax invoice, as they stand on the day you switch:
- Work billed to date, before VAT and before the advance and retention came off.
- How many claims have been issued, so the next one is numbered correctly.
- The advance received, the VAT declared on it, and the old advance tax invoice's number.
- The advance already recovered on the old claims, before VAT.
- The retention held, as the old claims withheld it: without VAT if they took deductions before VAT, with its VAT if after.
- Which of the two methods the old claims used.
The old claims' VAT stays where it is, in the returns for the periods they were issued in. Nothing about it is declared again. The open balances (the advance still to recover and the retention held) come into your new books through the opening trial balance, like any other balance on the switch date.
Most of these figures are already on the last certificate, in its "cumulative to date" column: the work certified, the advance recovered, the retention held. The advance received and its VAT come from the advance tax invoice. If your sheet and the certificate disagree, use the certificate, because that is what the main contractor will check your next claim against.
Pick the switch date with your accountant. The start of a VAT period is the cleanest, because then one return holds only old documents and the next holds only new ones.
The reference contract, with claim 1 billed in Excel
Same contract as in the other posts: 1,000,000 before VAT, a 30% advance, 10% retention, work billed 30%, 40% and 30%. The advance tax invoice and claim 1 went out from the old sheet. On the day you switch, the position is this:
| Figure | Before VAT | After VAT |
|---|---|---|
| Work billed to date | 300,000.00 | 300,000.00 |
| Claims already issued | 1 | 1 |
| Advance received | 300,000.00 | 300,000.00 |
| VAT declared on the advance | 15,000.00 | 15,000.00 |
| Advance already recovered | 90,000.00 | 90,000.00 |
| Retention held | 30,000.00 | 31,500.00 |
| Advance left to recover | 210,000.00 | 210,000.00 |
After VAT the retention held includes its VAT (30,000 + 1,500), because the old claim declared it.

The next claim takes back only the advance that is left
Claim 2 bills 40% of the work, 400,000. It takes 30% of that as advance, 120,000, from the 210,000 left. Claim 3 takes the last 90,000. After that the advance is fully recovered, and a claim that tried to take more would be refused.
| Line | Before VAT | After VAT |
|---|---|---|
| Work certified | 400,000.00 | 400,000.00 |
| VAT on the work | — | 20,000.00 |
| Less advance (30%) | −120,000.00 | −126,000.00 |
| Less retention (10%) | −40,000.00 | −42,000.00 |
| VAT on the net | 12,000.00 | — |
| Payable | 252,000.00 | 252,000.00 |
After VAT the claim declares 20,000 − 6,000 = 14,000 and names the old advance tax invoice, whose VAT it does not declare again.
Nothing on claim 2 shows that a system changed. That is the test I would use: if the main contractor cannot tell from the claim that you switched, the position came across right.
- Claim 1, in the old system300,000.00
- Claim 2, in Mizarah400,000.00
- Claim 3, in Mizarah300,000.00
- On claim 1, in the old system90,000.00
- On claim 2120,000.00
- On claim 390,000.00
The 100% cap counts the old work
On the day you switch, this contract has 700,000 left to bill, not 1,000,000. Claims 2 and 3 bill exactly that. A fourth claim is refused, because the cap adds the work billed in the old system to the work billed since.
Skip the opening position and the new system believes the whole contract is still open. It would let you bill another 1,000,000, and the job would end at 130%.
The certificate keeps counting as well. Claim 2 is numbered 2, and its "previously certified" line shows the 300,000 billed before the switch, the same as it would have in the old sheet.
The old retention follows the old claims' method
Before VAT: the 30,000 held on claim 1 was never taxed. When it is released, it goes on the release tax invoice with its 1,500 of VAT, the same as the retention from the new claims. Releasing everything on this contract is 100,000 plus 5,000 of VAT: 105,000.
After VAT: the 31,500 held on claim 1 had its VAT declared on the old claim. It comes back as 31,500 with no VAT. Together with the 42,000 and 31,500 from the new claims, the release is 105,000 and declares nothing.
| Line | Before VAT | After VAT |
|---|---|---|
| From claim 1, billed in the old system | 30,000.00 | 31,500.00 |
| From claims 2 and 3 | 70,000.00 | 73,500.00 |
| VAT declared on the release | 5,000.00 | 0.00 |
| Payable | 105,000.00 | 105,000.00 |
This is why the old claims' method matters more than any other figure on the list. Get it wrong and the old retention either goes out without its VAT or has its VAT declared a second time.
Check it once, then leave it alone
Agree the figures before you issue anything new on the contract. After that, the position should not change, because every later claim is worked out from it. Four checks catch most mistakes:
- The work billed to date is not more than the contract.
- The advance recovered is not more than the advance received.
- The advance recovered plus the retention held is not more than the work billed.
- You know which method the old claims used. If the old sheet mixed the two, sort that out with your tax agent before you carry anything across.
Before you switch, it helps to work your next claim out by hand once, so you know what the new system should produce. The free progress bill calculator does one claim under either method.