Monday, July 11, 2011

Play Basel II Accord with SAS (1): capital requirement

Basel II Accord, revised by Basel Committee on Banking Supervision (BCBS) and adopted by more than 100 nations, regulates the commercial banks’ capital against risks. Major US banks are under the transition window to fully comply it (2008-2011) [Ref. 1]. I am especially interested in exploring Basel II by SAS codes.

Basel II Accord provides the detailed requirements by its three ‘pillars’. Pillar 1 is about the minimal capital requirement and tries to hold the total capital at 8% of risk-weighted assets. Paragraph 272 gives the equations to calculate the capital requirement for a loan with its risk factors, such as PD(probability of default), LGD(loss given default) and M(effective maturity). I used a user-defined function in SAS to compute it. Then I drew three plots for 1/ 2.5/ 5-year maturity, respectively. The function behaves like a paraboloid, which may suggest that the relationship may not hold when PD is very high. In addition, the longer maturity needs more capital.

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);
data reqcap01;
do pd = 0.01 to 0.80 by 0.01;
do lgd = 0.01 to 0.6 by 0.01;
do m = 1 to 5 by 0.5;
reqcap = reqcap(pd, lgd, m);
output;
end;
end;
end;
run;

proc template;
define statgraph surface001;
dynamic title;
begingraph;
entrytitle title;
layout overlay3d / cube = true rotate = 30
xaxisopts = (label="Probability of default")
yaxisopts = (label="Loss given default")
zaxisopts = (label="Required capital");
surfaceplotparm x = pd y = lgd z = reqcap
/ surfacetype = fill surfacecolorgradient = reqcap;
endlayout;
endgraph;
end;
run;

%macro surfaceplot(mlist = 1 2.5 5);
%do i = 1 %to 3;
%let maturation = %scan(&mlist, &i, ' ');
ods html gpath = "c:\tmp\" style = money;
proc sgrender data = reqcap01(where=(m=&maturation)) template = surface001;
dynamic title = "Time to maturation is &maturation yr";
run;
ods html close;
%end;
%mend;
%surfaceplot(mlist = 1 2.5 5);

The unconditional expected loss, LGD * PD, is already subtracted by the capital requirement function, according to Basel II. It can be compared with required capital in the same plot above.

proc template;
define statgraph surface002;
dynamic title;
begingraph;
entrytitle title;
layout overlay3d / cube = false rotate = 150 tilt = 30
xaxisopts = (label="Probability of default")
yaxisopts = (label="Loss given default")
zaxisopts = (label="Required capital") ;
surfaceplotparm x = pd y = lgd z = reqcap
/ surfacetype = fill surfacecolorgradient = reqcap;
surfaceplotparm x = pd y = lgd z = eval(pd * lgd);
endlayout;
endgraph;
end;
run;

ods html gpath = "c:\tmp\" style = money;
proc sgrender data = reqcap01(where=(m=2.5)) template = surface002;
dynamic title = "Time to maturation is 2.5 yr";
run;
ods html close;

Paragraph 273 in Pillar 1 makes firm-size adjustment for small- or medium-sized borrowers with less than 50 million Euros reported sales. Then I made another function to reflect the adjustment.

proc fcmp outlib = work.myfunclib.finance;
function reqcap_sme(pd, lgd, m, s);
s1 = max(s, 5);
corr = 0.12*(1-exp(-50*pd))/(1-exp(-50)) + 0.24*(1-(1-exp(-50*pd))/(1-exp(-50)))
- 0.04*(1-(s1-5)/45);
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);
data reqcap02;
m = 1.5;
do s = 5 to 50 by 5;
do pd = 0.01 to 0.80 by 0.01;
do lgd = 0.01 to 0.6 by 0.01;
reqcap = reqcap_sme(pd, lgd, m, s);
output;
end;
end;
end;
run;
proc export data = reqcap02 outfile = 'c:\tmp\out.csv' replace;
run;


Since SAS does not have an option for parallel 3d plotting yet, I switched to R’s ‘lattice’ package. Comparing the plots, it shows that Basel II made an allowance for small size borrowers to inhibit the trend of over-lending to big entities.

x = read.csv('c:/tmp/out.csv')
library('lattice')
wireframe(reqcap ~ pd * lgd | s , x, shade = TRUE,
screen = list(z = -30, x = -50), lwd = 0.01,
xlab = "Probablity of default", ylab = "Loss given default",
zlab = "Required capital")


PS.
Rick Wicklin kindly provided a novel solution for four-dimension visualization - paneled contour plot, powered by SAS's graph template language (GTL). Therefore, we can clearly see the trend as the sizes of the borrowers change. In conclusion, by utilizing the SG procedures and GTL of SAS, a SAS user may easily grow to a data artist.

proc template;
define statgraph Graph;
dynamic _X _Y _Z _panelby;
dynamic _panelnumber_;
begingraph;
layout datapanel classvars=(_panelby) /
rowdatarange=unionall row2datarange=unionall
columndatarange=unionall column2datarange=unionall
headerlabeldisplay=value rows=3 columns=4;
layout prototype;
contourplotparm x=_X y=_Y z=_Z /
name='contour' contourtype=LABELEDLINEFILL
colormodel=ThreeColorRamp gridded=false;
endlayout;
endlayout;
endgraph;
end;
run;

ods html gpath = "c:\tmp\" style = money;
proc sgrender data=WORK.REQCAP02 template=Graph;
dynamic _X="PD" _Y="LGD" _Z="REQCAP" _panelby="S";
run;
ods html close;
References:
1. Philippe Jorion. ‘Financial Risk Manager Handbook’ 6th edition. Wiley, 2010
2. ‘Basel II - Third Consultative Package, Pillar One (29 April 2003)’. www.bis.org/bcbs/cp3part2.pdf

1 comment:

  1. Rick,
    Fantastic! Thanks a lot for this gracious help. I tried GTL or the SGPANEL procedure for 3D scenarios, and didn’t figure out a way yet. I really like your contour plot solution. It is just beautiful.
    Charlie

    ReplyDelete