Tuesday, June 21, 2011

Credit default swap pricing by PROC FCMP

Sometimes I feel curious about how running a simple VBA macro in Excel could beat my 8-core desktop to indefinite waiting time with 100% CPU usage. On those occasions, I wish SAS could be a rescue, since I am more familiar and confident with SAS. The good news is that in SAS 9.2, many essential Excel functions were translated by Proc FCMP and stored in a built-in dataset named sashelp.slkwxl. Then it will be more convenient for Proc FCMP to port code from Excel to SAS as a bridge. The sashelp.slkwxl dataset contains 41 functions derived from Excel as below:

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***************************************;
         

Good math, bad engineering

As a formal statistician and a current engineer, I feel that a successful engineering project may require both the mathematician’s abilit...