---
source: https://carbone.io/examples/insurance-premium-bordereau/
title: "Insurance Bordereau in Excel for Lloyd's | Carbone"
description: "Generate an insurance bordereau in Excel from JSON with Carbone: Lloyd's CRS v5.2 risk and premium fields, live formulas, surplus lines taxes, flagged rows."
generated_at: "2026-11-17"
---

# Insurance premium bordereau

A Lloyd's coverholder's monthly risk and premium report in Excel

[![Insurance premium bordereau: XLSX generated from an XLSX template and JSON data](/examples/insurance-premium-bordereau/thumb-result.webp)](/examples/insurance-premium-bordereau/result.xlsx "Download the generated document")

[Download template](/examples/insurance-premium-bordereau/template.xlsx)

[Sample data](/examples/insurance-premium-bordereau/data.json)

[Download the generated XLSX](/examples/insurance-premium-bordereau/result.xlsx)

- **Template:** XLSX
- **Export as:** XLSX, PDF, ODS, CSV, HTML [+19 more](/documentation/developer/http-api/generate-reports.md#output-file-type)
- **Carbone:** 5.18+

Features

-   [Loop](/documentation/design/repetitions/with-arrays.md)
-   [Excel table](/documentation/design/template-formats/xlsx.md)
-   [Formula](/documentation/design/template-formats/xlsx.md)
-   [Conditional formatting](/documentation/design/template-formats/xlsx.md)
-   [Chart](/documentation/design/advanced-features/charts.md)
-   [Date](/documentation/design/formatters/date.md)
-   [Number format](/documentation/design/formatters/number.md)

## Generate insurance bordereaux from your policy administration system

A coverholder, often an MGA, binds risks on behalf of Lloyd's syndicates under a binding authority, and reports every transaction to the underwriters in a monthly bordereau. Lloyd's asks syndicates to collect this data in line with its [Coverholder Reporting Standards](https://www.lloyds.com/market-resources/delegated-authorities/compliance-and-operations/reporting-standards) for any binding authority incepting or renewing since July 2017. The policy administration system sends the month's transactions to the Carbone API as JSON and gets back the bordereau as a native Excel workbook, which the Lloyd's broker and the underwriters filter, check and recalculate.

Here, Tidewell Specialty Underwriters, a fictional Texas MGA, reports October 2026 under a three-section binding authority for US surplus lines business: commercial property, general liability and professional liability. The bordereau follows the structure of the Lloyd's Coverholder Reporting Standards v5.2: each column carries the field name of the Risk/Premiums standard, in the order of its US template, under its field code marked `M` for mandatory or `C` for conditional. The fields that apply to the whole binding authority are in the header.

In this example, you will:

-   Show the binding authority, the reporting period and the totals in a header block
-   List the transactions in a loop inside an Excel table
-   Convert the JSON dates to native Excel dates
-   Compute the commission, the net premium to London and the surplus lines taxes with Excel formulas
-   Flag cancellations, return premiums and late transactions with conditional formatting
-   Summarize the premium by class of business and the taxes by State of Filing, with a native chart

## How to build an insurance bordereau in Excel

### 1\. Add the binding authority header

Rows 4 to 11 hold the fields that apply to the whole binding authority, each with its CRS code: `{d.coverholder.name}` (CR0013), `{d.binder.umr}` (CR0005), `{d.binder.usClassification}` (CR0025) and the surplus lines broker. The title cell also holds `{o.preReleaseFeatureIn=5018000}`, so the cell prints only the title.

The reporting period dates are native Excel dates, so formulas and conditional formatting can compare them:

```text
I5: {d.period.start:formatD(X):div(86400):round(0):add(25569):formatN}
```

[:formatD(X)](/documentation/design/formatters/date.md#formatd-patternout-patternin) gives the Unix time in seconds, [:div(86400)](/documentation/design/formatters/number.md#div-value) turns it into days, [:round(0)](/documentation/design/formatters/number.md#round-precision) removes the time zone offset, and [:add(25569)](/documentation/design/formatters/number.md#add-value) moves the origin to January 1, 1900, the origin of Excel dates. [:formatN](/documentation/design/template-formats/xlsx.md#numbers-and-dates) writes the result as a number, and the cell format `dd mmm yyyy` displays `01 Oct 2026`.

### 2\. List the transactions in an Excel table

-   Row 13 holds the CRS codes, and row 14 the exact CRS field names, from `Section No` (CR0007) to `Industrial sector of the insured` (CR1298).
-   Row 15 repeats each transaction with a [loop](/documentation/design/template-formats/xlsx.md#loops): `{d.risks[i].certificateRef}`, `{d.risks[i].insuredName}`, `{d.risks[i].riskTransactionType}`, and the same date tag for `{d.risks[i].inceptionDate}`, `{d.risks[i].expiryDate}` and `{d.risks[i].effectiveDate}`.
-   Amounts and rates are single tags ending with `:formatN`, like `{d.risks[i].grossPremium:formatN}` and `{d.risks[i].commissionRate:formatN}`, so they stay numbers with the cell formats `#,##0.00;(#,##0.00)` and `0.0%`.
-   The last columns hold the policy issuance date, the TRIA terrorism acceptance and declination dates, and the industrial sector of the insured. A terrorism date can be empty, so its tag ends with `:ifGT(0):elseShow(''):formatN`, which leaves the cell empty instead of writing an invalid number.
-   Row 16 ends the loop with `{d.risks[i+1].certificateRef}`.
-   Rows 14 to 17 form an [Excel table](/documentation/design/template-formats/xlsx.md#tables-and-filters) named `Bdx`, with a total row. Carbone extends it to the 22 transactions, so the filters and the totals cover every row. The panes are frozen under the header row and after the insured name.

### 3\. Compute the premium with Excel formulas

The JSON holds only the inputs of the policy administration system: gross premium, commission rate, tax rates. The template computes the rest in the first row of the table, and Carbone copies each [formula](/documentation/design/template-formats/xlsx.md#formulas) to every transaction:

```text
L15: =IF(OR(G15="New business",G15="Renewal"),"Original premium",IF(N15<0,"Return premium","Additional premium"))
P15: =ROUND(N15*O15,2)       Commission Amount
R15: =N15-P15                Net Premium to London
AA15: =ROUND(Y15*Z15,2)      Tax 1 - Amount, the surplus lines tax
Q15: =AA15+AH15              Total Taxes and Levies
```

Return premiums are negative, so a cancellation lowers the gross premium, the commission and the taxes in the totals. The surplus lines tax and the stamping fee follow the State of Filing, the home state of the insured. For policies incepting in 2026, they are 4.85% and 0.04% in Texas, 3% and 0.18% in California, 3.6% and 0.15% in New York. The surplus lines broker pays them to the state, so they are not part of the Net Premium to London.

The header totals use structured references, like `=SUM(Bdx[Net Premium to London in original currency])`, and the total row uses `=SUBTOTAL(109,Bdx[Gross premium paid this time])`, so it follows the filters.

### 4\. Flag the transactions with conditional formatting

The [conditional formatting](/documentation/design/template-formats/xlsx.md#conditional-formatting) rules of row 15 apply to all the generated rows:

-   The Risk, Transaction Type is green for New business, blue for Renewal, amber for Endorsement and red for Cancellation.
-   `=$G15="Cancellation"` tints the row, and negative amounts are red.
-   Data bars compare the gross premium of the transactions.
-   `=OR(M15<$I$5,M15>$I$6)` flags in amber an effective date outside the reporting period, like the September endorsement reported in October. The header counts these transactions in "Flagged for review".

### 5\. Summarize by class of business and by State of Filing

The Summary sheet loops on `{d.binder.sections[i].classOfBusiness}` and `{d.stateOfFiling[i].state}`, and sums the bordereau with `=SUMIFS(Bdx[Gross premium paid this time],Bdx[Section No],A6)`. The native [bar chart](/documentation/design/template-formats/xlsx.md#charts) of the gross premium by class of business uses the cells of the loop, so it shows every section. The control totals check that the classes add up to the bordereau, that the gross premium less the commission equals the Net Premium to London, and that the taxes by state add up to the Total Taxes and Levies.

> This template requires Carbone v5.18.0 or later. The tag `{o.preReleaseFeatureIn=5018000}` enables the support of Excel tables, formulas, conditional formatting and charts on the rows generated by loops.

## Related Examples

- [Excel transaction report](/examples/transaction-report/index.md): XLSX template to XLSX.
- [Quarterly financial dashboard](/examples/s-financial-dashboard/index.md): XLSX template to XLSX.
- [GHS safety data sheet](/examples/safety-data-sheet/index.md): DOCX template to PDF.

## Related topics

- [CSRD sustainability statement](/examples/csrd-sustainability-report/index.md)
- [Prescription with medication schedule](/examples/prescription/index.md)
- [DPE energy performance certificate](/examples/dpe-energy-performance-certificate/index.md)
- [EU energy label](/examples/eu-energy-label/index.md)
- [Master services agreement](/examples/master-services-agreement/index.md)
- [Nutrition Facts label](/examples/nutrition-facts-label/index.md)
