Subcontractor Payment Certificate in Excel: Claimed vs Certified Quantities, Back-Charges and Retention

The subcontractor claims and the main contractor certifies, and between claimed and certified quantities most project disputes arise. On top of that, several things are deducted from the subcontractor’s entitlement: materials the main contractor supplied, labour or plant borrowed, damages, retention and advance instalments.

This sheet prepares the subcontractor certificate clearly. For each item you enter the quantity claimed by the subcontractor and the quantity certified by the main contractor, so the claim differences show, then it applies back-charges, retention and its release and advance recovery, giving the net due and its due date by the payment period in the contract.

Who is it for?

• Main contractor engineers certifying subcontractor claims
• Subcontractors who want to check their accounts
• Project managers and accountants
• Contract engineers

What is inside the sheet?

• A table of subcontract items with contract, claimed and certified quantities
• Claim difference by value and percent for each item
• A table of main contractor deductions: materials, labour, plant, damages and others
• Retention, its cap and the release of part of it
• Advance recovery
• Previously paid and net due
• Due date from the payment period
• A guide sheet

How to use it

1. Enter the subcontract data: value, retention percentage, advance and payment period in days.
2. Enter the items with quantities and rates.
3. Enter the cumulative claimed quantities then the certified ones after review.
4. Enter the main contractor’s deductions in their table with a description and value.
5. Read the net due, the due date and the claim difference.

Formulas used

• Certified value = certified quantity × rate
• Claim difference = claimed value − certified value
• Retention = % × certified value (capped)
• Net due = certified − retention − advance recovery − back-charges − previously paid + retention released
• Due date = certificate receipt date + payment period

Worked example from the sheet itself

An illustration: a claimed quantity of 120 m² at a rate of 85, and a certified 110 m².

• Claimed 10,200 and certified 9,350, so the claim difference is 850.
• Retention of 5% of 9,350 = 467.5, and a deduction of 1,200 for materials the main contractor supplied.
• The payment period is 45 days from receipt of the certificate.

Why claimed differs from certified

The usual causes: overstated progress, incomplete items claimed in full, quantities beyond the approved drawings, or no approval by the resident engineer of work not yet inspected. It is healthy to show the difference clearly and discuss it item by item and not as a total, so it does not turn into a dispute over the total.

The sheet shows the difference per item and in percent, and highlights items where the claim exceeds the certified by a large percentage so review can begin there.

Back-charges

When the main contractor supplies materials, labour, plant or services to the subcontractor (scaffolding, power, accommodation), or repairs defective work at its own cost, it deducts their value from the subcontractor’s entitlement. The deduction must be documented: a delivery note, a work order or a prior notice, and the contract must allow the deduction.

Do not deduct without notifying the subcontractor of the value and reason, since undocumented deductions are among the commonest causes of dispute. The sheet records a description and value for each deduction so they appear itemised in the certificate.

Retention and its release

As with the main contractor, retention is held from the subcontractor’s entitlement at a percentage, and many contracts release half at practical completion and the other half after the defects period. Enter the released amount in the right certificate and it is added to the amount due.

Beware of pay-when-paid

Some subcontracts include a pay-when-paid or pay-if-paid clause that ties payment to the subcontractor to receipt by the main contractor from the employer. The strength of such a clause varies with the law applied in your country, and some systems restrict or ban it. Ask a lawyer before relying on it. The sheet computes the due date from the contract payment period only and does not apply this clause.

Certifying step by step

1. Receive the certificate and record its date.
2. Review the quantities on site with the resident engineer.
3. Enter the certified quantities and document the differences.
4. Compute the back-charges with their documents.
5. Send the certified certificate to the subcontractor for agreement or objection.
6. Include it in the main contractor’s certificate to the employer under the main contract.

Common mistakes

• Deducting without a document or notice.
• Computing retention on the claimed value rather than the certified.
• Forgetting to recover the subcontractor’s advance.
• Delaying certification without notice, so the subcontractor starts claiming interest or stops work.
• Not matching the subcontractor’s rates to those in its contract.

Tips for best results

• Agree with the subcontractor on the measurement method before starting.
• Document every deduction with a paper both parties sign.
• Keep a log of certificate dates.

Frequently asked questions

Is the sheet for the employer or the main contractor?

It is designed for the main contractor certifying a subcontractor, and it helps the subcontractor check its accounts.

Does it compute VAT?

A rate in the contract data controls it, per your country’s system.

Does it handle pay-when-paid?

No. It computes the due date from the payment period only.

How many items?

Up to 50 items.

Download the sheet

Press the download button at the top of the page to get the Excel file. It includes a full guide sheet, yellow input cells and formulas protected from accidental edits. The sheet is also available in Arabic from the language switcher.

The sheet is a calculation aid. Contractual and legal rulings are for your contract and legal adviser.