Real world application: Deriving default probabilities from observed CDS spreads

cAse113

New Member
Hi there,

I have to solve a problem which is actually a real world application of Malz, Chapter 7 - Bootstrapping default probabilities given an observable CDS spread curve.

Please refer to the excel attached: I have created an excel spreadsheet that should do the calculation. What it basically does is - given an observable CDS spread curve - modelling the cash flows of a hypothetical CDS such that the present value of the CDS at initiation is zero. It is an iterative calculation process (goal seek in excel) that equates the present values of cash flows for both legs of the swap for each term (year 1 to year 10) with manipulating the hazard rate for the respective term. The calculation iteration (goal seek for term 1 to term 10) is made through VBA - however I had to remove the code because I cannot upload a .xlsm file here.

Example of an iteration step (term 1): Cell E105 shall be zero with manipulating the hazard rate in Cell D36
Example of an iteration step (term 2): Cell E106 shall be zero with manipulating the hazard rate in Cell D37
and so on...

Please note the excel is based on another excel I found through google: (lamfin.arizona.edu/fixi/credit/ppt/cds1.xls)

As I am not a super quant-expert, it would be great if one (that is ;) ) could have a look and check whether the logic applied is correct. Also, is my interpretation correct, that the cumulative default probability up and including year 10 is (1 - Survival probability of year 10) ? In this case this would be around 16% which is quite high given the rather low CDS spreads...

Note the risk free rate currently used in the model is just a random input (no real market data).

Looking forward to feedback for this!
 

Attachments

  • Unlinked and no VBA_CDS default probability derivation_Rev2016-08-04.xlsx
    32.1 KB · Views: 47
Top