Fitting distribution to Scenario Analysis Data in Excel II

Operational Risk Modeling Analytics

EduPristine and Bionic Turtle have entered into a partnership to promote practical applications of the concepts related to risk analytics and modeling. Practical and hands on understanding of building excel based models related to operational and credit risk is necessary for any job related to risk management. For this purpose, we would be illustrating step by step model building techniques for risk management. Registrations for Operational Risk are OPEN. Please sign-up now.

Using Quantile Approach

Hi,

This is Dinesh profile from EduPristine! If you remember last time we had starting discussion on fitting distribution to data collected from operational risk experts through Scenario Analysis, particularly Interval Approach

Recap

Last time we had discussed that to fit distribution to scenario data, it is important that data is elicited from experts in such a manner that it is amenable to distribution fitting using one of the methods such as moment matching, Quantile/percentile matching, maximum likelihood, OLS.

Business experts may not understand probability and statistics. So questions need to be framed in such a manner that scenarios are easy to understand and probability distributions can be fitted to frequency and severity data elicited from experts.

Methodologies of fitting distributions

If you remember we had started fitting distributions using scenario analysis and Interval approach, where experts mention the frequency of losses estimated within specific loss intervals.

This time we discuss the percentile approach – data is collected for specific percentiles/quantiles of loss severity from experts. In this tutorial we would discuss the Interval Approach. In the following illustrations, we will fit continuous distributions to scenario data collected for loss severities.

Assume that the output of a scenario workshop is that the median loss severity and 90^th percentile loss severity are USD 30000 and USD 160000 respectively.

Using Quantile Matching for fitting severity distribution to data collected through Quantile approach

Step-1: Decide on a distribution to be fitted to data.

• One of the decision criteria could be fatness of the tail.
  • Usually, thin tailed distributions (exponential, Weibull with shape parameter>1, gamma) should be fitted to HFLS cells (High Frequency, Low Severity)
  • Medium and fat tailed distribution (such as lognormal, Weibull with shape<1, extreme value) should be fitted to LFHS cells.
• Whether the cell is HFLS or LFHS may be based on expert judgement and empirical studies.
• One of the problems with Quantile approach is that ex-ante. The modeller has to decide which distribution would best describe operational losses in a cell.
• This is in contrast with Loss Data Analysis where the modeller can fit various distributions to data and the check which distribution best fits the data (using information criteria and other goodness of fit tests).

For this illustration, let us fit a lognormal distribution to scenario data.

Step-2: Decide on seed values of distribution parameters to calculate theoretical Quantile

• For lognormal distribution, both parameters need to be positive.

Step-3: Calculate the theoretical Quantile

• Theoretical Quantile = INVCDF(probability)
• In our illustration, theoretical median would be = LOGINV(.5)
• And theoretical 90^th Quantile would be = LOGINV(.9)

Step-4: Compare theoretical quantiles with empirical/scenario quantiles.

• Calculate the sum of squared differences between theoretical and scenario quantiles.
• Squaring up magnifies deviances and will help us in penalising large deviations.

Step-5: Use an optimization algorithm (like Excel Solver) to minimise sum of squared differences between theoretical and empirical quantiles by changing parameter values.

In our illustration, change in parameter-1 (log_mean) to 10.31 and parameter-2 (log_stdev) to 1.31 reduces the squared deviation between theoretical quantiles and expert opinion to zero.

Therefore, lognormal (10.31, 1.31) may be used for severity modeling in OpVaR estimation.

Practical considerations

One of the common issues is how many quantiles should be elicited from the experts. For fitting a two-parameter continuous distribution, atleast two quantiles should be elicited.

For practical considerations, it may be difficult to ask experts about more than three-four quantiles, lest they will be confused. BCBS in its July-2009 paper on Results from the 2008 Loss Data Collection Exercise for Operational Risk observes that the median number of severity percentiles for banks using the percentile approach was four, with a narrow inter-quartile range indicating that at least three quarters of these banks used four or fewer percentiles.

Further Reading

Both the MLE approach and Quantile approach to fit continuous distributions to scenario analysis data are also discussed in BCBS paper on Results from the 2008 Loss Data Collection Exercise for Operational Risk, Annexure-C.

Templates to download

I have created a template for you, where the subheadings are given and you have to link the model to get the cash numbers!

Way forward

The last tutorial illustrated the Interval approach to fitting distribution using scenario analysis and this tutorial illustrated the percentile/ quantile approach to fitting distribution. I hope that you enjoyed the tutorial. We would keep discussing more practical aspects of Operational Risk to last post on this blog. Stay Tuned.

By the way, we have launched the course on Operational Risk Modeling, which covers all these practical concepts through video lectures. If you want to join the same, you can join by clicking here join the course

To reach the author, you can send an email to dinesh.chaudhary@asymmetrix.co.in.