Challenge
Annual rating agency surveys, including those required by Fitch, S&P, and AM Best, as well as frameworks like BCAR and RBC, are time-consuming, error-prone, and require diligence, consistency, and precision. While each survey and methodology is unique, they all depend on highly organized, well-mapped investment data. One Rivvit client, an insurance asset manager, needed to improve the structure and efficiency of their annual survey process. They faced several recurring obstacles, including:
- Disconnected data sources and spreadsheets
- No centralized mappings for key survey fields and customized sector buckets (e.g. ABS sector, non-US regions, DSCR groupings)
- Manual workflows for gathering and populating Microsoft Excel templates provided by the rating agencies
- Compressed timelines and manual efforts to meet survey deadlines
Solution
To bring structure and automation to the client’s process, we implemented a multi-step solution using Rivvit.
- Upstream normalization and consistency
We began by resolving inconsistencies as close to the source as possible. For example, one system might denote rate types as single-letter codes (F, L, V), while another uses more descriptive labels (Fixed, Floating, Variable). By standardizing this logic upstream, we reduced complexity and bottlenecks downstream and eliminated redundancy in both code and report parameters.
- Survey-specific mapping and lookup tables
Next, we created lookup tables for key survey fields (for example, Fitch’s corporate sub-types, ABS types, RMBS types, and Schedule BA classifications) and integrated those with existing investment sector and general ledger tags from the client’s investment and accounting books of record (IBOR and ABOR).
Similar logic was applied for S&P sectors (e.g., ABS, Muni, GICS) and AM Best-specific investment types. These tables ensured that assets were consistently categorized based on each agency’s unique grouping requirements, while maintaining flexibility to support internal reporting structures.
- Custom logic to derive survey fields
Where direct mappings didn’t exist, we built calculated fields in the client’s data mart using a mix of source values, flags, and custom logic. For instance, AM Best investment types were derived from a combination of public/private flags and SAA sectors. We also automated the creation of DSCR and LTV buckets and applied rules-based mappings for NAIC buckets. This made the data format-ready for survey submission, consistent day over day and from year to year.
- Standardized weighted average logic
To support survey requests requiring aggregate statistics, Rivvit developed standardized logic to calculate weighted metrics (e.g., book yield, yield to maturity, effective duration) at the holdings level. These were designed to roll up dynamically at any level of aggregation (sector, LTV bucket, NAIC rating) using appropriate weightings like book value or market value. This flexible structure allowed for a repeatable, audit-friendly process across multiple use cases.
- Report generation and on-demand access
Once the data was in good shape, we automated the survey data output process. The client could refresh survey data within the Rivvit UI as often as source data was updated (daily or even multiple times daily as accounting books were being reconciled) and formatted results could be exported directly for use in the Excel templates provided by rating agencies for submission.
Throughout the year, the Rivvit team collaborated with the client to reconcile upstream data, refine field logic, and enhance lookup tables, ensuring the solution was fully ready for year-end survey season. By the first quarter, the required survey data was readily available at the click of a button.
Results
As we explored in a previous article, Understanding BCAR and RBC: Key risk measures for insurance asset managers, clean and structured data is essential not only for BCAR and RBC, but also for recurring survey requirements. By investing in a flexible data foundation that is well-organized, mapped to rating agency expectations, and responsive to business needs, this client can reduce operational risk and stay agile under regulatory and rating agency scrutiny.
As a result of this project, the client experienced measurable improvements:
- Significantly reduced manual effort
- Reduced turnaround time from weeks to days
- Improved data quality, traceability, and consistency across reporting cycles
- Created a repeatable, scalable process for future rating agency requests