Excel to BIR DAT File: Fixing Rounding and Centavo Mismatches That Fail BIR Validation

A BIR DAT file can fail the Alphalist Data Entry and Validation Module even when every TIN, name, and ATC in it is correct, because the file’s declared total doesn’t match the sum of its own detail-row amounts down to the centavo — and the usual cause isn’t bad data, it’s rounding each line and the overall total in a different order. This is a distinct problem from a record-count mismatch in the file’s trailer (covered in Excel-to-DAT File Conversion Mistakes: Header, Detail, and Trailer Records) — here, the row count is right and every individual amount is plausible, but the arithmetic doesn’t tie out to the last centavo.

Why the BIR’s electronic format forces exact reconciliation in the first place #

DAT files exist precisely so the BIR’s systems can machine-validate a filer’s data instead of re-keying a paper listing, and that validation checks arithmetic consistency between the file’s parts, not just whether each field is individually well-formed. The RELIEF/Alphalist electronic-submission framework was set up under BIR Memorandum Order No. 4-2003, which describes the underlying requirement plainly:

“…the RELIEF System shall cover all VAT taxpayers above the threshold limits set by [Revenue Regulations] No. 8-2002 to submit Summary Lists of Sales and Purchases in magnetic form based on a prescribed electronic format…”

A “prescribed electronic format” is machine-checked, and one of the checks every version of the Data Entry and Validation Module performs is whether a file’s totals reconcile internally — which is exactly the check a centavo-level rounding discrepancy fails, even though nothing is wrong with the underlying transactions themselves.

Where the mismatch actually comes from #

The near-universal cause of a one- or few-centavo total mismatch is rounding at the wrong stage of the calculation: Excel (or whatever system exports the data) computes each row’s withholding or VAT amount at full decimal precision, then either rounds each row before summing, or sums the unrounded figures and rounds only the grand total — and those two approaches don’t always agree. For example, a 2% withholding computation on ₱12,345.67 produces ₱246.9134, which rounds to ₱246.91 on its own; summed across several similarly awkward amounts, rounding each row first can land a centavo or two away from rounding the combined raw total first. Standard BIR filing practice is to round each line item to two decimals before it ever gets summed — the detail amount in the DAT file and the amount used to build the declared total should come from the same already-rounded figures, not from two separate calculation paths.

A few concrete Excel causes of this:

• A formula like =ROUND(SUM(range),2) on the total row, while each individual row uses an unrounded formula — the total is rounded once, the rows never are, so the file’s sum-of-rows disagrees with its own total.
• Mixing ROUND() and TRUNC() in the same workbook — truncation silently drops the fractional centavo instead of rounding it, producing a small but consistent downward drift across many rows.
• A hidden or filtered row still being summed into the header/trailer total but not exported into the DAT file’s detail records (or the reverse — exported but excluded from the displayed total).

How to check and fix it before uploading #

1. Round every detail row’s tax/VAT amount to exactly two decimal places with ROUND(value, 2) — never leave it as a raw formula result.
2. Build the file’s total as =SUM() of the already-rounded detail column, not as a separately rounded calculation of the unrounded figures.
3. Before export, manually foot the rounded detail column and compare it to the total cell — they should match to the centavo; if they don’t, the rounding order is still inconsistent somewhere in the sheet.
4. Re-run the comparison after any late edit (added row, deleted row, changed rate) — a fix applied once can silently break again if a new row is pasted in with the old unrounded formula.

Worked example: a five-payee QAP sheet #

A withholding agent’s Q3 QAP worksheet computes 2% EWT on five payees’ gross income. Four rows round cleanly, but one payee’s gross income of ₱87,432.50 produces a raw 2% figure of ₱1,748.65 exactly — no rounding issue there either. The actual problem row is a payee paid ₱33,333.33, whose 2% raw result is ₱666.6666: the sheet’s total-row formula sums all five raw (unrounded) results and only then applies ROUND() to the grand total, giving a declared total that is ₱0.01 higher than what you get from summing the five individually rounded row amounts (₱666.67 for that row, alongside the other four).

The fix is to insert a rounded “EWT (2 decimals)” column — =ROUND(B2*0.02,2) for each row — and export the DAT file’s detail amounts from that rounded column, with the total built as =SUM() of that same rounded column rather than a separate rounding of the raw figures. Once both the detail rows and the total are built from the same rounded numbers, the file’s internal total matches the sum of its rows exactly, and the centavo-level validation failure stops recurring on every future quarter’s file, not just this one.

Summary #

A DAT file that fails validation with every individual row looking correct is almost always a rounding-order problem: round each detail amount to two decimals first, then sum those already-rounded figures for the total — never round only the final total while leaving the detail rows unrounded, and never mix ROUND() with TRUNC() in the same workbook. This keeps the file internally consistent the way the BIR’s Data Entry and Validation Module expects under the prescribed electronic format set up by BIR Memorandum Order No. 4-2003. For the file-structure side of DAT validation errors, see Excel-to-DAT File Conversion Mistakes: Header, Detail, and Trailer Records, and for a broader error checklist, see Excel to BIR DAT File: The Most Common Validation Errors and How to Fix Each One.