Actuarial and Data Analytics

Providing information and advice on real problems

Claim Reserving in R

Leave a comment

Introduction to Reserving  in R:

In our last two blogs we discussed how to combine reserve estimates, as well as how to start integrating Machine Learning (ML) into your process.  Hopefully the reader found these interesting and useful.  If you have any comments, questions, or just looking for advice, please feel free to contact us.

With that, to provide a more solid foundation, and give the reader real tools to deploy (instead of just talk), I plan on releasing the code used in our prior articles over the next weeks/months.

For the first release, we will start with the basics (triangle based methods).  Other posts that follow will go over the steps to setup data for ML models, ways to run data through the ML models, and end with an excel package that will allow every Actuary to use ML in excel (without having to work in R).  This will serve as the carrot at the end of the stick!

Since most Actuaries are comfortable in excel, the idea was to create an interface that would allow them to perform all their reserving functions (including some basic ML models) without having to learn how to code in R, Python, or other languages.

However, prior to letting the cat out of the bag, I would like to go over the code piece by piece so the user has a better understanding of the model.  I have no intentions of providing a black box.  I want the user to at least grasp some of the basic concepts prior to using the file.

Starting with the basics:

As noted above, we will start by tackling the basics (traditional triangle based method).  For this specific example we will be covering the Paid Per Member Per Month (Paid PMPM) method.  Although this can be done efficiently in excel, this will give the reader an understanding of the logic used in R.

Let’s begin – Suppose we have a matrix called “data” that contains the three basic features (see below):

Incurred Month Processed Month Paid Claim PMPM
2010-01-01 2010-01-01  xx.xx
2010-01-01 2010-02-01  xx.xx
2010-01-01 2010-03-01  xx.xx
2010-01-01 2010-04-01  xx.xx
2010-01-01 2010-05-01  xx.xx
2010-01-01 2010-06-01  xx.xx
As a primer we need to create two functions called “add.months” and “add.months.v”.  This will allow us to filter the data as we loop through each incurred and paid month.
add.months= function(date,n) seq(date, by = paste (n, “months”), length = 2)[2]
add.months.v= function(date,n) as.Date(sapply(date, add.months, n), origin=”1970-01-01″)
Next we will define a runout and the lookback period.  Runout will reflect the number of months we wish to include (in this case we will assume 24 months of runout is sufficient enough to be complete).  Lookback on the other hand will be the number of recently observed lag PMPMs (based on the time period within the row) that we will use as our estimate.
runout = 24
lookback = 6

We will then create a loop that cycles through all rows within the data.

for (i in 1:dim(data)[1])
And finally, we have the portion that is being looped through.  Here data$PdPMPM is an added column to our initial data in which we will drop our results (and i is the row within the data).
{data$PdPMPM[i]=sum(data[data$Pdmo >=add.months.v(data$Pdmo[i],-(lookback-1)) & data$Pdmo<=data$Pdmo[i] & data$Lag > data$Lag[i] & data$Lag <=runout,]$Paid)/lookback
  +  sum(data[data$Incmo==data$Incmo[i] & data$Lag <= min(data$Lag[i],runout),]$Paid)}
 
 In short, we are cycling through all rows and trying to complete that row based on the incurred and paid month.
I have attached an excel sheet with dummy data illustrating and comparing the calculation in excel and R.
Enjoy!
Below is the link to the excel file!

 

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s