Validating Data and Calculation Implementation

Why Validate Data and Calculation Implementation?

A capital model can be conceptually suitable and still produce unreliable results if its source data are transformed incorrectly, its calculation logic is implemented incorrectly, or its outputs are reported incorrectly. Implementation validation checks whether the model does what its specification says it should do. It traces information from source records through transformations and calculations to reported results.

The checks should be designed to make errors observable. Reconcile counts and amounts at each data-processing stage; independently calculate selected outputs; test values at and around important boundaries; and rerun representative cases under controlled conditions. A check is most useful when it has a stated expected result and a clear explanation of what a difference would indicate. This is aligned with the CAS Exam 9 (Risk Management for Actuaries) syllabus.

Definition: Implementation validation is the testing of whether data handling, calculation procedures, code and reporting correctly implement the stated model specification. It is distinct from deciding whether the specification or its assumptions are appropriate.

Trace the Data Through Each Transformation

A data check should follow the record from its source to the model input, rather than compare only the final total. First define which records should be included and what each transformation does. Then check record counts, excluded records, units, totals and selected record-level calculations. A total that reconciles can still conceal offsetting errors, so aggregate checks should be supplemented with checks on individual records.

For a claim payment subject to an ordinary per-claim deductible $d$ and a payment limit $u$, let $z$ be the covered loss after any required currency and inflation adjustments. The payment is

Y^L=\min\bigl(\max(z-d,0),u\bigr).

This formula assumes the limit is the maximum insurer payment above the deductible. If the contract instead specifies a limit on the covered loss before applying the deductible, the formula must reflect that contract wording. Validation should test the implemented formula against the specification, not assume that every policy uses the same limit convention.

Worked Example: Reconciling Transformed Claim Data

A hypothetical portfolio has four source claim records. Three are valid claims; the fourth is void and must be excluded. The valid reported amounts are $8,000, $25,000 and $50,000. Their respective factors to the model’s valuation date are 1.10, 1.08 and 1.05. Each claim has a $10,000 deductible and a $40,000 payment limit above the deductible. Reconcile the valid records to the model’s total covered payment.

Method. Recalculate the adjustment and payment for each included record, then reconcile counts and totals. Do not apply the deductible or limit to the unadjusted source amount.

The valid-record count is 3, and the void-record count is 1. The sum of the three valid reported amounts is $8{,}000+25{,}000+50{,}000=\$83{,}000$.

For the first claim, the adjusted covered loss is $8{,}000(1.10)=\$8{,}800$. Its payment is $\min(\max(8{,}800-10{,}000,0),40{,}000)=\$0$.

For the second claim, the adjusted covered loss is $25{,}000(1.08)=\$27{,}000$. Its payment is $\min(\max(27{,}000-10{,}000,0),40{,}000)=\$17{,}000$.

For the third claim, the adjusted covered loss is $50{,}000(1.05)=\$52{,}500$. Its payment is $\min(\max(52{,}500-10{,}000,0),40{,}000)=\$40{,}000$, because the amount above the deductible is $42,500 and the payment limit is $40,000.

The adjusted covered-loss total is $8{,}800+27{,}000+52{,}500=\$88{,}300$. The model payment total is $0+17{,}000+40{,}000=\$57{,}000$. The check is complete only if the model contains three valid claims, excludes the void record, and reproduces both totals and the individual payments. A common mistake is to apply the limit before the deductible: for the third claim that would give $\min(52{,}500,40{,}000)-10{,}000=\$30{,}000$, not the specified $40,000 payment. The corrected order follows the stated contract: adjust the loss, subtract the deductible, then cap the payment.

Independently Recalculate Selected Outputs

An independent calculation is a check produced separately from the model code under review, using the same stated inputs and definitions. It may be a small hand calculation, a separate spreadsheet or a short script. Independence matters: copying the model’s code or formulas into another location can reproduce the same error rather than test for it.

