How to Extract UK P11D Benefits Data into Excel
for HMRC Annual Reporting (2026 Guide)

What Goes on a P11D — and Which Fields Land in Your Spreadsheet

A P11D reports the taxable benefits in kind (BIK) — non-cash perks like a company car, private medical cover, or a low-interest loan — that an employer provided to a single employee or director during the tax year (6 April to 5 April). One form covers one person. HMRC organises the form into 14 lettered sections, A through N, each covering a benefit category with its own valuation rule, and it is those section values you need in columns.

Knowing the section letters matters for extraction because the same benefit always lives in the same section regardless of which payroll system printed the draft. Here are the sections as HMRC defines them on the official "How to complete P11D and P11D(b)" guidance:

Within every section, two numbers do the work. The cash equivalent is the taxable value of the benefit — for a company car it is the P11D value (list price plus accessories) multiplied by an appropriate percentage set by the car's CO2 emissions and fuel type. The amount made good is any contribution the employee paid toward the benefit, which reduces the taxable value. The figure that actually flows to HMRC is cash equivalent minus amount made good. A benefit flagged with the 1A indicator is one on which the employer owes Class 1A National Insurance — which matters when you build the P11D(b) total later.

So a practical P11D extraction spreadsheet is one row per employee and a column set that mixes identity fields with per-section benefit values:

Why P11D Benefit Data Is Harder to Extract Than a Payslip

A payslip has a stable, repeating field set — gross, tax, NI, net — that looks broadly the same person to person. A P11D does not, and that is the first reason manual transcription drags. Every employee's form is sparse in a different place: one director has Sections F, H, and I populated; a field engineer has only Section G; an office manager has just Section I. You are not reading the same 20 boxes on every page — you are hunting for whichever two or three of the fourteen sections happen to carry a value, and skipping the empty ones without mistaking a blank for a zero.

The second reason is that each populated section is valued by a different rule, so the numbers do not sanity-check against each other the way payslip figures do. A company car cash equivalent depends on list price and CO2 band; a beneficial loan depends on the average balance and HMRC's official rate of interest; medical cover is simply the premium. There is no single "does this look right" heuristic across sections, which means an operator retyping the figures has no natural error trap — a digit typed wrong in Section F looks exactly as plausible as the correct one.

The third reason is layout. HMRC mandates the data content of a P11D, not its visual form, so substitute P11D outputs from different payroll software place the same section values in different positions. Sage might print benefits in a stacked table with section letters down the left; BrightPay might group them differently; a draft exported to PDF from a bureau's own template might reorder everything. A template-based OCR tool trained on one provider's layout breaks on the next. Reading by meaning rather than by coordinates — the same principle behind extracting P60 year-end certificates across payroll providers — is what lets one column definition cover every source.

Setting Up Your P11D Extraction Workflow

The workflow that replaces manual P11D transcription has three steps, and the first — defining your columns — is the one you do once and reuse for every employee, every payroll provider, and every future tax year the P11D survives.

You can try the extraction step on a single P11D right here — upload a draft (or a photo of one) and name a couple of columns to see how the section values map:

Building the P11D(b) Total from Your Extracted Data

A structured spreadsheet is not the end of the job — it is what makes the P11D(b) a formula instead of a calculator session. The P11D(b) is the employer's declaration of total Class 1A National Insurance owed across every benefit reported for every employee. HMRC's CWG5 guidance sets the arithmetic: add together the cash equivalent of every benefit liable to Class 1A, then multiply the total by the Class 1A rate — 15% for 2025/26, up from the 13.8% that applied through 5 April 2025.

With extracted data in columns, that is one SUMIF across your 1A-liable benefit columns and one multiplication. But the tool can go a step further: a computed column lets the AI calculate values during extraction rather than after. Name a column Total Taxable Benefit (sum of cash equivalents minus amounts made good) and the per-employee net figure is filled in as the drafts are read — so the number you sum for the P11D(b) is already net of employee contributions, one of the more common manual-aggregation slips.

Validating Extracted P11D Data Before It Feeds the P11D(b)

Because each section is valued by its own rule, the validation pass here is less about arithmetic checks and more about shape and completeness. The checks below run column by column in Excel and surface the handful of rows worth eyeballing against the source draft — not every field on every row.

Each extracted row carries its source file reference, so any flagged row is one click from the original draft. That traceability is what makes column-level checks realistic at 150 employees — manual transcription could never sustain them, which is precisely why manual P11D preparation tends to skip validation and hope.

P11D vs P60 vs P45: Three Forms, Three Extraction Jobs

UK payroll teams handle three statutory employee forms, and they are easy to conflate but need different column definitions. A P60 is the year-end certificate of pay and tax deducted, issued to everyone on payroll at 5 April. A P45 is issued when someone leaves, summarising pay and tax to their leaving date. A P11D reports taxable benefits in kind and is the only one of the three concerned with perks rather than pay.

The practical upshot: a P11D column set is about section cash equivalents and Class 1A liability, and does not overlap with a pay-and-tax form beyond the identity fields. If your team processes all three, keep a separate saved column definition for each and reuse them — the batch workflow is identical, but the field names are form-specific. See our guides to extracting P45 leaver data into Excel, the complete P45 extraction workflow, and batch-processing P60s for a payroll audit for the pay-and-tax side of the same toolkit.

FAQ