Actuary & risk analyst: MQL recipes

Recipes

Pure premium from frequency and severity
Simulated annual loss and 99.5% VaR
Deaths in a term life portfolio and risk capital
Pure premium of an excess of loss layer
Claims reserve with the chain-ladder method
Poisson fit of the claim counts
Lognormal severity, large claim probability and 99.5 pct quantile
Bayesian credibility of a fleet claim rate
Loss ratio drift with a z-test
Life annuity factor from a Gompertz-Makeham law

executed on MentDB

Pure premium from frequency and severity

Each policy has a Poisson claim frequency of 0.12 a year and a lognormal claim cost (mu 7.6, sigma 1.1). The script computes the mean claim, the expected loss, the probability of two claims or more, and the premium with a 25% loading.

Pipeline

01 · severityLognormal meanmath calc→02 · frequencyPoisson P(N >= 2)stat dist_cdf→03 · premiumLoaded premiummath round

Script

admin

#1. Claims per policy: Poisson 0.12 a year, severity lognormal mu 7.6 sigma 1.1;
-> "[lambda]" 0.12;
-> "[mu]" 7.6;
-> "[s]" 1.1;
#2. Mean claim cost and expected loss per policy;
-> "[sev]" (math calc "exp([mu]+[s]^2/2)");
-> "[el]" (math calc "[lambda]*[sev]");
#3. Probability of 2 claims or more in a year;
-> "[p2]" (- 1 (stat dist_cdf "poisson" (concat "[" [lambda] "]") 1));
#4. Pure premium plus 25% loading;
concat "Mean claim " (math round [sev] 2) ", expected loss " (math round [el] 2) " per policy, P(N >= 2) = " (math round_sig [p2] 3) ", commercial premium " (math round (* [el] 1.25) 2);

Result

mentdb

Mean claim 3659.2, expected loss 439.1 per policy, P(N >= 2) = 0.00665, commercial premium 548.88

Go further

Excess of loss: P(X > 20000) for one claim with stat dist_cdf lognormal
Fit the severity on observed claims with stat fit_model

executed on MentDB

Simulated annual loss and 99.5% VaR

The annual aggregate loss of a line of business follows a gamma law (shape 4, scale 250 k EUR). The script draws 10000 years, takes the mean and the 99.5% percentile (Solvency II VaR), checks it against the exact quantile, and derives the capital.

Pipeline

01 · simulate10000 annual lossesstat dist_random→02 · var99.5% percentilestat percentile→03 · checkExact quantilestat dist_inv→04 · shapeSkewness of the lossesstat skewness

Script

admin

#1. Simulate 10000 years of aggregate loss, gamma law, in k EUR;
-> "[sim]" (stat dist_random "gamma" "[4, 250]" 10000);
#2. Mean and 99.5% Value at Risk of the simulation;
-> "[mean]" (stat mean [sim]);
-> "[var]" (stat percentile [sim] 99.5);
#3. Exact quantile of the law, to check the simulation;
-> "[exact]" (stat dist_inv "gamma" "[4, 250]" 0.995);
concat "{\"mean\": " (math round [mean] 1) ", \"var_99_5_sim\": " (math round [var] 1) ", \"var_99_5_exact\": " (math round [exact] 1) ", \"capital\": " (math round (- [var] [mean]) 1) ", \"skewness\": " (math round (stat skewness [sim]) 3) "}";

Result

mentdb

{"mean": 996.1, "var_99_5_sim": 2649.3, "var_99_5_exact": 2744.4, "capital": 1653.2, "skewness": 0.952}

Go further

The simulation changes at each run: compare several runs or raise n to see the Monte Carlo error
Full picture of the simulated losses with stat summary or stat histogram

executed on MentDB

Deaths in a term life portfolio and risk capital

A portfolio of 2500 term life policies has a death rate of 4 per 1000 and a sum insured of 150000. The script uses the binomial law for the number of deaths: expected deaths, probability of more than 15 deaths, the 99.5% quantile and the capital above the expected claims.

Pipeline

01 · lawBinomial [n, q]concat→02 · tailP(D > 15)stat dist_cdf→03 · quantile99.5% deathsstat dist_inv→04 · capitalUnexpected claimsconcat

Script

admin

