Loss reserving is very important for property and casualty insurance companies. For insurance policies that cover all damages or injuries occurred during the insured period the claims may be made or fully regulated considerably after the insurance term. Future pay-outs have to be estimated for incurred but not reported (IBNR) claims to ensure that sufficient reserves are set aside that will cover the aggregate claim cost with a certain probability. The usual classification for IBNR is occurrence year versus reporting year and expected costs are determined for each combination. However, this does not give a sense of the distribution of costs over time nor their interdependence.

The VoseRunOff array function allows the stochastic modeling of costs over any desired period. Use this function to model a number N of payment events appearing at random points in time, where each event can take a random size. VoseRunOff then models the total amount of payment appearing at each year/month ... depending on the timestamps. The function parameters are as follows (using year as the nominal measure of time):

• TotalClaims (n) - the total number of claims predicted to occur from a policy in a certain occurrence year
• TimeObject - a distribution object describing the time from occurrence year until the year of payout
• TimeStamps - an array of increasing points in time at which the payouts will occur
• ClaimSizeObject - a distribution object describing the possible size of a claim

You can also use this function to just count the number of events (instead of their total size) happening at the timestamps by using a ClaimSizeObject that always returns 1 (E.g. VoseBernoulliObject(1))). The following examples are illustrated in different sheets of the Runoff example model.

Example 1 – Payout of random claims

A total of Poisson(121) claims are expected to occur from events occurring last year but not yet reported. The time until payout of a random claim follows a Lognormal(0.7,1.4) years distribution. The size of a random claim in $1000 follows a Pareto(5,2) distribution. We wish to model the payout per quarter for the next five years.

The example model sheet Runoff 1 illustrates how this is implemented in ModelRisk. Note that the VoseRunoff function extends one cell beyond the TimeStamp array to show the total cost of all claims that will occur beyond the last defined time point:

Example 2 - Payout time and claim size independence

The VoseRunoff algorithm assumes that the claim size and time until payout are considered to be independent. This may not be true. For example, where a very large claim occurs there may be some considerable dispute between the insured and insurer which could protract the time until payout. In this situation, it would be better to separate claims out into different strata where each stratum is a range of claim sizes with an appropriate Payout time distribution, and then sum the resultant payout streams, as shown in the example model sheet Runoff 2:

Using VoseRunoff with data

The VoseRunoff function uses Distribution Objects for the claim size and time until payment. Historic data will usually be available for these variables, in which case one can use fitted distribution objects to directly refer to the data, as shown in the example model sheet Runoff 3:

How VoseRunoff works

Imagine that the Poisson(121) generates a value of 115. The VoseRunoff function then generates 115 values sampled from the time until payout distribution (Lognormal(0.7,1.4) in this example) and 115 values from the claim size distribution (Pareto(5,2) in this example). It sorts each payout time into the bins defined by the TimeStamp array and sums the corresponding claim sizes for each bin to give the output values.

Can insurance reserving (IBNR) be modelled stochastically in Excel?

Yes. A stochastic reserving model needs three inputs: a distribution for the number of claims still to be paid, a distribution for the delay between occurrence and payment, and a distribution for the size of each claim. Run as a Monte Carlo simulation, the model returns a distribution of payouts for every future period instead of a single expected figure, so the reserve can be set at a stated confidence level — for example, the amount that covers the outstanding claims in 95% of the simulated outcomes.

The model on this page does that in an ordinary Excel workbook with ModelRisk's VoseRunoff function, and its three example sheets are free to download. Where claim history is available, the delay and claim-size distributions can be fitted to the data (sheet Runoff 3) rather than typed in. A deterministic IBNR table of occurrence year against reporting year gives the expected cost in each cell; the simulation adds what that table leaves out — how widely the cost can vary, and how it is spread over time.

Related insurance examples: the payout distribution for a policy with a deductible and limit, a tranched (layered) policy, premium calculations and a portfolio of correlated policies. To run any of them, download the free ModelRisk trial.