Case study AXIS Replication Case Study

The Client

Three large North American insurers providing traditional life, health, and annuity products.

The Ask

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.

Project Breakdown

Discovery and Design Phase

Identify insurance product types to be replicated:

  • Term
  • Whole life
  • Par
  • Long-term care
  • Variable annuities
  • Fixed annuities

Identify calculations to be replicated:

  • Seriatim cash flow projections
  • Seriatim reserve calculation
    • Gross premium valuation
    • CRVM
    • One-year and two-year FPT
    • FAS60
    • AG33
    • AG34
    • 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

Development Phase

Replication model development:

  • Start with AXIS replication tool template model that contains calculations that apply for most products
    • Policy movement
      • Mortality
      • Lapses
      • Premiums
      • Death Benefits
  • 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

Delivery Phase

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.