标题: Play Basel II Accord with SAS (2): portfolio simulation [打印本页] 作者: shiyiming 时间: 2011-7-25 18:31 标题: Play Basel II Accord with SAS (2): portfolio simulation From Dapangmao's blog on sas-analysis
<div class="post-header"><div class="post-header-line-1"></div></div><div class="post-body entry-content" id="post-body-2213368268329690441"><div class="separator" style="clear: both; text-align: center;"><a href="http://2.bp.blogspot.com/-LXP8FH9WaTA/TixsZrktXLI/AAAAAAAAAsY/6kY-yUlteBQ/s1600/Capture.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="249" src="http://2.bp.blogspot.com/-LXP8FH9WaTA/TixsZrktXLI/AAAAAAAAAsY/6kY-yUlteBQ/s400/Capture.JPG" width="400" /></a></div><br />
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. <br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://4.bp.blogspot.com/-tcZcCsUM3n8/TixsmS0MigI/AAAAAAAAAsg/Iwf5vVmeS30/s1600/HistogramSurface7.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="300" src="http://4.bp.blogspot.com/-tcZcCsUM3n8/TixsmS0MigI/AAAAAAAAAsg/Iwf5vVmeS30/s400/HistogramSurface7.png" width="400" /></a></div><br />
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 <a href="http://www.amazon.com/Credit-Modeling-using-Excel-Finance/dp/0470660929/ref=sr_1_1?ie=UTF8&qid=1311536727&sr=8-1">Gunter and Peter</a>. The histogram of this simulated portfolio between probability of default (PD) and exposure at default (EAD) is displayed above.<br />
<br />
<pre style="background-color: #ebebeb; border: 1px dashed rgb(153, 153, 153); color: #000001; font-size: 14px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>
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;
</code></pre><div class="separator" style="clear: both; text-align: center;"><a href="http://3.bp.blogspot.com/-KbVpcK9aQV0/Tixs8MR8WQI/AAAAAAAAAso/Ex_plQ5oGtg/s1600/SGPanel22.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="300" src="http://3.bp.blogspot.com/-KbVpcK9aQV0/Tixs8MR8WQI/AAAAAAAAAso/Ex_plQ5oGtg/s400/SGPanel22.png" width="400" /></a></div><br />
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%.<br />
<br />
<pre style="background-color: #ebebeb; border: 1px dashed rgb(153, 153, 153); color: #000001; font-size: 14px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>
proc format;
value gradefmt
&fmtvalue ;
run;
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;
</code></pre><div class="separator" style="clear: both; text-align: center;"><a href="http://2.bp.blogspot.com/-sG-kZu7Gihg/TixtPhD9iSI/AAAAAAAAAsw/Cm56wYb82AY/s1600/graph7.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="333" src="http://2.bp.blogspot.com/-sG-kZu7Gihg/TixtPhD9iSI/AAAAAAAAAsw/Cm56wYb82AY/s400/graph7.png" width="400" /></a></div><br />
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.<br />
<br />
<pre style="background-color: #ebebeb; border: 1px dashed rgb(153, 153, 153); color: #000001; font-size: 14px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>
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;
</code></pre><br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://4.bp.blogspot.com/-r455EzlIJdg/TixtsL_lM5I/AAAAAAAAAs4/Rxd8AtwSVBs/s1600/SGPlot9.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="300" src="http://4.bp.blogspot.com/-r455EzlIJdg/TixtsL_lM5I/AAAAAAAAAs4/Rxd8AtwSVBs/s400/SGPlot9.png" width="400" /></a></div><br />
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.<br />
<br />
<pre style="background-color: #ebebeb; border: 1px dashed rgb(153, 153, 153); color: #000001; font-size: 14px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>
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;
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;
</code></pre>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.</div><div class="blogger-post-footer"><img width='1' height='1' src='https://blogger.googleusercontent.com/tracker/3256159328630041416-2213368268329690441?l=www.sasanalysis.com' alt='' /></div><img src="http://feeds.feedburner.com/~r/SasAnalysis/~4/immc6MiMFXk" height="1" width="1"/>