Type Function
----------------------------------
Finance Excel ACCRINT
Excel ACCRINTM
Excel AMORDEGRC
Excel AMORLINC
Excel COUPDAYBS
Excel COUPDAYS
Excel COUPDAYSNC
Excel COUPNCD
Excel COUPNUM
Excel COUPPCD
European DATDIF
Excel DB
Excel DISC
Excel DOLLARDE
Excel DOLLARFR
Excel DURATION
Excel EFFECT
Excel MDURATION
Excel ODDFPRICE
Excel ODDFYIELD
Excel ODDLPRICE
Excel ODDLYIELD
Excel PRICE
Excel PRICEDSIC
Excel PRICE
Excel RECEIVED
Excel TBILLEQ
Excel TBILLPRICE
Excel TBILLYIELD
Excel YIELD
Excel YIELDDISC
Excel YIELDMAT
Mathematics Excel EVEN
Excel FACTDOUBLE
Excel FLOOR
Excel MULTINOMIAL
Excel ODD
Excel PRODUCT
Statistics Excel AVEDEV
Excel DEVSQ
Excel VARP With the help of user-defined function and some financial functions from sashelp.slkwxl, we can probably develop some pretty complicated SAS programs to replace VBA macros in Excel. For example, credit default swap, a popular instrument in credit derivative market, is like a contract to exchange default risk using spread between buyer and seller. Implementing the pricing mechanism may need a number of modules, like what Gunter and Peter showed with fixed risk-neutral probabilities of default [Ref. 1]. SAS macro can hardly fit in the role as a module, since nested macro with leaky macro variables is a big headache for SAS programmers. In the codes below, I used coupdaysnc_slk() and coupncd_slk() functions from sashelp.slkwxl, which correspond to the coupdaysnc() and coupncd() functions in Excel, and another 3 user-defined functions to build a system for CDS pricing. Besides the features of manufacturing home-made function and encapsulating macros, Proc FCMP proves to be a better tool for vector/matrix operations than Data Step array. The result shows that for some financial applications, the migration from Excel to SAS is smoothed by Proc FCMP.
References:
1. Gunter Loeffler and Peter Posch. ‘Credit Risk Modeling using Excel and VBA’. The 2nd edition. Wiley, 2011.
/*******************READ ME*********************************************
* - Credit default swap pricing by Proc FCMP -
*
* SAS VERSION: 9.2.2
* DATE: 22jun2011
* AUTHOR: hchao8@gmail.com
*
****************END OF READ ME******************************************/
****************(1) MODULE-BUILDING STEP********************************;
******(1.1) CREATE A FUNCTION FOR YEAR FRACTION*************************;
options cmplib = (sashelp.slkwxl work.myfunclib);
proc fcmp outlib = work.myfunclib.finance;
function yearfrac0(sdate, edate);
return(datdif(sdate, edate, '30/360') / 360);
endsub;
quit;
******(1.2) CREATE A FUNCTION FOR ACCRUED INTEREST AT SETTLEMENT*******;
proc fcmp outlib = work.myfunclib.finance;
function aci(settlement_date, maturity_date, rate, freq);
if settlement_date < maturity_date then
aci = 100 * rate / freq * (1 - coupdaysnc_slk(settlement_date, maturity_date, freq, 0)
/ coupdays_slk(settlement_date, maturity_date, freq, 0));
if aci = 0 or settlement_date = maturity_date then aci = 100 * rate / freq;
return(aci);
endsub;
quit;
******(1.3) CREATE A FUNCTION FOR NON-FLAT INTEREST RATE STRUCTURE******;
option mstored sasmstore = work;
%macro intspot_macro() / store source;
%let data = %sysfunc(dequote(&data));
proc sql noprint;
select count(*) into :nobs from &data;
quit;
%mend;
proc fcmp outlib = work.myfunclib.finance;
function intspot(data $, year);
array spots[1, 2] / nosymbols;
rc1 = run_macro('intspot_macro', data, nobs);
call dynamic_array(spots, nobs, 2);
rc2 = read_array(data, spots, 't', 'spotrate');
if nobs = 1 then intspot = spots[1, 2];
else do;
if year le spots[1, 1] then intspot = spots[1, 2];
else if year ge spots[nobs, 1] then intspot = spots[nobs, 2];
else do;
i = 1;
do until(spots[i, 1] gt year);
i + 1;
intspot = spots[i-1, 2] + (spots[i, 2] - spots[i-1, 2])*(year - spots[i-1, 1])
/ (spots[i, 1] - spots[i-1, 1]) ;
end;
end;
end;
return(intspot);
endsub;
quit;
******(1.4) CREATE A MACRO TO EVALUATE CREDIT DEFAULT SWAP SPREAD******;
%macro cdsprice(n = 20, Settlement_date = '15jul2006'd, Maturity_date = '15jul2013'd,
rate = 0.07125, freq = 2, recovery_rate = 0.4,
compounding = 2, pay_freq = 4, pd = 0.0197,
outfile = );
options mlogic mprint cmplib = (sashelp.slkwxl work.myfunclib)
nocenter mstored sasmstore = work;
proc fcmp;
mixed_date = mdy(month(&settlement_date), day(&settlement_date), year(&maturity_date) + 1);
array default_date[&n] / nosymbols;
default_date[1] = coupncd_slk(&settlement_date, mixed_date, &pay_freq, 0);
do i = 2 to &n;
default_date[i] = coupncd_slk(default_date[i-1], mixed_date, &pay_freq, 0);
end;
rc1 = write_array('_tmp01', default_date, 'default_date');
quit;
data _tmp02;
set _tmp01;
datdif = yearfrac0(&Settlement_date, default_date);
spotrate = intspot('rate', datdif);
aci = aci(default_date, &Maturity_date, &rate, &freq) / 100;
retain sum_pd;
if _n_ = 1 then sum_pd = 0;
else sum_pd = sum_pd + &pd;
fees = 1/&pay_freq * (1 - sum_pd) / (1 + spotrate/&compounding)**(&compounding*datdif);
default_pay = (1 - &recovery_rate - &recovery_rate*aci)*&pd
/ (1 + spotrate/&compounding)**(&compounding*datdif);
run;
proc sql noprint;
select sum(default_pay) / sum(fees) format = percent8.3 into: cds_spread from _tmp02;
select intck('year', min(default_date), max(default_date)) into: period from _tmp02;
quit;
ods html file = "&outfile" style = money;
title; footnote;
proc report data = _tmp02 nowd headline split = "|";
columns default_date aci spotrate fees default_pay ;
define default_date / display format = date9. "Dates of|default";
define aci / format = percent9.2 "Accruted interest|rate";
define spotrate / format = percent9.2 "Non-flat interest|rate";
define fees / format = percent9.2 "Accruted fees";
define default_pay / format = percent9.2 "Default payments";
compute after;
line @2 "The %sysfunc(strip(&period)) year CDS Spread is:&cds_spread";
line " ";
line @2 "Settlement date is :%sysfunc(putn(&Settlement_date, date11.))";
line @2 "Maturity date is :%sysfunc(putn(&Maturity_date, date11.)) ";
line @2 "Payment frequency is :&pay_freq";
line @2 "Reference bond coupon rate is :%sysfunc(putn(&rate, percent9.2)) ";
line @2 "Reference bond coupon freqency is :&freq ";
line @2 "Compounding frequency is :&compounding " ;
line @2 "Risk-neutral probabilities of default is :%sysfunc(putn(&pd, percent9.2))";
line @2 "Recover rate is : %sysfunc(putn(&recovery_rate, percent9.2))";
endcomp;
run;
ods html close;
%mend cdsprice;
****************(2) TESTING STEP****************************************;
******(2.1) INPUT DATA OF A TERM STRUCTURE OF INTEREST RATE*************;
data rate;
format t 6.2 spotrate percent7.2;
input t: SpotRate best32.;
cards;
0.083333333 0.055609
/*To buy Gunter and Peter's book will have complete data*/
10 0.057603
;;;
run;
******(2.2) RUN THE MACRO TO HAVE RESULT*******************************;
%cdsprice(outfile = c:\tmp\result.xls);
****************END OF ALL CODING***************************************;
No comments:
Post a Comment