VoseDepletion

VoseDepletion(ClaimInterval,ClaimSize,Resource,Horizon)

Array function used to determine whether or not, starting from a certain amount of money (Resource), you default (exhaust the resource) before a certain time horizon (Horizon) when claims with a certain size (ClaimSize) come in at a certain rate (ClaimInterval). The parameters ClaimInterval and ClaimSize can be either distribution objects or fixed numbers. If they are distribution objects the function will select a random sample for each individual claim and/or interval between each claim.

The generated output is a 3 by 2 array with on the first row the DepletionTime, on the second row the DepletionFlag and on the last row the DepletionShortfall.

Example

Suppose an insurance fund has $1 000 000 in cash to cover the cost of a life insurance policy it has now terminated. The company wants to know if the $1 000 000 (Resource) will be enough to cover the claims coming in at a certain rate (ClaimInterval) for the next 2 years (Horizon). The VoseDepletion function can simulate if and when the resource will run out within that time Horizon. Suppose the rate at which the claims come in follows a Poisson process with a mean of 20 days between each claim, giving a ClaimInterval = VoseExpon(20) days. Suppose the claim size follows a LogNormal(25 000,4 000) distribution. The outputs of interest for the insurance company can now be modelled with the function:

VoseDepletion(VoseExponObject(20),VoseLogNormalObject(25000,4000),1000000,2*365)

This function covers a 3 x 2 array. It will typically generate the following types of outputs:

which means that in this iteration the fund was not exhausted within two years; or

Which means that in this iteration the fund was exhausted at day 727 and the fund was short $6581 at that moment.

Although the terminology used for this function is insurance related, the conceptual model has many more applications. For example, you are a health authority with a stockpile of 80 000 vaccine shots. Infections occur in random outbreaks with mean time between outbreaks of 120 days. Each outbreak requires Gamma(3,5 500) shots (ignoring the discrete nature of the actual number) and you wish to know whether you have enough stock to last the next 3 years

VoseDepletion(VoseExponObject(120),VoseGammaObject(3, 5000),80 000,3*365)

Inserting this array formula into cell range B2:C4 we get something like this:

Running a simulation and taking the mean value for Cell C3 will give the probability of running out of vaccine within the timeframe (in this case, about 87.3%).

The easiest way of 'constructing' the VoseDepletion function is to open the Depletion Calculation window.

 

ModelRisk

Monte Carlo simulation in Excel. Learn more

Tamara

Adding risk and uncertainty to your project schedule. Learn more

Navigation

FREE MONTE CARLO SIMULATION SOFTWARE

For Microsoft Excel

Download your free copy of ModelRisk Basic today. Professional quality risk modeling software and no catches

Download ModelRisk Basic now

FREE PROJECT RISK SOFTWARE

For Primavera & Microsoft Project

Download your free copy of Tamara Basic today. Professional quality project risk software and no catches.

Download Tamara Basic now
-->