Three large North American insurers providing traditional life, health, and annuity products.
Develop an automated Excel-based tool to replicate AXIS reserves and cash flows for a model validation program and to increase transparency and understanding of AXIS calculations.
Discovery and Design Phase
Identify insurance product types to be replicated:
- Whole life
- Long-term care
- Variable annuities
- Fixed annuities
Identify calculations to be replicated:
- Seriatim cash flow projections
- Seriatim reserve calculation
- Gross premium valuation
- One-year and two-year FPT
- AG43 Standard Scenario
Determine calculations to be validated through separate testing:
- Cohort-level calculations building on seriatim calculations
Discuss options for validation of model assumptions:
- Option 1: Assume that the AXIS seriatim/cell assumptions have been set up correctly
- Low maintenance and allows for automated flow of assumptions from AXIS model into replication model
- Assumption validation to be done as a separate exercise, typically by junior internal staff
- Option 2: Apply assumptions independently of AXIS model
- Requires more maintenance for model updates
- End-state solution typically validates targeted assumptions where practical
Determine testing sample policy set:
- Typically, one policy per model cell
- Covers all product/policy combinations
Replication model development:
- Start with AXIS replication tool template model that contains calculations that apply for most products
- Policy movement
- Death Benefits
- Policy movement
- Customize tool for client’s specific block of business
- Execute processes for populating back-end databases with AXIS policy and assumption data
- Develop AXIS replication tool to allow client to project cash flows and reserves for any policy and scenario on demand
- Develop process to run reconciliation for selected subset of sample policies/scenarios
Navigate and respond to challenges:
- Some reserve calculations require several projections (e.g. AG43 standard scenario)
- Develop an automated process to combine required elements from projections
- Some products require substantial computational complexity, e.g. disability products require tracking each incurral month cohort
- Develop an automated process to process disability policies while maintaining efficient calculation time and workbook size
- Develop AXIS replication tool with Excel best practices to minimize calculation runtime and facilitate model maintenance
Deliver AXIS replication tool:
- Deliver a complete AXIS replication tool (Excel spreadsheet and backend databases) that allow client to independently validate cash flow and reserve calculations for all policies
- Provide an automated process to run a reconciliation process for a selected set of sample policies
Deliver user guides and model documentation, including:
- In-model documentation with comment descriptions of all model calculations
- Detailed user guides for model usage and maintenance
Provide training and change management support:
- Train staff so that the model can be maintained in-house
Results included the following:
- Required elements of the model validation plan were satisfied by ensuring that AXIS model calculations are accurate.
- Transparency of AXIS calculations was greatly improved, such that all model calculations can be drilled down within the replication tool back to source assumptions.
- The replication exercise increased understanding of AXIS calculation methodology, an outcome that was particularly helpful for new AXIS users.
- Increased transparency allows for easier fulfillment of internal and external audit requests, including very quick provision of sample policy calculation details.
- AXIS model setup issues were identified and remediations recommended.