Expected aggregate stop-loss payment under a normal approximation

Gives the expected excess of total net claims over the aggregate attachment point, the net stop-loss premium before loading, when total claims are approximately normal. phi_z is the standard normal density at z, written out with exp and sqrt, and Q_z is the standard normal upper-tail probability 1 minus Phi(z), entered from Excel's 1-NORM.S.DIST(z,TRUE) because the calculator has no normal distribution function. The formula has no maximum aggregate payment.

Signature

z = (A - E_S) / SD_S; phi_z = exp(-z * z / 2) / sqrt(2 * 3.14159265); EL = SD_S * (phi_z - z * Q_z)
Inputs
InputsDefinitionUnit
AAggregate attachment point for the contract yeardollars
E_SExpected annual net claims of the groupdollars
SD_SStandard deviation of the group's annual net claimsdollars
Q_zProbability that a standard normal variable exceeds z, equal to 1 minus Phi(z) and to the normal approximation to the chance of reaching the attachment pointprobability
Output
zNumber of standard deviations by which the attachment point lies above expected claimsstandard deviations
phi_zValue of the standard normal density function at znone
ELExpected excess of total net claims over the attachment point, the carrier's expected paymentdollars

Function

Aggregate stop-loss payment and expected payment function

Maps a self-funded employer's eligible claims for the contract year, net of specific stop-loss recoveries, to the payment due from the aggregate stop-loss carrier and the claims the employer retains, given the aggregate attachment point and any maximum aggregate payment. Applied to the distribution of the year's claims instead of one realised year, the same rule gives the carrier's expected payment, the net stop-loss premium before any loading. The records follow the notation of the Aggregate Stop-Loss article, where S is net claims for the year, A is the aggregate attachment point and L is the carrier's payment.

Computational function

  • Computational function: group size and attachment factor to expected aggregate stop-loss payment

    Takes the inputs an analyst usually holds about a self-funded group, the number of members, the mean and standard deviation of net claims per member and the attachment factor, and returns the carrier's expected aggregate stop-loss payment and its share of expected claims. It chains HE-FM-ASL-003 (pooled mean and standard deviation), HE-FM-ASL-002 (attachment point), HE-FM-ASL-004 (standardised distance) and HE-FM-ASL-005 (normal expected excess), so its inputs differ from the symbols of the expected payment formula, which needs the attachment point and the distribution of total claims. The calculator version takes Q_z as an input because it has no normal distribution function; the Excel, R and Python versions compute it.

    Inputs and outputs: n: Number of covered members; required, above zero. Unit: count.; mu: Expected annual net claims per member; required, above zero. Unit: dollars per member.; sigma: Standard deviation of annual net claims per member; required, above zero. Unit: dollars per member.; a: Attachment factor, 1.25 for 125% of expected claims; required. Unit: ratio.; Q_z: Standard normal upper-tail probability at the returned z, 1-NORM.S.DIST(z,TRUE); calculator input only. Unit: probability.; E_S: Expected total net claims, returned. Unit: dollars.; SD_S: Standard deviation of total net claims, returned. Unit: dollars.; A: Aggregate attachment point, returned. Unit: dollars.; z: Standardised distance to the attachment point, returned. Unit: standard deviations.; phi_z: Standard normal density at z, returned. Unit: none.; EL: Expected aggregate stop-loss payment. Unit: dollars.; EL_share: Expected payment as a share of expected claims. Unit: proportion.

    Assumption: Members' net claims are independent with a common mean and standard deviation, total claims are approximately normal, and the policy has no maximum aggregate payment. Skewed claims and common shocks make the result understate the expected payment, most of all for small groups.

    Worked example (Employer 1, 200 members at 125% of expected claims): The article's larger group gives an expected payment of about $3,283, about 0.22% of expected claims. n = 200; mu = 7500; sigma = 15000; a = 1.25; Q_z = 0.03854993; E_S = 1500000; SD_S = 212132.03; A = 1875000; z = 1.767767; phi_z = 0.083623; EL = 3282.86; EL_share = 0.002189

    Worked example (Employer 2, 20 members at 125% of expected claims): The same rule for the smaller group gives about $12,088, about 8.1% of expected claims. n = 20; mu = 7500; sigma = 15000; a = 1.25; Q_z = 0.28807506; E_S = 150000; SD_S = 67082.04; A = 187500; z = 0.559017; phi_z = 0.341233; EL = 12087.82; EL_share = 0.080585

    Worked example (Attachment point at expected claims): With a factor of 1, z is zero and the expected payment is the standard deviation of total claims times the normal density at zero, about 0.3989, a limiting case that checks the implementation. n = 200; mu = 7500; sigma = 15000; a = 1; Q_z = 0.5; E_S = 1500000; SD_S = 212132.03; A = 1500000; z = 0; phi_z = 0.398942; EL = 84628.44; EL_share = 0.056419

    Excel: =LET(es,Members*MeanMember,sdv,SDMember*SQRT(Members),zv,(AttachFactor*es-es)/sdv,sdv*(NORM.S.DIST(zv,FALSE)-zv*(1-NORM.S.DIST(zv,TRUE)))) With named cells Members, MeanMember, SDMember and AttachFactor, the formula returns the expected payment in Excel 2021 or Microsoft 365; dividing by Members*MeanMember gives the share.

    R: agg_sl_expected <- function(n, mu, sigma, a) { es <- n*mu; sdv <- sigma*sqrt(n); z <- (a*es-es)/sdv; sdv*(dnorm(z)-z*(1-pnorm(z))) } Uses dnorm and pnorm from base R and is vectorised over n, so one call covers several group sizes.

    Python: def agg_sl_expected(n, mu, sigma, a): es = n*mu; sdv = sigma*math.sqrt(n); z = (a*es-es)/sdv; nd = statistics.NormalDist(); return sdv*(nd.pdf(z)-z*(1-nd.cdf(z))) Needs the standard library modules math and statistics only.

    Test (Expected excess less expected shortfall equals expected claims less the attachment point): With the function's result in a cell named ExpPayment, the identity holds under the normal approximation. Expected result: TRUE. Excel check: =LET(es,Members*MeanMember,sdv,SDMember*SQRT(Members),zv,(AttachFactor*es-es)/sdv,ABS(ExpPayment-sdv*(NORM.S.DIST(zv,FALSE)+zv*NORM.S.DIST(zv,TRUE))-(es-AttachFactor*es))<1E-6)

    Test (Expected payment at least the excess of expected claims): The expected payment is never below zero or below expected claims less the attachment point. Expected result: TRUE. Excel check: =ExpPayment>=MAX(Members*MeanMember*(1-AttachFactor),0)

    Common error (Using one attachment percentage as if it meant the same protection for every group): At 125% of expected claims the expected payment is about 0.22% of claims for 200 members and about 8.1% for 20 members. Comparing quotes or modelling exposure by the percentage alone ignores how the spread of total claims shrinks with group size.

    Source: Rossi R, Tarim SA, Prestwich S, Hnich B. Piecewise linear approximations of the standard normal first order loss function. arXiv:1307.1708v4; 2013 (published in Applied Mathematics and Computation. 2014;231:489-502). Lemma 9, the closed form of the expected excess E[max(omega minus x, 0)] of a normal variable, and Lemma 8 for the complementary function used in the first test.

    E_S = n * mu; SD_S = sigma * sqrt(n); A = a * E_S; z = (A - E_S) / SD_S; phi_z = exp(-z * z / 2) / sqrt(2 * 3.14159265); EL = SD_S * (phi_z - z * Q_z); EL_share = EL / E_S

