From Policy and Claim Records to a Frequency Table
A frequency analysis needs a response and a denominator that refer to the same exposure period and unit. Policy records describe when coverage was in force; claim records describe occurrences that may be counted against that coverage. The construction task is to align those records without losing partial terms, omitting policies with no claims, or counting one occurrence more than once.
The earlier material on coverage periods, exposure bases, claim counts, and frequency responses establishes the key ingredients. This workshop applies them as a record-by-record process. Every example is hypothetical and uses a calendar-year analysis window. Exposure is measured in policy-years using actual covered days divided by 365. No interest rate, mortality assumption, or probability distribution is needed: the examples are deterministic reconciliations of stated records.
Before counting, specify the analysis window and date convention. In this tutorial, the analysis year is the half-open interval from January 1, 2025, up to but not including January 1, 2026. A coverage interval is also half-open: its effective date is included and its termination or cancellation effective date is excluded. Thus, a policy cancelled effective July 1 provides coverage through June 30, not for July 1. For a policy interval \([a,b)\), only the overlap with the analysis window earns exposure.
This day-count convention is an explicit choice, not a universal rule for every insurance program. A production analysis must use the applicable contract and business conventions consistently. It must also use a denominator appropriate to the response: if the response counts occurrences for a given coverage, the exposure must represent that same coverage and time at risk.
A Record-Level Construction Workflow
A reliable build starts with the policy ledger, not with a claim-only extract. The policy ledger identifies the units that could have claims, including policies with none. Claim records are then assigned to eligible policy units and counted according to the selected occurrence definition. Use these steps in order:
Fix the analysis window, exposure base, response definition, and date convention. Decide which coverages and claim statuses are eligible.
For each eligible policy unit, intersect its covered interval with the analysis window. Convert the resulting days to the stated exposure base.
Use the defined claim-counting unit, such as a unique occurrence identifier, rather than automatically treating every payment or transaction row as a claim.
Match each occurrence to an eligible policy and verify its occurrence date falls within both the analysis window and the applicable covered interval.
Produce one row per chosen unit, retain eligible zero-count rows, and compare total counts and exposure with independently calculated source totals. Investigate exceptions before using the table.
The order matters. If the table is built from claims alone, a policy with no claim has no row to retain. If claims are joined to policies before policy exposure is reduced to the intended row grain, multiple claim rows can repeat the policy exposure. The final table should have the intended unit grain, not one row per payment, claim transaction, or coverage segment unless that is explicitly the chosen modeling unit.
Worked Construction Examples
Worked Example: Cancellations, Partial Terms, and a Zero-Claim Policy
A hypothetical insurer is constructing a 2025 occurrence-frequency table by policy. Use the half-open date convention and actual covered days divided by 365. The policy ledger contains: A, effective January 1, 2025, cancelled effective July 1, with two distinct covered occurrences dated March 10 and June 20; B, effective April 1, 2025, scheduled to expire January 1, 2026, with no claims; C, effective October 1, 2024, scheduled to expire October 1, 2025, with one covered occurrence dated February 5; and D, effective December 1, 2025, scheduled to expire December 1, 2026, with one covered occurrence dated December 18. Construct the table and calculate total frequency. All records are hypothetical; no interest, mortality, or claim distribution assumption is used.
Method and date calculations. Intersect each policy's coverage with January 1, 2025, through January 1, 2026. A's cancellation date is excluded, so its covered days are January through June: \(31+28+31+30+31+30=181\). B covers April 1 through the end of December: \(30+31+30+31+31+30+31+30+31=275\) days. C overlaps the window from January 1 through September 30: \(31+28+31+30+31+30+31+31+30=273\) days. D overlaps from December 1 through December 31: \(31\) days.
Count and assign occurrences. A has two distinct covered occurrences, B has zero, C has one, and D has one. Each listed occurrence date falls within the applicable policy coverage and the analysis year. Therefore the policy-level rows are:
| Policy | Covered days | Exposure (policy-years) | Claim count |
|---|---|---|---|
| A | 181 | 181/365 | 2 |
| B | 275 | 275/365 | 0 |
| C | 273 | 273/365 | 1 |
| D | 31 | 31/365 | 1 |
| Total | 760 | 760/365 | 4 |
Total exposure is \(760/365=2.0821918\) policy-years, and the total response is \(2+0+1+1=4\) occurrences. The portfolio frequency is \(4/(760/365)=1460/760=1.9210526\) occurrences per policy-year, or 1.921 occurrences per policy-year to three decimals. The table checks: each policy appears once, total days equal the sum of its four rows, and total claims equal the four distinct eligible occurrences. The mistake this method avoids is dropping B because it has no claim, which would understate exposure and distort frequency.
Worked Example: Multiple Claims Across Partial-Term Segments
A hypothetical policy is in force from January 1, 2025, and is cancelled effective October 1, 2025. The rating data require two rows because a classification changes effective July 1. The policy has one covered occurrence dated March 12 and one dated August 9. For each segment, calculate exposure using actual covered days divided by 365 and count occurrences by date. This is a deterministic record exercise; no interest, mortality, or probability distribution is assumed.
Method. The first segment is January 1 up to July 1. Its covered days are \(31+28+31+30+31+30=181\), and the March 12 occurrence belongs to it, so its response is 1. The second segment is July 1 up to October 1. Its days are \(31+31+30=92\), and the August 9 occurrence belongs to it, so its response is also 1. The cancellation effective October 1 excludes that date.
| Policy segment | Covered days | Exposure (policy-years) | Claim count |
|---|---|---|---|
| January 1 to July 1 | 181 | 181/365 | 1 |
| July 1 to October 1 | 92 | 92/365 | 1 |
| Total policy | 273 | 273/365 | 2 |
The segment exposures reconcile to the policy exposure because \(181+92=273\) days. The segment counts reconcile to the policy count because \(1+1=2\) occurrences. The frequency is \(2/(273/365)=730/273=2.6739927\), or 2.674 occurrences per policy-year to three decimals. Here, the segments are appropriate only because the requested table grain distinguishes the two rating periods. If a policy-level table were required instead, the single policy row would have 273 days of exposure and a count of 2. The common mistake is to use a full year of exposure merely because the original policy term was annual; cancellation limits the actual exposure.
Worked Example: Reconciling Transactions, an Out-of-Coverage Record, and Zero Claims
A hypothetical 2025 policy ledger contains P31, covered all year; P32, covered January 1 up to cancellation effective May 1; and P33, covered all year with no claims. A raw claim extract has two payment rows with occurrence ID K1 for P31, one row with occurrence ID K2 for P31, one row with occurrence ID K3 for P32 dated April 20, and one row with occurrence ID K4 for P32 dated June 3. A further occurrence ID K5 refers to P34, which is absent from the policy ledger. Construct a policy-level table. Treat repeated payment rows for the same occurrence as one occurrence, and do not count records outside coverage. The records are hypothetical; no financial discounting, mortality, or distribution assumptions apply.
Step 1: Calculate exposure. P31 and P33 each have 365 covered days. P32 has \(31+28+31+30=120\) covered days from January 1 up to May 1. Its June 3 date falls after cancellation. Thus, eligible exposure is \(365+120+365=850\) days, or \(850/365\) policy-years.
Step 2: Resolve the claim rows. The two K1 payment rows represent one distinct occurrence, not two. K2 is a second distinct P31 occurrence, so P31 has count 2. K3 is a distinct P32 occurrence within coverage, so P32 has count 1. K4 is outside P32's covered period and is flagged rather than counted. K5 cannot be assigned eligible exposure because P34 is not in the policy ledger; it is an unmatched-record exception requiring investigation, not a valid zero-exposure policy observation.
| Policy | Covered days | Exposure (policy-years) | Eligible distinct occurrences | Exception status |
|---|---|---|---|---|
| P31 | 365 | 365/365 | 2 | Two K1 transactions reduced to one occurrence |
| P32 | 120 | 120/365 | 1 | K4 is after cancellation |
| P33 | 365 | 365/365 | 0 | Retained with zero claims |
| Total eligible | 850 | 850/365 | 3 | K5 remains unmatched |
Frequency is \(3/(850/365)=1095/850=1.2882353\), or 1.288 occurrences per policy-year to three decimals. The arithmetic checks against three eligible distinct IDs (K1, K2, K3) and 850 covered days. The table should not be considered fully production-ready until K4 and K5 have been resolved or documented under an approved exception rule. The common mistake is counting every payment row as a separate claim, then also including a claim with no verified covered policy exposure.
Why the Aggregation Works—and How to Check It
When each record is assigned to exactly one eligible unit, total claim count is the sum of the unit counts, and total exposure is the sum of the unit exposures. The resulting portfolio frequency is the ratio of those totals. It can also be expressed as an exposure-weighted average of unit frequencies for units with positive exposure.
To justify the identity, distribute the exposure weight across each unit frequency: \(\left(E_j/\sum_k E_k\right)(N_j/E_j)=N_j/\sum_k E_k\). Summing over positive-exposure units gives \(\sum_j N_j/\sum_k E_k\). A zero-claim unit contributes \(N_j=0\) but may contribute positive exposure to the denominator. A unit with zero exposure cannot have an eligible covered occurrence under the stated convention and should be investigated rather than assigned an ordinary frequency.
This identity is also a practical diagnostic. If policy-level totals and segment-level totals do not agree, something has changed in the allocation: a date boundary may have been applied inconsistently, a claim may have been counted twice, or exposure may have been duplicated. The check does not prove every assignment is correct, but it reveals many common construction errors.
Common Traps in Frequency Data Construction
- Building only from claim records. This omits eligible policies with no claims. Start from the policy population and attach counts, so positive exposure with a zero response is represented.
- Using scheduled term instead of earned time. A policy scheduled for a year but cancelled partway through the analysis window earns only its covered overlap under the stated basis.
- Counting transactions rather than occurrences. Multiple payments or status records can refer to one occurrence. Apply the declared counting unit and deduplicate with the appropriate stable identifier.
- Ignoring date eligibility. A claim's presence in an extract does not establish that its occurrence date is within the analysis period and coverage interval. Test both conditions.
- Repeating exposure after a one-to-many join. If a policy has several claim rows, summing an unreduced policy exposure after joining can multiply the denominator. Aggregate to the intended unit grain before producing totals.
- Silently discarding exceptions. Unmatched policies, missing dates, negative intervals, and claims outside coverage should be flagged and resolved under a documented rule. They should not disappear unnoticed from a reconciled table.
Check Your Understanding
For each hypothetical problem, use the half-open date convention and actual covered days divided by 365 unless stated otherwise. Assume no interest, mortality, or probability distribution; report frequency to three decimals where requested.
- A policy is effective January 1, 2025, and cancelled effective July 1, 2025. It has one distinct covered occurrence in May. How many covered days, what exposure, and what count belong in its row? (Answer: 181 days; 181/365 = 0.4959 policy-years; count 1.)
- A policy is effective January 1, 2025, and cancelled effective April 1, 2025, with no claims. Give its exposure and response. Should it remain in the table? (Answer: 90/365 = 0.2466 policy-years; response 0; yes, retain it.)
- A policy is covered from January 1 up to cancellation effective October 1. A classification change creates a January 1–July 1 segment with one occurrence and a July 1–October 1 segment with two occurrences. Give total days, total count, and frequency. (Answer: 273 days; count 3; \(3/(273/365)=4.011\) occurrences per policy-year.)
- Three policies have 365, 120, and 90 covered days, and their eligible distinct claim counts are 2, 1, and 0. Calculate the portfolio exposure and frequency. (Answer: 575/365 = 1.5753 policy-years; total count 3; \(3/(575/365)=1.904\) occurrences per policy-year.)
- A full-year policy has two payment rows for occurrence R1 and one row for occurrence R2; a second full-year policy has no claims. Under a distinct-occurrence response, what are the total count, total exposure, and frequency? (Answer: count 2; exposure 730/365 = 2 policy-years; frequency 1.000 occurrence per policy-year.)