For a finite scenario distribution with losses $L_j$ and probabilities $p_j$, where $p_j\geq 0$ and $\sum_j p_j=1$, the expected loss is the probability-weighted sum. Define the discrete $q$-quantile as the smallest loss whose cumulative probability is at least $q$:

E[L]=\sum_j p_jL_j,\qquad \operatorname{VaR}_q(L)=\inf\{x:P(L\leq x)\geq q\}.

The expectation follows from the definition of a probability-weighted average: each possible loss contributes its value multiplied by the probability of that outcome. The quantile definition implies a useful check: the cumulative probability immediately below the reported VaR must be less than $q$, while the cumulative probability at the reported VaR must be at least $q$. For a discrete distribution, the VaR need not have cumulative probability exactly equal to $q$.

Worked Example: Checking a Scenario-Based Capital Calculation

A hypothetical two-risk model produces four mutually exclusive aggregate-loss scenarios. Their probabilities and aggregate losses are: 0.50 at $0, 0.30 at $40,000, 0.15 at $100,000 and 0.05 at $200,000. Independently calculate expected loss, the 90% VaR and capital defined as 90% VaR less expected loss.

Method. Check that the probabilities sum to one, compute the weighted average, and accumulate probabilities in ascending order to find the quantile.

The probability total is $0.50+0.30+0.15+0.05=1.00$. Expected loss is $0.50(0)+0.30(40{,}000)+0.15(100{,}000)+0.05(200{,}000)$. The four contributions are $0, $12,000, $15,000 and $10,000, respectively, so $E[L]=\$37{,}000$.

The cumulative probabilities at losses $0, $40,000, $100,000 and $200,000 are 0.50, 0.80, 0.95 and 1.00. Since 0.80 is below 0.90 and 0.95 is at least 0.90, $\operatorname{VaR}_{0.90}(L)=\$100{,}000$. Capital under the stated definition is $\$100{,}000-\$37{,}000=\$63{,}000$.

The independent checks are a probability total of 1.00, expected loss of $37,000 and the required cumulative-probability bracket around VaR. A common mistake is to interpolate between $40,000 and $100,000 to force a cumulative probability of 0.90. That is not the specified discrete-quantile convention: the smallest loss reaching 0.90 is $100,000.

Use Boundary Tests and Invariants

A boundary test checks values where a change in calculation logic can alter the result: just below a deductible, exactly at it and just above it; at a limit and beyond it; or at a threshold used to classify or aggregate records. These tests are particularly effective for finding incorrect inequality signs, rounding at the wrong stage, and applying a cap to the wrong quantity.

An invariant is a relationship that must hold across calculations if the model’s stated definitions are implemented consistently. For a reinsurance layer, if gross loss equals net loss plus recovery, then the aggregate totals must satisfy the same identity, provided there are no other adjustments or components. This follows by adding the individual identities $G_j=N_j+R_j$ over claims $j$:

\sum_j G_j=\sum_j N_j+\sum_j R_j.

The identity does not validate the contract interpretation by itself. It is a calculation check conditional on that interpretation. It can reveal a missing recovery, a duplicated amount, or an inconsistent net-loss calculation.

Worked Example: Testing a Reinsurance Calculation at Boundaries

A hypothetical model applies a per-claim excess-of-loss layer with a $20,000 retention and a $50,000 maximum recovery per claim, followed by an $80,000 annual aggregate recovery cap. Calculate the recovery for a portfolio of losses $18,000, $42,000, $75,000, $100,000 and $100,000, and identify useful boundary checks.

Method. For each claim, calculate recovery as the loss above the retention, floored at zero and capped at $50,000. Then apply the annual aggregate cap to the sum of per-claim recoveries.

The individual recoveries are $\min(\max(18{,}000-20{,}000,0),50{,}000)=\$0$; $\min(\max(42{,}000-20{,}000,0),50{,}000)=\$22{,}000$; and $\min(\max(75{,}000-20{,}000,0),50{,}000)=\$50{,}000$. Each $100,000 claim also produces $\min(80{,}000,50{,}000)=\$50{,}000$. Thus the uncapped recovery total is $0+22{,}000+50{,}000+50{,}000+50{,}000=\$172{,}000$, and the capped annual recovery is $\min(172{,}000,80{,}000)=\$80{,}000$.

