Hey all.
Lets say I got some historical data for OP risk losses (frequency & severity) for last 10 years.
From that data I can construct frequency distribution for number of loss events per year.
I plan to use POISSON.DIST(x,average(data set),0) function for that.
This function will never return 0% for any given x.
I plan to increase x until POISSON.DIST(x,average(data set),1) returns 0.99999%.
I plan to use Monte Carlo simulations like this:
1. A random draw is taken from the frequency distribution to produce the
simulated number of loss events per year.
2. A number of random draws that is equal to the simulated number of
loss events is taken from the severity distribution.
3. The predicted annual loss amount is obtained by summing the simulated losses
obtained in step 2.
4. Steps 1, 2, and 3 are repeated a large number of times to obtain a large
number of values (amounts) of the annual loss, which are used to plot a
histogram representing the distribution of the total annual loss.
In literature, many suggest a use of Log-normal distribution for severity distribution.
I am not sure how to use LOGNORM.DIST() to construct this severity distribution and how to take data from when it is constructed.
Any help would be great.
Lets say I got some historical data for OP risk losses (frequency & severity) for last 10 years.
From that data I can construct frequency distribution for number of loss events per year.
I plan to use POISSON.DIST(x,average(data set),0) function for that.
This function will never return 0% for any given x.
I plan to increase x until POISSON.DIST(x,average(data set),1) returns 0.99999%.
I plan to use Monte Carlo simulations like this:
1. A random draw is taken from the frequency distribution to produce the
simulated number of loss events per year.
2. A number of random draws that is equal to the simulated number of
loss events is taken from the severity distribution.
3. The predicted annual loss amount is obtained by summing the simulated losses
obtained in step 2.
4. Steps 1, 2, and 3 are repeated a large number of times to obtain a large
number of values (amounts) of the annual loss, which are used to plot a
histogram representing the distribution of the total annual loss.
In literature, many suggest a use of Log-normal distribution for severity distribution.
I am not sure how to use LOGNORM.DIST() to construct this severity distribution and how to take data from when it is constructed.
Any help would be great.