#1. Term life portfolio: 2500 insured, death rate 4 per 1000, 150000 each;
-> "[n]" 2500;
-> "[q]" 0.004;
-> "[si]" 150000;
-> "[law]" (concat "[" [n] ", " [q] "]");
#2. Expected deaths and probability of more than 15;
-> "[mean]" (* [n] [q]);
-> "[p15]" (- 1 (stat dist_cdf "binomial" [law] 15));
#3. 99.5% quantile of the deaths and risk capital;
-> "[d995]" (stat dist_inv "binomial" [law] 0.995);
concat "Expected deaths " [mean] ", P(D > 15) = " (math round_sig [p15] 3) ", 99.5% quantile " [d995] " deaths, capital " (* (- [d995] [mean]) [si]) " over the expected claims of " (* [mean] [si]);

Result

mentdb

Expected deaths 10, P(D > 15) = 0.0484, 99.5% quantile 19 deaths, capital 1350000 over the expected claims of 1500000

Go further

Poisson approximation: same quantile with stat dist_inv poisson [10]
Pandemic shock: add 1.5 per 1000 to q and compare the capital

executed on MentDB

Pure premium of an excess of loss layer

Claims follow a gamma law (shape 2, scale 5000 EUR). The limited expected values, written with the regularized incomplete gamma function, give the expected loss in the layer 10000 xs 5000 and its annual pure premium for 120 claims a year.

Pipeline

01 · levLimited expected valuesgamma_inc(a, x)→02 · meanMean claimgamma(x)→03 · hitClaims reaching the layermath calc→04 · premiumLayer pure premiummath calc

Script

admin

#1. Claim severity: gamma law, shape k = 2, scale 5000 EUR, and 120 claims a year;
-> "[k]" 2; -> "[th]" 5000; -> "[N]" 120;
#2. Limited expected value E of min X, u = k th P of k+1, u/th + u * 1 - P of k, u/th;
-> "[lev5]" (math calc "[k]*[th]*gamma_inc([k]+1, 5000/[th])+5000*(1-gamma_inc([k], 5000/[th]))");
-> "[lev15]" (math calc "[k]*[th]*gamma_inc([k]+1, 15000/[th])+15000*(1-gamma_inc([k], 15000/[th]))");
#3. Mean claim th gamma of k+1 / gamma of k, and the share of claims reaching the layer;
-> "[mean]" (math calc "[th]*gamma([k]+1)/gamma([k])");
-> "[hit]" (math calc "1-gamma_inc([k], 5000/[th])");
#4. Pure premium of the excess of loss layer 10000 xs 5000;
concat "mean claim = " [mean] " EUR, " (math round (math calc "100*[hit]") 1) " % of claims exceed 5000, expected layer loss per claim = " (math round (math calc "[lev15]-[lev5]") 2) " EUR, layer pure premium = " (math round (math calc "[N]*([lev15]-[lev5])") 0) " EUR/year"

Result

mentdb

mean claim = 10000 EUR, 73.6 % of claims exceed 5000, expected layer loss per claim = 4273.51 EUR, layer pure premium = 512822 EUR/year

Go further

Add an inflation of 5 %: multiply the scale and compute the leverage effect on the layer.
Check by simulation with stat dist_random "gamma".

executed on MentDB

Claims reserve with the chain-ladder method

The actuary has the cumulative paid claims triangle of four accident years. The script computes the development factors (volume weighted), projects the ultimate cost of each year from its latest diagonal and gives the reserve (IBNR) per year and in total.

Pipeline

01 · triangleLoad the trianglejson load→02 · factorsDevelopment factorsjson iarray→03 · ultimateProject the diagonaljson select→04 · reserveTotal reservestat sum

Script

admin

