Skip to content

Triangles

Download File Here

A friendly, step-by-step guide to extract, explore, and analyse Triangles data using the provided Excel workbook.

Formulas for This Tutorial

These are the main formulas you’ll need to work through this tutorial:

=ANT.Live.Premium_Triangles()
=ANT.Live.Event_Name_Triangles()
=ANT.ACT.Mappings.get_class_mapping()

For more information on the inputs and outputs of these formulas, you can read more about them on the relevant documentation page.

Triangles Formulas

Quick Summary

Get the live Triangles table from Antenna API, convert it to a stable Power Query table, add helper columns, and build automated views (YTD, current month, quarters) for reporting.

Before you start

Note

Prerequisite: Antenna installed (see the Instructions page in the workbook).

Step 1 - Extract the dataset (fast win)

Goal: Run the AOD formula once and turn the result into a Power Query table you can safely refresh.

What you'll do:

  1. Activate the AOD formula in the workbook
  2. Convert the resulting range to a Table
  3. Load via Power Query and name it for later use

Steps:

  1. Open the Class Mapping - AOD tab.
    Find cell A1 — it currently contains the formula commented out. Before: AOD formula commented out

  2. Remove the leading apostrophe ' from cell A1 to enable:

  3. Click cell A1, then go to: Data → From Table/Range to open Power Query.

    Data table range

  4. In Power Query: rename the source (e.g., FromArray_1 → Class Mapping) then click Close & Load.

    Power query renaming

    Tip

    The Class Mapping table here is being created as a utility table to be used in other formulas. When editing the data on Power Query you could select only the class you are concerned with at the time.

    Why convert to a Table?

    • Tables protect your report from unexpected column-order or count changes.
    • You control when and how the data refreshes (the formula itself refreshes automatically on open).

  5. Filter for Marine Data: To demostrate we will be working with Marine major class. Select Major Class → Text Filters → Equals → Marine

    Major Class Filter

  6. Uncomment the formula on the ‘Premium Triangles - AOD’ and the ‘Claims Triangles - AOD’ tabs. Triangles formula

    You can see from these formulas that they both use the Class Mapping table that we created in the preceeding steps.

    Repeat the steps for converting the output into actual tables like we did for the Class Mappings outputs.

    You should now see the following tables, on your Power Query editor: Final tables

Options for the original tab:

  • Option 1 (recommended): Hide the - AOD tab if you plan to refresh regularly.
  • Option 2: If your dataset is large (>10k rows), comment the formula again and hide the tab to speed up workbook open times.

Tip

Clear naming tip: append - AOD to extraction tabs (e.g., Premium Triangles - AOD) so it's obvious which sheet is the source.

Step 2 - Exploring Aggregation Levels

Goal: Modify Triangle Data - AOD formula flags to change data granularity, then refresh the Triangle Data table to review results in the “2. Fields” tab.

You can modify parameters of the analytics functions for various aggregation level of your data.

Using the other parameters in the formula

Before: AOD formula commented out

An example in which the show entities parameter has been enabled is shown above. The data will be provide split at entity level.

Instructions 🧩

  • 1️⃣ Set show_mgas = TRUE → refresh → check the “2. Fields” tab (shows MGA-level data).
  • 2️⃣ Set show_entities = TRUE → refresh → shows Entity level split (i.e. split by FUL, FIID, FIBL).
  • 3️⃣ Set show_brokers = TRUE → refresh → view Broker-level data (the metrics according to the various Broker groupings in the data).

Note

We designed the hierarchy so that MGAs are the highest level of aggregation, followed by Entities, then Brokers. Each level assumes that the user also wants to see all higher-level groupings.

  • If show_entities = True, the output will include Entities and their parent MGAs.
  • If show_brokers = True, the output will include Brokers along with their related Entities and MGAs.

An additional parameter for the triangle formulas is triangle_type.
You can select from the following options: reserving, premium, or binder.

Tip

The difference between premium or reserving and binder is:

  • binder: Typically used for MGAs, with triangles developed from the inception of the binder.
  • premium and reserving: Developed from the standard underwriting year.
Option Available Formulas Description
binder All triangle formulas Provides triangle data related to binders
premium =ANT.Live.Premium_Triangles() Provides premium-related triangle data
reserving =ANT.Live.Event_Name_Triangles() =ANT.Live.Reserving_Triangles() Provides reserving-related triangle data

Step 3 - Using the Data

Navigating to the "Using the Data" sheet you can see a summary that was created from the result, you can see a template simple triangle report for all the classes being considered, you can pick different metrics you want to look at from both the Premium Triangles output and from the Claim_Triangles output. A summary definition of the fields you can select from are provided below.

Metric Fields Definition
Incremental Gross Gross Written Premium USD Gross of reinsurance gross of acquisition costs written premium in USD
Incremental Gross Net Written Premium USD Gross of reinsurance net of acquisition costs written premium in USD
Incremental Gross Gross Earned Premium USD Gross of reinsurance gross of acquisition costs earned premium in USD
Incremental Gross Net Earned Premium USD Gross of reinsurance net of acquisition costs earned premium in USD
Incremental Gross Paid Claims USD Gross Paid Claims in USD
Incremental Gross Incurred Claims USD Gross Incurred Claims in USD (Paid + Outstanding)

Step 4 - Exploring Aggregation Levels

Prerequisite: Step 2 ✅

Goal: Modify Premium Data - AOD formula flags to change data granularity, then refresh the Premium Data table to review results in the “2. Fields” tab.

You can modify parameters of the analytics functions for various aggregation level of your data.

Using the other parameters in the formula

Before: AOD formula commented out

An example in which the show entities parameter has been enabled is shown above. The data will be provide split at entity level.

Instructions 🧩

  • 1️⃣ Set show_mgas = TRUE → refresh → check the “2. Fields” tab (shows MGA-level data).
  • 2️⃣ Set show_entities = TRUE → refresh → shows Entity level split (i.e. split by FUL, FIID, FIBL).
  • 3️⃣ Set show_brokers = TRUE → refresh → view Broker-level data (the metrics according to the various Broker groupings in the data).
  • 4️⃣ Set show_layer_ids = TRUE → ensure class_ids is not blank ⚠️ → refresh → view data at the lowest level.

Why did the actuary break up with their loss triangle? Because it kept bringing up old development years! 😂

Feedback 💬

Go to this page to provide feedback on this tutorial

Triangle Tutorial