Reinsurance

Postcode Validation on Reinsurance Bordereaux: Error Guide

On this page

Last reviewed: November 2026

The flood accumulation report put 31% of a treaty's sum insured in one postcode. It was the cedent's head office.

Elsewhere, a thousand German rows had four-digit postcodes. Excel had removed the leading zero from every one.

Postcode validation on reinsurance bordereaux tests each risk's postcode for format, completeness and country fit before exposure is measured.

Cat models, CRESTA aggregates and event estimates all start from the postcode, so one bad column moves every number.

This guide gives you a country format table, seven postcode error types with tests and fixes, Excel checks and a fallback ladder.

Key Takeaways

  • Postcodes are text. Store them as text from import, or leading zeros disappear.
  • Test by country. Each country needs its own pattern; one rule for all fails.
  • Watch concentration. A single postcode with a large share of sum insured is usually a placeholder.
  • Degrade, don't guess. Map partial codes to a coarser zone and report them separately.

What Is Postcode Validation on Reinsurance Bordereaux?

Postcode validation on reinsurance bordereaux is a set of row-level tests that each risk location carries a real, complete postcode for its country.

It feeds cat exposure returns to reinsurers and cat model input preparation.

Which Postcode Formats Should a Reinsurer Test by Country?

Postcode formats a reinsurer should test by country differ in length, characters and leading zero risk.

CountryStructurePattern to testLeading zero risk
UKOutward code 2–4 characters, space, inward code 3 characters^[A-Z]{1,2}[0-9][A-Z0-9]? [0-9][A-Z]{2}$None, alphanumeric
US5-digit ZIP or ZIP+4^[0-9]{5}(-[0-9]{4})?$Yes, e.g. Puerto Rico 006–009
India6-digit PIN, first digit 1–9^[1-9][0-9]{5}$No, but numeric storage
Germany5 digits since 1 July 1993^[0-9]{5}$Yes, codes starting 0

The ONS postal geography page sets out the UK outward and inward codes and about 1.79 million live unit postcodes.

The USPS addressing standard asks for a correct 5-digit ZIP Code or ZIP+4 and lists Puerto Rico prefixes 006 to 009.

The UK pattern is simplified and will miss a few special codes. Treat a fail as a query, not an automatic rejection.

Which Postcode Errors Break Treaty Cat Exposure on Bordereaux?

Postcode errors that break treaty cat exposure on bordereaux fall into seven types, each with its own symptom, test and fix.

Why do postcodes lose leading zeros on bordereaux?

Postcodes lose leading zeros on bordereaux when a spreadsheet stores them as numbers.

  • Symptom: German or US rows with four digits; Puerto Rico ZIPs as three.
  • Cause: Microsoft notes Excel automatically removes leading zeros.
  • Test: numeric value, length below the country's length.
  • Fix: pad with TEXT(value,"00000") for five-digit countries only, then import as text.

How do reinsurers spot placeholder postcodes on a bordereau?

Reinsurers spot placeholder postcodes on a bordereau through a dummy value list and a concentration test.

  • Symptom: 00000, TBC, N/A, or the cedent's own office postcode repeated.
  • Cause: a mandatory field filled to pass a system edit.
  • Test: share of total sum insured per postcode; investigate any single code above 2%.
  • Fix: return the rows for real risk addresses; report the sum insured as unlocated meanwhile.

The 2% trigger is illustrative. Tune it to the portfolio's size and spread.

What should happen to partial postcodes on a property bordereau?

Partial postcodes on a property bordereau should be used only at the resolution they really support.

  • Symptom: UK outward code only, such as PO15, or a PIN cut to three digits.
  • Cause: older systems or privacy trimming by the cedent.
  • Test: UK outward-only pattern ^[A-Z]{1,2}[0-9][A-Z0-9]?$, or length short of the country standard.
  • Fix: map to district or a CRESTA low-resolution zone and flag the row.

How do mailing addresses distort postcode-level accumulation?

Mailing addresses distort postcode-level accumulation when the bordereau holds the policyholder's correspondence address, not the risk address.

  • Symptom: multi-site policies with one postcode; landlords' portfolios at an agent's office.
  • Cause: the cedent exports the billing address field.
  • Test: policies with several buildings but one postcode, and postcodes matching broker or agent offices.
  • Fix: require a risk address per location; see property bordereaux for reinsurers.

How do country mismatches show up in postcode fields?

Country mismatches show up in postcode fields as valid codes tested against the wrong country's pattern.

  • Symptom: a five-digit code on a UK row, or a UK code on an Irish row.
  • Cause: country defaulted to the cedent's home market.
  • Test: run all four patterns; a pass on a different country's pattern is a mismatch.
  • Fix: correct the country first, then revalidate the postcode.

Why do badly formatted UK postcodes fail lookup tables?

Badly formatted UK postcodes fail lookup tables because the reference file holds one standard form.

  • Symptom: po155rr, PO15 5RR or PO155RR.
  • Cause: free-text capture with no input mask.
  • Test: the UK pattern fails, but passes after normalising.
  • Fix: upper case, remove spaces, insert one space before the last three characters.

Why do CRESTA zone versions break postcode mapping?

CRESTA zone versions break postcode mapping because zone boundaries change between releases.

NIQ's 2026 announcement lists 140 countries, 4,814 low-resolution and 247,202 high-resolution zones.

It says the zones rest on administrative and postcode boundaries, with assignment tables to compare vintages.

  • Fix: record the CRESTA version on every exposure return and remap old returns before comparing years.

Which Formulas Detect Postcode Errors in a Bordereau?

Formulas that detect postcode errors in a bordereau can run in Excel for Microsoft 365, using REGEXTEST for patterns.

Assume A country code, B postcode and C sum insured.