#1. Cumulative paid claims in kEUR, accident years 2022 to 2025 by development year;
json load "rx_tri" "[[1000, 1800, 2100, 2200], [1100, 2000, 2350], [1250, 2300], [1400]]";
-> "[n]" (json count "rx_tri" /);
json load "rx_f" "[]";
#2. Chain-ladder factor of each development year: sum of column j+1 over sum of column j, same years;
for (-> "[j]" 0) (< [j] (- [n] 1)) (++ "[j]") {
	-> "[num]" 0; -> "[den]" 0;
	for (-> "[i]" 0) (< [i] (- (- [n] 1) [j])) (++ "[i]") {
		-> "[num]" (+ [num] (json select "rx_tri" (concat "/[" [i] "][" (+ [j] 1) "]")));
		-> "[den]" (+ [den] (json select "rx_tri" (concat "/[" [i] "][" [j] "]")));
	};
	json iarray "rx_f" "/" (math round (/ [num] [den]) 4) NUM;
};
#3. Ultimate of each year = latest diagonal x remaining factors, IBNR = ultimate - paid;
json load "rx_res" "[]";
json load "rx_ibnr" "[]";
for (-> "[i]" 0) (< [i] [n]) (++ "[i]") {
	-> "[last]" (- (- [n] 1) [i]);
	-> "[paid]" (json select "rx_tri" (concat "/[" [i] "][" [last] "]"));
	-> "[ult]" [paid];
	for (-> "[j]" [last]) (< [j] (- [n] 1)) (++ "[j]") {-> "[ult]" (* [ult] (json select "rx_f" (concat "/[" [j] "]")));};
	json iarray "rx_ibnr" "/" (math round (- [ult] [paid]) 1) NUM;
	json iarray "rx_res" "/" (concat "{\"year\": " (+ 2022 [i]) ", \"paid\": " [paid] ", \"ultimate\": " (math round [ult] 1) ", \"reserve\": " (math round (- [ult] [paid]) 1) "}") OBJ;
};
-> "[out]" (concat "{\"factors\": " (string regexp_replace (json doc "rx_f") "\s" "") ", \"by_year\": " (string regexp_replace (json doc "rx_res") "\s" "") ", \"total_reserve\": " (math round (stat sum (json doc "rx_ibnr")) 1) "}");
json unload "rx_tri"; json unload "rx_f"; json unload "rx_res"; json unload "rx_ibnr";
[out]

Result

mentdb

{"factors": [1.8209,1.1711,1.0476], "by_year": [{"year":2022,"ultimate":2200,"paid":2200,"reserve":0},{"year":2023,"ultimate":2461.9,"paid":2350,"reserve":111.9},{"year":2024,"ultimate":2821.7,"paid":2300,"reserve":521.7},{"year":2025,"ultimate":3127.5,"paid":1400,"reserve":1727.5}], "total_reserve": 2361.1}

Go further

Add a tail factor after the last development year for long-tail lines such as liability
Measure the reserve uncertainty with the Mack formula or a bootstrap with stat dist_random

executed on MentDB

Poisson fit of the claim counts

The pricing actuary checks whether the claim counts of 40 motor policies follow a Poisson law. The script builds the frequency table, the dispersion index (variance over mean, 1 for Poisson), the expected counts lambda^n e^-lambda / Gamma(n+1) and the log-likelihood with lnGamma.

Pipeline

01 · tableFrequency tablestat frequency→02 · dispersionVariance / meanstat pvariance→03 · poissonn! as Gamma(n+1)math gamma→04 · likelihoodLog-likelihoodmath lngamma

Script

admin

#1. Number of claims of 40 motor policies last year;
-> "[k]" "[0,0,1,0,2,0,0,1,0,0,3,0,1,0,0,0,1,2,0,0,0,1,0,0,0,1,0,4,0,0,1,0,0,2,0,0,1,0,0,0]";
-> "[lam]" (stat mean [k]);
#2. Frequency table and dispersion index: a Poisson law has variance = mean;
json load "rx_f" (stat frequency [k]);
-> "[disp]" (/ (stat pvariance [k]) [lam]);
#3. Poisson probabilities lambda^n e^-lambda / Gamma of n+1 and the log-likelihood with lnGamma;
json load "rx_obs" "[]"; json load "rx_exp" "[]"; -> "[ll]" 0;
json parse_array "rx_f" "/" "[row]" {
	json load "rx_r" [row];
	-> "[n]" (json select "rx_r" "/value"); -> "[c]" (json select "rx_r" "/count");
	json iarray "rx_obs" "/" [c] INT;
	json iarray "rx_exp" "/" (math round (* 40 (/ (* (math power [lam] [n]) (math exp (* -1 [lam]))) (math gamma (+ [n] 1)))) 2) NUM;
	-> "[ll]" (+ [ll] (* [c] (- (- (* [n] (math log [lam])) [lam]) (math lngamma (+ [n] 1)))));
};
-> "[out]" (concat "lambda " [lam] ", dispersion " (math round [disp] 3) ", observed " (string regexp_replace (json doc "rx_obs") "\s" "") ", Poisson expected " (string regexp_replace (json doc "rx_exp") "\s" "") ", log-likelihood " (math round [ll] 3));
json unload "rx_f"; json unload "rx_r"; json unload "rx_obs"; json unload "rx_exp";
[out]

Result

mentdb

lambda 0.525, dispersion 1.618, observed [27,8,3,1,1], Poisson expected [23.66,12.42,3.26,0.57,0.07], log-likelihood -41.581

Go further

