Flaw of average (FOA) – Stochastic Model in Excel

So far, we used deterministic approach in my previous blogs, e.g. we specify the age that we want to retire, how many years of survival or the annual incremental rates.  These values are all predetermined; we can add more scenarios but all the scenarios are still determined by us.  These values may be our best estimate and most probable to happen.  However, there may be situations that are very rate but the consequence can be huge, e.g. 2008 financial market meltdown.  This is why we should consider stochastic model.

What is stochastic model?  There are many definitions but generally involve random variables.  Let me use a very simple example to illustrate.  Let’s say we want to estimate the cost of dental claims for a new client who have 2,500 employees. We have dental expense records for 1 million employee and the average claim amount per employee per year is $7,256.00.  So it is simple and the estimate total for 2,500 employee would be $18,140,000.

As I mentioned in my previous blog, Excel to handle flaw of average (FOA) – Data Table, the problem of average is that 50% of the outcome is bigger and 50% is smaller.  50% of outcome can be bigger?  How much bigger? We may like to find out more about our estimation.  This is where stochastic model would help. 

Let’s first populate all 1 million dental claims in column B and use column A as an index (lookup) column.

We would randomly select the claims amount by generating a random number between 1 to 1,000,000 and use VLOOKUP to retrieve the corresponding claim amount.

=VLOOKUP(RANDBETWEEN(1, 1000000),$A$1:$B$1000000, 2)

Then we generate 2,500 records for our first scenario and the total claim is $18,467,408.17.

We repeat the same procedures to randomly generate 200 scenarios.

Now we have a distribution of total claim costs, then we can use Excel Percentile function to determine the percentile of of the total claim costs distributions.

=PERCENTILE($H$2503:$GY$2503,I2505/100)

The total claim costs at 50th percentile is $18,104,248 which is very close to our total claim costs based on the average claim ($18,140,000).  This distribution give us a better understanding on what would happen, e.g. there is only 1% chance that the total claim cost would be bigger than $19,698,418.

This is a very simple stochastic model, we may want to consider age, gender, family size, location or even use a more robust random generator when we develop a real life stochastic model.  However, I just want to illustrate what a stochastic model is and demonstrate how we can build a stochastic model in Excel.

Andrew Chan is the owner and founder of ALG Inc.

We help you to make better and faster decisions!