UK full format        =REGEXTEST(B2,"^[A-Z]{1,2}[0-9][A-Z0-9]? [0-9][A-Z]{2}$")
UK outward only       =REGEXTEST(B2,"^[A-Z]{1,2}[0-9][A-Z0-9]?$")
US ZIP or ZIP+4       =REGEXTEST(B2,"^[0-9]{5}(-[0-9]{4})?$")
India PIN             =REGEXTEST(B2,"^[1-9][0-9]{5}$")
Germany PLZ           =REGEXTEST(B2,"^[0-9]{5}$")
Lost leading zero     =AND(OR(A2="DE",A2="US"), ISNUMBER(B2), LEN(B2)<5)
Placeholder           =OR(B2="00000",B2="TBC",B2="N/A",B2="UNKNOWN")
Share of sum insured  =SUMIFS(C:C,B:B,B2)/SUM(C:C)
UK normalised         =LEFT(SUBSTITUTE(UPPER(B2)," ",""),LEN(SUBSTITUTE(B2," ",""))-3)&" "&RIGHT(SUBSTITUTE(UPPER(B2)," ",""),3)

Microsoft's REGEXTEST page confirms the match is case-sensitive by default.

Older Excel versions lack REGEXTEST; use the cedent's bordereau template mapping stage instead.

How Should Unresolved Postcodes Be Handled in Treaty Cat Exposure?

Unresolved postcodes in treaty cat exposure should move down a fallback ladder, never be guessed.

LevelUse whenReport as
1. Full postcodePattern passes and lookup succeedsLocated
2. District or sectorPartial code onlyLocated, coarse
3. CRESTA low-resolution zoneDistrict maps to one zoneZone level
4. Region or stateOnly city or state knownRegion level
5. UnknownPlaceholder or no dataUnlocated sum insured, shown separately

Set a tolerance for levels 4 and 5 in the bordereau agreement, and track it each quarter.

How Does Insurnest Fit Around Postcode Validation?

Reinsurance Bordereaux Reconciliation does not validate postcodes or any location field.

Its job starts after the data is clean: it calculates each reinsurer's premium share and claim liability from configured participation.

It then compares those values with bordereaux, premium and settlement records, and marks each position matched or for review.

Key Terms

  • Outward code: The first part of a UK postcode, naming the area and district.
  • CRESTA zones: A standard system of zones used to aggregate insured values for accumulation control.
  • Unlocated sum insured: Exposure that cannot be placed at any usable geographic level.

Sources

Frequently Asked Questions

What is postcode validation on reinsurance bordereaux?

Postcode validation on reinsurance bordereaux tests each risk row's postcode for format, completeness and country fit, so treaty cat exposure is measured at the right place.

Which postcode formats should a reinsurer test by country?

A reinsurer should test postcode formats by country: UK outward plus three-character inward code, US five-digit ZIP or ZIP+4, India six-digit PIN, Germany five digits.

Why do postcodes lose leading zeros on bordereaux?

Postcodes lose leading zeros on bordereaux because Excel stores them as numbers. Microsoft confirms Excel automatically removes leading zeros unless the column is formatted as text.

How do reinsurers spot placeholder postcodes on a bordereau?

Reinsurers spot placeholder postcodes on a bordereau by listing known dummy values and testing concentration: any single postcode carrying an unusual share of total sum insured.

What should happen to partial postcodes on a property bordereau?

Partial postcodes on a property bordereau should be accepted only at the lower resolution they support, flagged as such, and queried with the cedent for full codes.

Why do CRESTA zone versions break postcode mapping?

CRESTA zone versions break postcode mapping because boundaries change between releases. The 2026 release covers 140 countries with 247,202 high-resolution zones.

Which formulas detect postcode errors in a bordereau?

Formulas that detect postcode errors in a bordereau include REGEXTEST country patterns, a TEXT padding check for lost zeros, placeholder lists and SUMIFS concentration shares.

How should unresolved postcodes be handled in treaty cat exposure?

Unresolved postcodes in treaty cat exposure should move down a fallback ladder: full postcode, district, CRESTA low-resolution zone, region, then unknown with its sum insured reported separately.

Hitul Mistry

Hitul Mistry

CEO, Insurnest

An InsurTech leader with more than a decade of experience across insurance and technology, focused on solving business problems with the help of technology. Has worked with brokers, insurance carriers, and reinsurance firms across the India, UAE, and US markets.

View LinkedIn profile →
ShareLinkedInX

Read our latest blogs and research

Featured Resources

Reinsurance

Cat Exposure Returns to Reinsurers: Accurate Aggregates

Cat exposure returns to reinsurers need accurate peril aggregates. Use this four-stage checklist on scope, location data, aggregation and sign-off every time.

Read more
Reinsurance

Property Bordereaux for Reinsurers: Error Catalogue & Fixes

property bordereaux for reinsurers: an error catalogue for locations, sums insured and building codes, with detection rules, fixes and a 10x outlier example.

Read more
Reinsurance

Cat Model Input Preparation: Clean Cedent Exposure Data

Cat model input preparation cleans cedent exposure schedules before modelling. Follow a worked example, a step-by-step method, key controls and edge cases.

Read more

Meet Our Innovators:

We aim to revolutionize how businesses operate through digital technology driving industry growth and positioning ourselves as global leaders.

circle basecircle base
Pioneering Digital Solutions in Insurance

Insurnest

Empowering insurers, re-insurers, and brokers to excel with innovative technology.

Insurnest specializes in digital solutions for the insurance sector, helping insurers, re-insurers, and brokers enhance operations and customer experiences with cutting-edge technology. Our deep industry expertise enables us to address unique challenges and drive competitiveness in a dynamic market.

Get in Touch with us

Ready to transform your business? Contact us now!

We respond within one business day.