A dispersion of 1.6 and too many zeros and large counts point to a negative binomial law
Group the classes 3 and more before a stat chi2_test: expected counts under 5 make the test unreliable

executed on MentDB

Lognormal severity, large claim probability and 99.5 pct quantile

The actuary fits a lognormal law to 15 property claims (mean and standard deviation of the logs), then computes the probability that a claim exceeds 100000 EUR with the complementary error function, the 99.5 pct severity quantile with the inverse error function and the mean of the law.

Pipeline

01 · logsln of each claimmath log→02 · fitmu and sigmastat stddev→03 · tailP(X > 100k)math erfc→04 · quantile99.5 pct quantilemath erf_inv

Script

admin

#1. Property claims in EUR of the year, the severity is modelled by a lognormal law;
json load "rx_cl" "[4200, 12800, 2650, 31500, 7900, 5400, 88000, 15200, 3100, 9600, 21400, 6800, 47300, 11200, 2900]";
json load "rx_ln" "[]";
json parse_array "rx_cl" "/" "[x]" {json iarray "rx_ln" "/" (math log [x]) NUM;};
-> "[mu]" (stat mean (json doc "rx_ln")); -> "[s]" (stat stddev (json doc "rx_ln"));
#2. Probability that a claim exceeds 100000 EUR: 1/2 erfc of ln x - mu over sigma sqrt 2;
-> "[p_big]" (* 0.5 (math erfc (/ (- (math log 100000) [mu]) (* [s] (math sqrt 2)))));
#3. Quantile 99.5 pct of the severity with the inverse error function, and the mean of the law;
-> "[q]" (math exp (+ [mu] (* (* [s] (math sqrt 2)) (math erf_inv (- (* 2 0.995) 1)))));
-> "[mean]" (math exp (+ [mu] (/ (* [s] [s]) 2)));
json unload "rx_cl"; json unload "rx_ln";
concat "mu " (math round [mu] 4) ", sigma " (math round [s] 4) " | P claim over 100k " (math round (* [p_big] 100) 3) " pct | severity 99.5 pct quantile " (math round [q] 0) " EUR | mean " (math round [mean] 0) " EUR"

Result

mentdb

mu 9.2459, sigma 1.0446 | P claim over 100k 1.499 pct | severity 99.5 pct quantile 152738 EUR | mean 17880 EUR

Go further

The 99.5 pct quantile is the Solvency II level: compare it with the reinsurance retention
Check the fit of the logs with stat shapiro before trusting the tail

executed on MentDB

Bayesian credibility of a fleet claim rate

A transport fleet had 7 claims in 40 vehicle-years while the portfolio rate is 10 pct, taken as a Beta(3, 27) prior. The script computes the posterior rate, shows it is the Buhlmann credibility mix Z x own rate + (1-Z) x portfolio rate, and the probability of no claim next year for 12 vehicles with the Beta function.

Pipeline

01 · posteriorBeta posteriormath calc→02 · credibilityZ = n / (n + a + b)math round→03 · predictiveBeta-binomial P(0)math beta→04 · fixedFixed-rate comparisonmath power

Script

admin

#1. Portfolio prior on the yearly claim probability of a vehicle: Beta law with a = 3 and b = 27, mean 10 pct;
-> "[a]" 3; -> "[b]" 27;
#2. A transport fleet had 7 claims in 40 vehicle-years: Beta-binomial posterior;
-> "[n]" 40; -> "[k]" 7;
-> "[a2]" (+ [a] [k]); -> "[b2]" (+ [b] (- [n] [k]));
-> "[post]" (/ [a2] (+ [a2] [b2]));
-> "[Z]" (/ [n] (+ [n] (+ [a] [b])));
#3. Probability of no claim next year for 12 vehicles: B of a2, b2 + 12 over B of a2, b2;
-> "[p0]" (/ (math beta [a2] (+ [b2] 12)) (math beta [a2] [b2]));
concat "posterior claim rate " (math round (* [post] 100) 2) " pct = Z x " (* (/ [k] [n]) 100) " + 1-Z x 10 with credibility Z " (math round [Z] 4) " | check " (math round (* 100 (+ (* [Z] (/ [k] [n])) (* (- 1 [Z]) 0.1))) 2) " | P no claim for 12 vehicles " (math round [p0] 4) " vs " (math round (math power (- 1 [post]) 12) 4) " with a fixed rate"

Result

mentdb

posterior claim rate 14.29 pct = Z x 17.5 + 1-Z x 10 with credibility Z 0.5714 | check 14.29 | P no claim for 12 vehicles 0.181 vs 0.1573 with a fixed rate