Try this function

Implementations

  • Excel

    Normal expected excess over the aggregate attachment point in one cell

    With the standardised distance in a cell named ZScore and the standard deviation of total claims in SDClaims, Excel returns the expected excess. NORM.S.DIST with FALSE gives the density and with TRUE the cumulative probability.

    =SDClaims*(NORM.S.DIST(ZScore,FALSE)-ZScore*(1-NORM.S.DIST(ZScore,TRUE)))

Assumptions

  • Normal total claims and no maximum aggregate payment

    Total net claims are approximately normal with mean E_S and standard deviation SD_S, and the policy has no maximum aggregate payment. Skewed claims and common shocks make the normal result understate the expected excess, most of all for small groups.

  • Net premium before loading for aggregate stop-loss

    EL is the expected cost of claims to the carrier. The premium charged adds a loading for administrative costs, the cost of capital tied up by irregular payments and any risk aversion of the carrier, so the premium exceeds EL.

Worked examples

  • Expected aggregate stop-loss payment for a 200-member group

    For Employer 1 the expected excess is about $3,283, about 0.22% of expected claims of $1,500,000. The article's $3,282 multiplies by the bracket rounded to 0.01547.

    A = 1875000; E_S = 1500000; SD_S = 212132.03; Q_z = 0.03854993; z = 1.767767; phi_z = 0.083623; EL = 3282.86

  • Expected aggregate stop-loss payment for a 20-member group

    For Employer 2 the expected excess is about $12,088, about 8.1% of expected claims of $150,000, so the same 125% rule transfers a far larger share of the smaller group's risk.

    A = 187500; E_S = 150000; SD_S = 67082.04; Q_z = 0.28807506; z = 0.559017; phi_z = 0.341233; EL = 12087.82

Common errors

  • Applying the payment rule to expected claims

    Putting expected claims into the payment rule gives the larger of 1,500,000 less 1,875,000 and zero, a payment of zero for Employer 1 and also zero for Employer 2. The expected payments are about $3,283 and $12,088, because the carrier pays in the high-claims years and never pays a negative amount in the others.

  • Using the cumulative normal in place of the density

    Writing NORM.S.DIST(ZScore,TRUE) in the first term gives about $189,498 for Employer 1 instead of about $3,283, more than 12% of expected claims for a layer reached about one year in 26.

Sources

  • Closed form of the first order loss function of a normal variable

    Rossi R, Tarim SA, Prestwich S, Hnich B. Piecewise linear approximations of the standard normal first order loss function. arXiv:1307.1708v4; 2013. Published as: Piecewise linear lower and upper bounds for the standard normal first order loss function. Applied Mathematics and Computation. 2014;231:489-502 (doi:10.1016/j.amc.2014.01.019). Equation 1 defines the first order loss function as the expected value of max(omega minus x, 0), the expected excess over x; Lemma 9 gives its closed form for a normal variable with mean mu and standard deviation sigma as sigma times phi((x minus mu)/sigma) minus (1 minus Phi((x minus mu)/sigma)) times (x minus mu)/sigma, and Lemma 8 gives the complementary function used in the validation test.

    View source →

  • Arrow on the loading of insurance premiums

    Arrow KJ. Uncertainty and the welfare economics of medical care. American Economic Review. 1963;53(5):941-973. Section IV.B on the reasons for loading the premium above the expected payment: administrative costs, the cost of capital tied up by irregular payments and any residual risk aversion of the insurer.

    View source →

Canonical Identity

Persistent URI
https://healtheconomics.wiki/formula/expected-aggregate-stop-loss-payment-under-a-normal-approximation

Stable URI · Machine-readable · Resolvable · CC BY 4.0