Subcontractor payment certificate template in Excel
A subcontractor certificate is what the main contractor pays a subcontractor for the period works. This free Excel template needs no sign-up: the same previous, current and cumulative logic, with the deductions of a subcontract, including the materials the main contractor supplied.
Free, with no sign-up and no email · Excel file (xlsx) in Arabic, right-to-left, ready to print
Who is this template for?
- Main contractor: prepare each subcontractor's certificate and check its quantities before paying.
- Subcontractor: claim the period works from the main contractor on the same table it will be checked on.
- Project manager: compare what is paid to subcontractors with what is claimed from the owner for the same items.
- Accountant: see the net due after the materials supplied and the retention are deducted.
What is in the file?
- One right-to-left sheet, set to print on A4 landscape.
- The subcontract details in the title: project, main contractor, subcontractor, subcontract number and trade.
- Two item sections, subcontract works and additional works, with example items marked «مثال» and empty rows with their formulas.
- The deductions: retention, advance recovery, materials supplied by the main contractor, delay penalties (an optional line) and other deductions, then the net due and the signature boxes.
How to fill it, step by step
- Type the project, main contractor, subcontractor, subcontract number, certificate number and period in the title lines.
- Delete the example items and copy the subcontract items with their rates and quantities, not the owner contract items.
- Copy the cumulative quantity of the last approved subcontractor certificate into «الكمية السابقة» (previous quantity), and type the period quantity in «الكمية الحالية» (current quantity).
- Type the value of the materials you supplied to the subcontractor as a negative on its line: what was deducted before under previous, this period's materials under current.
- Leave the delay penalties line at zero unless the subcontract sets a penalty and the delay is established.
- Check the retention and advance rates against the subcontract, then write the amount due in words and collect the signatures.
Column guide: what goes in each column
| Column in the file | What to write |
|---|---|
| مNo. | The item number as in the subcontract. |
| بيان الأعمالDescription of works | The item description as in the subcontract bill of quantities. |
| الوحدةUnit | Unit of measure: m3, m2, linear metre, tonne, number or lump sum. |
| الكمية التعاقديةContract quantity | The item quantity in the subcontract, not in the owner contract. |
| الفئةUnit rate | The rate per unit in the subcontract. |
| الكمية السابقةPrevious quantity | The cumulative quantity of the last approved subcontractor certificate. Zero on the first. |
| الكمية الحاليةCurrent quantity | What the subcontractor did in the period, from a take-off the site engineer checks. |
| الكمية الإجماليةCumulative quantityformula | Previous + current. A formula, do not type in it. |
| نسبة الصرف %Payment % | 100% for a finished item, a lower share for an item not yet accepted or waiting for snags to be fixed. |
| القيمة السابقةPrevious valueformula | Previous quantity × rate × payment %. |
| القيمة الحاليةCurrent valueformula | Current quantity × rate × payment %. |
| القيمة الإجماليةCumulative valueformula | Cumulative quantity × rate × payment %. |
| نسبة الإنجازProgressformula | Cumulative ÷ contract quantity. Above 100% needs a variation to the subcontract. |
| ملاحظاتNotes | The take-off record number, or why the payment % is reduced. |
A worked example from the file
The internal plaster item: contract quantity 6,500 m2 at a rate of 115, 1,200 m2 paid before and 1,800 m2 done this period, so the current value is 207,000. For the whole certificate (the five example items):
| Works this period | 573,500 |
|---|---|
| Retention 5% on the period works | 28,675 |
| Advance recovery 10% on the period works | 57,350 |
| Materials supplied by the main contractor this period | 62,500 |
| Delay penalties | 0 |
| Net due to the subcontractor for this certificate | 424,975 |
Net due for the certificate = net to date minus the net of the previous certificates; the file works it out on the last line of the summary.
Practical notes
- Record every material issue to a subcontractor on an issue note it signs; deducting materials without one invites a dispute.
- The retention and advance rates in the file are examples; the right rates are the ones in the subcontract.
- Do not pay a subcontractor more quantity than the consultant approved for the owner on the same item, unless there is a written reason.
- If the contract adds a tax or a stamp duty, put it on the other deductions line as a negative.
Manage subcontractor certificates in Ta3mir
In Ta3mir every subcontractor has its own contract and chain of certificates; each certificate starts from the previous one, and retention and advance recovery come from the contract terms.