Go further

The Beta-binomial probability 0.181 is higher than 0.157: the uncertainty on the rate widens the law
With more years of history Z goes to 1 and the fleet is priced on its own experience

executed on MentDB

Loss ratio drift with a z-test

A home insurance line was priced at a 65 pct loss ratio with a known monthly standard deviation of 6 points. The script runs a z-test on the 12 monthly loss ratios, computes the 95 pct critical value with the inverse error function and the spread observed this year, and gives a decision.

Pipeline

01 · testz and p-valuestat ztest→02 · criticalsqrt 2 x erf_inv(0.95)math erf_inv→03 · spreadPopulation variancestat pvariance→04 · decisionReprice or notif

Script

admin

#1. Monthly loss ratios in pct of a home insurance line, priced at 65 pct with a known monthly standard deviation of 6 points;
-> "[lr]" "[68.2, 71.5, 63.9, 74.8, 69.1, 66.4, 72.7, 70.3, 61.8, 75.6, 69.9, 73.4]";
-> "[target]" 65; -> "[sigma]" 6;
#2. z-test of the mean loss ratio against the pricing target;
json load "rx_z" (stat ztest [lr] [target] [sigma]);
#3. Two-sided 95 pct critical value from the inverse error function, and the spread observed this year;
-> "[zc]" (* (math sqrt 2) (math erf_inv 0.95));
-> "[obs_sd]" (math sqrt (stat pvariance [lr]));
-> "[out]" (concat "mean " (math round (stat mean [lr]) 2) " pct, z " (math round (json select "rx_z" "/z") 3) " vs critical " (math round [zc] 3) ", p-value " (math round (json select "rx_z" "/p_value") 6) ", observed spread " (math round [obs_sd] 2) " points | " (if (> (math abs (json select "rx_z" "/z")) [zc]) {"reprice the line";} {"no evidence of drift";}));
json unload "rx_z";
[out]

Result

mentdb

mean 69.8 pct, z 2.771 vs critical 1.96, p-value 0.005584, observed spread 4.05 points | reprice the line

Go further

The observed spread 4.05 is below the assumed 6: with stat ttest_1 the drift would be even clearer
Test the claim frequency and the average cost separately to find the source of the drift

executed on MentDB

Life annuity factor from a Gompertz-Makeham law

The pension actuary prices a life annuity of 12000 EUR a year for a person aged 65 at a 2 pct rate, with Gompertz-Makeham mortality mu(x) = A + B c^x. The script computes the survival probabilities with expm1 for c^t - 1, the probability to reach 85, the curtate life expectancy, the annuity factor and the single premium.

Pipeline

01 · survivalt p xmath expm1→02 · discountv^tmath power→03 · sumAnnuity factormath exp→04 · premiumSingle premiummath round

Script

admin

#1. Gompertz-Makeham mortality mu of x = A + B c^x, annuity of 1 EUR a year paid at the end of each year to a person aged 65, rate 2 pct;
-> "[A]" 0.0002; -> "[B]" 0.000018; -> "[c]" 1.105; -> "[x]" 65; -> "[v]" (/ 1 1.02);
#2. Survival t p x = exp of - A t - B/ln c . c^x . c^t - 1, with expm1 for c^t - 1;
-> "[ax]" 0; -> "[ex]" 0; -> "[lnc]" (math log [c]);
for (-> "[t]" 1) (<= [t] 55) (++ "[t]") {
	-> "[tpx]" (math exp (- (* -1 (* [A] [t])) (* (* (/ [B] [lnc]) (math power [c] [x])) (math expm1 (* [t] [lnc])))));
	-> "[ax]" (+ [ax] (* (math power [v] [t]) [tpx]));
	-> "[ex]" (+ [ex] [tpx]);
	if (== [t] 20) {-> "[p85]" [tpx];};
};
#3. Single premium of a pension of 12000 EUR a year;
concat "P survive to 85 " (math round [p85] 4) ", curtate life expectancy " (math round [ex] 2) " years, annuity factor a65 " (math round [ax] 4) ", single premium " (math round (* 12000 [ax]) 0) " EUR"

Result

mentdb

P survive to 85 0.4678, curtate life expectancy 18.28 years, annuity factor a65 14.6492, single premium 175790 EUR

Go further

Fit A, B and c on a national life table with stat fit_model before pricing
A 0.5 pct lower rate raises the premium: run the loop with v = 1/1.015 to see the interest sensitivity