Perform a loss reserve analysis on P&C insurance loss development triangles. Use this skill whenever the user uploads loss triangles (paid or incurred), Schedule P data, or asks about reserve adequacy, reserve sufficiency, IBNR estimation, loss development analysis, loss reserve review, or ultimate loss projections. Also trigger when the user mentions chain ladder, Bornhuetter-Ferguson, Cape Cod, loss development factors, tail factors, or actuarial reserving methods. This skill reads triangles from Excel or CSV, runs multiple standard actuarial methods, diagnoses unusual patterns, and produces a formatted Excel report with exhibits. Even if the user just says 'check my reserves', 'run development on this triangle', or 'analyze my loss reserves', use this skill.
kalta-ai8 星标2026年3月18日
职业
分类
金融与投资
技能内容
Purpose
Analyze P&C insurance loss development triangles using standard actuarial reserving methods, flag anomalies, and produce a professional loss reserve analysis report in Excel. This skill applies standard methods described in Friedland's Estimating Unpaid Claims Using Basic Techniques and references relevant Actuarial Standards of Practice (ASOPs) for educational context.
When to Use
User uploads a loss triangle (Excel or CSV) and wants reserve analysis
User asks about IBNR, reserve adequacy, or ultimate loss estimates
User mentions chain ladder, BF, or loss development
User has Schedule P or similar statutory data
Workflow
Step 1: Identify and Parse the Triangle
Check /mnt/user-data/uploads/ for uploaded files
Read the file using the scripts/parse_triangle.py helper
If the data format is ambiguous, ask the user:
相关技能
Is this paid losses, incurred losses, or both?
What is the evaluation date?
Are the development periods in months or years?
Is there an earned premium column or sheet for BF/Cape Cod methods?
Per ASOP No. 23 (Data Quality), Section 3.2 (Selection of Data), consider whether the data is appropriate for the intended analysis. Ask the user about known data issues such as changes in claim coding, coverage triggers, or reopened claims that may affect triangle integrity. See Friedland Ch. 1–4 for background on the claims process, data types, and meeting with management to understand context.
Supported formats:
Standard triangle layout: rows = accident years/quarters, columns = development periods
Columnar layout: columns for accident period, development period, and loss amount
Transaction-level data: claim-level records with accident_date, evaluation_date, and loss amounts — automatically aggregated into a triangle
Schedule P format (will need manual identification of the relevant section)
Sample data (in examples/) for testing all methods:
sample_triangle.csv — 5×5 paid loss triangle (2019-2023)
sample_loss_transactions.csv — ~500 claims with paid, case reserve, and incurred columns
sample_premium.csv — earned premium by accident year (enables BF and Cape Cod methods)
Chain Ladder (Volume-Weighted) — Standard link ratio method using volume-weighted average factors (Friedland Ch. 7; foundational development method per ASOP No. 43 Section 3.6.1)
Chain Ladder (Simple Average) — Arithmetic average of age-to-age factors for comparison
Bornhuetter-Ferguson — If earned premium is provided; blends expected loss ratio with chain ladder (Friedland Ch. 9; Bornhuetter & Ferguson, PCAS 1972; incorporates credibility concepts per ASOP No. 25)
Cape Cod — If earned premium is provided; derives expected loss ratio from the data itself (Friedland Ch. 10; Stanard, PCAS 1985)
Expected Loss Ratio — If earned premium and an a priori loss ratio are provided (Friedland Ch. 8; useful when development data has limited credibility per ASOP No. 43 Section 3.6.1)
Step 3: Review Diagnostics
The script also produces diagnostic exhibits. Review these and call out to the user:
Calendar year development test: Checks if development along diagonals is consistent. Large swings may indicate reserve strengthening/weakening or operational changes.
High/low factor analysis: Flags individual age-to-age factors that are outliers (>2 standard deviations from the mean for that development period).
Tail factor sensitivity: Shows how ultimates change under ±10% and ±25% tail factor variations.
Incurred-vs-paid gap (if both triangles provided): Large divergence in later maturities may signal case reserve adequacy issues.
Residual plots: Standardized residuals from the chain ladder model to check for systematic patterns.
These diagnostics align with ASOP No. 43 Section 3.7.1 (Reasonableness), which calls for the actuary to assess whether results are reasonable. The calendar year test and outlier analysis help identify data irregularities referenced in ASOP No. 23 Section 3.3 (Review of Data). See also Friedland Ch. 6 for discussion of the development triangle as a diagnostic tool and Werner & Modlin Ch. 6 for loss development context in ratemaking.
Step 4: Rebuild Workbook with Formula Transparency
The script produces a workbook with static values. Before presenting, rebuild the workbook so every derived cell uses a live Excel formula with a cached calculated value. This is the minimum transparency standard for actuarial work — a reviewer must be able to click any cell and trace the calculation back to the source triangle.
Projected excess for each extrapolated period beyond the triangle: =EXP($slope_cell * period + $intercept_cell) — extend rows until projected < 0.0001 or max 20 extra periods
Projected ATA for each: =1 + projected_excess_cell
Tail factor: =PRODUCT(projected_ATA_range)
Guard condition: if slope >= 0 or fewer than 3 qualifying factors, set tail = 1.000 with a note explaining why (no decay detected or insufficient data)
The CDF chain's rightmost value must reference this tail factor cell, not a hardcoded number
What stays as static values (inputs, not derived):
Triangle data (raw input)
Earned premium (user input)
A priori ELR for BF (user input or derived assumption)
Important: Every formula cell must also have a cached calculated value so numbers display correctly in all viewers (web preview, Excel, Google Sheets). Use xlsxwriter's write_formula(row, col, formula, format, result) or equivalent approach that writes both the formula and the pre-calculated value.
Step 5: Interpret Results and Present
After running the analysis:
Copy the output Excel to /mnt/user-data/outputs/
Present the file to the user
Provide a narrative summary that includes:
The range of indicated ultimates across methods
Whether carried reserves appear adequate, deficient, or redundant relative to the chain ladder indication
Which accident years show the most development volatility
Any red flags from the diagnostics
Caveats about data limitations and the quick-check nature of the analysis
Reference the concept from ASOP No. 43 Section 3.7 (Unpaid Claim Estimate) and 3.7.1 (Reasonableness) — note that a point estimate is a selection, and the actuary should assess whether results are reasonable
Important tone guidance: This is a quick check, not a full reserve study. Always caveat that:
The analysis relies on standard methods and does not incorporate claim-level information
Tail factor selection is mechanical and should be reviewed by a credentialed actuary
Results should be cross-referenced with operational knowledge (e.g., changes in claims handling, coverage, or legal environment)
This does not constitute an actuarial opinion under ASOP No. 43 or a Statement of Actuarial Opinion per ASOP No. 36. A credentialed actuary should review results, apply professional judgment on factor selections, and consider the requirements of ASOP No. 43 Section 3 before relying on these estimates for decision-making
Step 5: Interactive Follow-Up
After presenting results, offer the user options to:
Adjust tail factors manually
Exclude specific accident years or development periods
Change the weighting scheme for age-to-age factors
Run sensitivity scenarios on the a priori loss ratio (for BF/Cape Cod)
Compare against a benchmark loss ratio
When adjusting selections, note that ASOP No. 43 Section 3.6.7 (Changing Conditions) and 3.6.6 (External Conditions) may affect development patterns and tail behavior. ASOP No. 25 provides guidance on credibility weighting when blending methods. Tail factor considerations are discussed in Friedland Ch. 7 as part of the development technique.
Key Actuarial Concepts (Reference)
Read references/methods.md for detailed formulas and implementation notes on each method if needed during development or debugging.
Error Handling
If the triangle has fewer than 3 accident periods, warn that credibility is very limited. ASOP No. 25 Section 3.4 (Professional Judgment) notes that credibility assessment is not always a precise mathematical process; triangles with very few data points may warrant heavier reliance on the ELR or BF methods with an externally-derived expected loss ratio
If any development factors are < 1.0 (negative development), flag explicitly — it may be legitimate but warrants explanation
If the triangle is not square (jagged), handle gracefully by only computing factors where data exists
If earned premium is not provided, skip BF and Cape Cod methods and note this in the output