Progress bills in Excel: the five places they break.
Excel is where most small contractors start, and a careful sheet can bill a contract correctly. The trouble usually starts on the third or fourth claim, when the sheet has been copied forward, a formula still points at last month and the advance comes off twice.
By Muhammad Arslan, founder of Mizarah, who kept a UAE joinery's books for thirteen years · 7 minute read · Updated 24 October 2026 · Practical guidance, not tax advice: confirm anything that matters for your business with your tax agent or the Federal Tax Authority.
A progress bill in Excel usually goes wrong in five places: VAT worked out on a mix of before- and after-deduction figures, the advance recovered more or less than once, retention withheld but never tracked to its release, previous claims deducted at the wrong value, and VAT rounded on the total instead of line by line. Three checks catch most of it: billed to date never above 100%, advance recovered plus advance left equal to the advance received, and VAT across the contract equal to 5% of its value.
The most common error is a mix of the two methods.
A sheet that takes the advance off before working out VAT, but leaves the retention inside the VAT base, produces a third set of figures. It matches neither method, so it matches neither the certificate nor what the main contractor expects to pay.
Pick one method, before VAT or after VAT, and make every deduction in every column follow it.
| Method | VAT on the claim | Payable |
|---|---|---|
| Before VAT | 9,000.00 | 189,000.00 |
| After VAT | 10,500.00 | 189,000.00 |
| Mixed: advance before, retention after (wrong) | 10,500.00 | 190,500.00 |
The advance comes off once in total, never twice.
Each claim takes back its share of the advance until all of it is recovered. In a sheet copied forward each month, the usual slips are a recovery row repeated from last month, a percentage taken of the cumulative work instead of this claim's work, and a before-VAT claim that deducts the advance with its VAT.
- Before VAT, the advance comes off at its net value: 90,000 on a 300,000 claim, not 94,500.
- After VAT, it comes off with its VAT, and the claim declares only the VAT not already declared on the advance tax invoice.
- Across all the claims, recovered plus still to recover equals the advance received.
| Claim | This claim | Recovered to date | Left to recover |
|---|---|---|---|
| Claim 1 | 90,000.00 | 90,000.00 | 210,000.00 |
| Claim 2 | 120,000.00 | 210,000.00 | 90,000.00 |
| Claim 3 | 90,000.00 | 300,000.00 | 0.00 |
Retention is withheld in one column and forgotten everywhere else.
The sheet takes 10% off each claim, and then nothing records what is held on each contract or when it comes back. The release falls due after the defects period, often a year or more after handover, when nobody is looking at that sheet any more.
Keep a running total of retention held on every contract, with its release stages and dates. Before VAT, the release is a tax invoice of its own: the retention plus 5%, declared when you release it.
| After | Withheld | Held to date |
|---|---|---|
| Claim 1 | 30,000.00 | 30,000.00 |
| Claim 2 | 40,000.00 | 70,000.00 |
| Claim 3 | 30,000.00 | 100,000.00 |
| Held after the last release | 0.00 | |
A cumulative certificate needs the earlier work taken off at the right value.
Many certificates are cumulative: they show the work done to date, then subtract what was certified before. The claim is the difference, and the advance and the retention are worked out on that difference.
When an earlier invoice is deducted as a whole, its value follows the method: before VAT it comes off at its net value (received 105, deduct 100); after VAT, including its VAT.
| Line | Amount |
|---|---|
| Work done to date | 700,000.00 |
| Less work certified before | −300,000.00 |
| This claim | 400,000.00 |
| Less advance (30%) | −120,000.00 |
| Less retention (10%) | −40,000.00 |
| Taxable amount | 240,000.00 |
| VAT (5%) | 12,000.00 |
| Payable | 252,000.00 |
Round VAT on each line, then add the lines.
A sheet that works out VAT once on the total can disagree by a fil or two with the same VAT rounded line by line, and the printed invoice stops adding up. Rounding each line, then adding the lines, keeps every line and the total in agreement.
Across a contract the VAT may then differ from exactly 5% by rounding fils only, because each document is rounded on its own.
| Way | VAT |
|---|---|
| Rounded on each line: 0.51 + 0.51 | 1.02 |
| Worked out again on the total of 20.58 | 1.03 |
Three checks that catch most mistakes.
Run these on every contract before a claim goes out, whatever you bill it with. If a check fails, the sheet is wrong somewhere above it.
- Work billed to date, including this claim, is not above the contract value.
- Advance recovered to date plus advance left to recover equals the advance received.
- Over the whole contract, the VAT declared is 5% of the contract value, to within rounding fils.
Mizarah runs these checks itself: a claim past 100% is refused, the advance comes off under your company's method until the advances account is zero, and VAT is rounded on each line. The free progress bill calculator and Excel template on this site apply the same rules.
Common questions.
- Can I do progress billing in Excel?
- Yes, for one or two contracts at a time with one person doing the billing. Use one VAT method for every deduction, keep running totals for the advance and the retention, and run the three checks on every claim.
- Is there a free progress bill template?
- Yes. The progress bill calculator on mizarah.com works out a claim with an advance, retention and VAT by either method, and has an Excel template that follows the same rules.
- Why does my VAT not match the main contractor's figure?
- Usually because the two sides use different methods, or the sheet mixes them: for example the advance off before VAT but the retention inside the VAT base. Agree the method with the main contractor and apply it to every deduction.
- Should VAT be rounded per line or on the total?
- Choose one and use it everywhere. Rounding each line and adding the lines keeps every line and the total in agreement; working VAT out again on the total can differ by a fil or two.
Read next: VAT on progress claims: before or after deductions · Progress bill calculator · Choosing software for a UAE contractor
See your own contract worked out this way.
Put a contract you have already billed through the trial and check the figures against what you invoiced. No card needed, and nothing you enter is deleted when the trial ends.