Play Basel II Accord with SAS (2): portfolio simulation

Although Basel II largely depends on probability instead of generalized linear model that SAS is especially good at, still SAS’ excellent data manipulation and visualization features make it one of the finest tools to explore and implement this accord. Paragraphs from No. 403 to No. 409 of Basel II - Pillar 1 list the requirements for a functioning grading structure, such as at least 7 grading levels are needed.

The internal rating-based (IRB) approaches utilize a bank's own rating structure to estimate the risk weights. To discover the impact of an arbitrary grading structure toward portfolio-wise capital requirement, I simulated a 2000-borrower size credit portfolio. Other settings are 45% loss given default (LGD), 2.5 yr maturity and a 7-level grading structure 0-0.05-0.08-0.15-0.5-2-15, exactly like what were used by Gunter and Peter. The histogram of this simulated portfolio between probability of default (PD) and exposure at default (EAD) is displayed above.

data simu;
   format lgd pd percent8.2 ead dollar8.;
   lgd = 0.45;
   m = 2.5;
   do i = 1 to 2000;
      pd = rand('EXPO') * 1.8 / 100;
      ead = rand('UNIFORM') * 300 + 700;
      output;
   end;
   drop i;
   label lgd = 'Loss given default'
         pd  = 'Probability of default'
         ead = 'Exposure at default'
         m   = 'Maturity';
run;

ods html gpath = 'c:\tmp\' style = money;
ods graphics on;
proc kde data = simu;
   bivar pd ead / plots = histsurface;
run;
ods graphics off;
ods html close;

data grdstr01;
   grdstr = '0-0.05-0.08-0.15-0.5-2-15';
   informat lowbound 8.4;
   do i = 1 to 7;
      lowbound = scan(grdstr, i, '-') / 100;
      output;
   end;
   call symput('grdstr', grdstr);
   keep lowbound;
run;

data grdstr02;
   merge grdstr01 grdstr01(firstobs=2 rename=(lowbound=uppbound));
   if missing(uppbound) = 1 then uppbound = 1; 
   grade = _n_;
   ratio = uppbound - lowbound;
run;

proc sql noprint;
   select cats(lowbound, '-<', uppbound, '=', grade) into: fmtvalue separated by ' '
   from grdstr02;
quit;

A grading format and the capital requirement function were built. Then the portfolio was graded and the by-grade required capitals were calculated. The classified results are showed above. Overall, the weighted portfolio-wise capital requirement is 7.95%.

proc format;
   value gradefmt
      &fmtvalue ;
run;

proc fcmp outlib = work.myfunclib.finance;
  function reqcap(pd, lgd, m);
     corr = 0.12*(1-exp(-50*pd))/(1-exp(-50)) + 0.24*(1-(1-exp(-50*pd))/(1-exp(-50)));
     mtradj = (0.11852 - 0.05478 * log(pd))**2;
     return( (lgd * probnorm((probit(pd) + corr**0.5 * probit(0.999))
           / (1-corr)** 0.5) - pd*lgd) * (1 + (m-2.5)*mtradj) / (1-1.5*mtradj) );
  endsub;
quit;

options cmplib = (work.myfunclib);
proc sql noprint;
   create table _tmp01 as
   select * , put(pd, gradefmt.) as grade 
   from simu
   ;
   select count(*) into: totalno
   from _tmp01
   ;
   select avg(pd) into: totalpd
   from _tmp01
   ;
   create table _tmp02 as
   select distinct grade, count(grade) / &totalno as proppd, avg(pd) as grouppd, 
                  reqcap(calculated grouppd, lgd, m) as groupcr,
                  (calculated proppd * calculated grouppd) / &totalpd as propdft
   from _tmp01
   group by grade
   ;
   create table _tmp03 as
   select a.*, b.groupcr as cr,  
      b.groupcr*a.ead as expcr 'EAD*Required Capital' format = dollar8.2
   from _tmp01 as a left join _tmp02 as b
   on a.grade = b.grade
   ;
   select sum(expcr) / sum(ead) into :totalcr
   from _tmp03
   ;
quit;

ods html gpath = 'c:\tmp\' style = money;
proc sgpanel data = _tmp03 noautolegend;
   title "The portfolio-wise required capital is %sysfunc(putn(&totalcr, percent8.2))";
   title2 "By a grading structure &grdstr";
   panelby grade / columns = 4 rows = 2;
   needle x = pd y = expcr;
   rowaxis grid;
run;
ods html close;

The required capital corresponding to each grade was demonstrated in a tile plot. The size of the subplots indicates the counts of borrowers at individual grades. Obviously, the concentrations of borrows concentrated in some grades such as 5 and 6, which contradicts the requirement by Paragraph 406.

proc sql;
   create table _tmp04 as
   select distinct grade, cr 'Required capital', count(grade) as count
   from _tmp03
   group by grade
   ;
quit;
ods html gpath = 'c:\tmp\' style = harvest;
goptions device=javaimg ftitle="arial/bold" ftext="arial" 
htitle=.15in htext=.2in xpixels=600 ypixels=500;
proc gtile data = _tmp04;
    tile count tileby = (grade, count) / colorvar = cr;
run;
ods html close;

Furthermore we can use area under curve(AUC) or Gini coefficient to evaluate this grading structure. For this portfolio, Gini coefficient by such a grading structure is 0.4296, which is pretty low and may suggest that they don’t match each other.

data _tmp05;
   if _n_ = 1 then do
      sum_proppd = 0; sum_propdft = 0;
      dif_dft = 1; dif_pd = 1;
      output;
   end;
   set _tmp02;
      retain sum_proppd sum_propdft;
      sum_proppd = sum_proppd + proppd;
      sum_propdft = sum_propdft + propdft;
      dif_dft = max(0, 1 - sum_propdft);
      dif_pd  = max(0, 1 - sum_proppd);
   output;
run;

proc iml;
   use _tmp05;
      read all var{dif_pd dif_dft};
   append;
   start TrapIntegral(x,y);
      call sort(x,1);
      call sort(y,1);
      N = nrow(x);
      dx = x[2:N] - x[1:N-1];
      meanY = (y[2:N] + y[1:N-1])/2;
      return( dx` * meanY );
   finish;
   area = TrapIntegral(dif_pd, dif_dft);
   gini = (area - 0.5) / (&totalpd / 2 + (1 - &totalpd) - 0.5);
   call symput('area', left(char(area)));
   call symput('gini', left(char(gini)));
quit;

ods html gpath = 'c:\tmp\'  style = money; title;
proc sgplot data = _tmp05;
   series x = dif_pd  y = dif_dft ;
   scatter x = dif_pd  y = dif_dft ;
   band upper = dif_dft lower = 0 x = dif_pd / transparency=.5;
   xaxis grid label = 'Ratio of observations ';
   yaxis grid label = 'Ratio of default' ;
   inset "GINI Coefficient is %sysfunc(putn(&gini, 8.4))" 
      / position = topleft border;
   keylegend "scatter";
run;
ods html close;

In conclusion, the grading structure has significant impact toward the required capital. Optimization of grading structure can be realized by the method of random walk, which will be discussed next post.