Gross loss is $18{,}000+42{,}000+75{,}000+100{,}000+100{,}000=\$335{,}000$. Net loss is $\$335{,}000-\$80{,}000=\$255{,}000$, and the invariant check is $\$255{,}000+\$80{,}000=\$335{,}000$.

Boundary tests should include losses of $19,999, $20,000 and $20,001, which should recover $0, $0 and $1; and losses of $69,999, $70,000 and $70,001, which should recover $49,999, $50,000 and $50,000. A common mistake is to apply the $80,000 annual cap separately to each claim. The cap applies once to the annual sum, so the portfolio recovery is $80,000, not $172,000.

Control Reruns and Reconcile Reported Results

A controlled rerun uses a documented input set and execution conditions, then compares the new results with an expected result or a prior run. For deterministic calculations, identical inputs and code should produce identical outputs. For simulations, the run configuration should record the random seed and other settings needed to reproduce the tested run. If the implementation intentionally uses nondeterministic computation, define an appropriate tolerance and explain it; do not dismiss unexplained differences as ordinary variation.

Compare more than the headline capital number. For a representative test case, retain checks such as input record counts, selected transformed values, module totals, aggregate loss measures and reported values. Differences should be traced to a specific stage, such as data selection, transformation, aggregation, rounding or reporting. Rounding should not hide a discrepancy: compare unrounded intermediate values where possible, and document the rounding rule applied to displayed amounts.

Fix the test case.
Record the input files, key settings and, for simulation, the seed and run configuration.

State expected checks.
Specify record counts, control totals, selected output values, tolerances and the relationships that must hold.

Run and compare.
Compare inputs and outputs at intermediate stages as well as at the final reported result.

Investigate exceptions.
Identify the first stage where the actual result differs from the expected result, then document the cause and resolution.

Validation effort is most effective when it combines checks with different failure modes. A total reconciliation can miss offsetting row errors; an independent hand calculation can check a selected case but not every code path; and a boundary test may reveal a limit error without showing whether the production data include the affected records. Together, these methods provide stronger evidence that the implemented process is operating as specified.

Key takeaway: Validate the path from source data to reported result. Reconcile counts and amounts, independently recalculate representative outputs, test boundaries and invariants, and rerun fixed cases under controlled conditions. Record expected values and investigate differences at the earliest stage where they appear.

Check Your Understanding

For each problem, show the intermediate checks before comparing your result with the answer.

A valid claim is reported as $36,000 and has an adjustment factor of 1.05. It is subject to a $10,000 deductible and a $30,000 payment limit above the deductible. What adjusted loss and payment should the transformed data contain? (Answer: Adjusted loss $37,800; payment $27,800.)
A discrete loss is $0 with probability 0.60, $40,000 with probability 0.25 and $100,000 with probability 0.15. Calculate expected loss and 80% VaR using the smallest-loss-reaching-the-quantile convention. (Answer: Expected loss $25,000; 80% VaR $40,000.)
A per-claim layer has a $25,000 retention and a $40,000 per-claim recovery limit. The annual aggregate recovery cap is $60,000. For claims of $15,000, $32,000 and $90,000, calculate uncapped recoveries, capped recovery and net loss. (Answer: Uncapped recoveries $0, $7,000 and $40,000; capped recovery $47,000; gross loss $137,000; net loss $90,000.)
A data file has 120 source rows, of which 4 are void and 6 are duplicate records that must be removed. No row is both void and duplicate. How many records should remain, and what is the first count reconciliation to check? (Answer: 110 records; verify $120-4-6=110$ and reconcile the included, void and duplicate counts to the 120 source rows.)
A model reports gross loss of $510,000 and recovery of $85,000, but net loss of $435,000. Apply the gross-equals-net-plus-recovery invariant and state the discrepancy. (Answer: Net loss should be $425,000; the reported net loss is $10,000 too high.)

What You'll Learn