Credit Default Swap (CDS) Pricing in Excel using QuantLib
Free and open source QuantLib supports the precise valuation of Credit Default Swaps (CDS) in Excel.
A CDS contract specifies N payment times T1, T2, …, TN at which, counterparty A (the Protection Seller) receives a pre-agreed fixed amount from counterparty B (the Protection Buyer) provided that a pre-defined credit event – such as the default of some specific issuer with respect to a bond issued by that issuer - has not taken place.
Otherwise, if the credit event occurs then the swap is terminated and the protection seller A is obliged to compensate B either in cash or by buying the referenced bond from B at par.
If B is in possession of the referenced bond then the CDS contract can be regarded as an insurance contract undertaken by B as protection against a future sudden devaluation of the referenced bond due to an issuer's default. In this case, the regular fixed amounts paid by B at times T1, T2, …, TN are the insurance premia for the assumed protection.
Still, a CDS is considered to be a derivative rather than an insurance contract because there is no requirement that the protection buyer B possesses the referenced bond.
The stream of cash flows paid by the protection buyer is called the fixed leg or premium leg of the swap.
The single contingent cash flow paid by the protection seller is called the floating leg or protection leg of the swap.
The diagram below shows the timing of the various contingent cash flows:
Recommended for Deriscope starters: The Overview and Quick Guide pages.
Creating a slimmed-down CDS object in 13 seconds
I can use the wizard to produce a formula creating a CDS object in only 13 seconds, provided I am happy with the default assignments, as shown in the video below:
The produced formula together with the formula responsible for the time schedule of the premium leg is shown below. Note this compact form displays only the mandatory input parameters. I can view all parameters – both mandatory and optional – inside the wizard by selecting the cell A1 holding the handle name of the created CDS object.
Creating a full-fledged CDS object
The next video shows how I can instruct the wizard to build the formulas so that they include all input parameters.
Note in particular the two additional entries that appear as soon as I change the Quote Type input from Spread to Up Front.
During the construction, a useful intermediate output called ¬Premium Cash Flows allows me to see the premium payments implied by my existing entries.
Similarly within the screen of the premium leg schedule, the intermediate outputs called ¬Unadjusted Dates and ¬ Adjusted Dates display the dates defining the accrual periods of the premium leg.
and this is how the pasted formulas look like:
Understanding the main formula
As you see, cell A1 contains the formula =ds(A2:B18), which takes one input argument and returns the text &CDS_A1:1.1
The prefix & indicates that &CDS _A1:1.1 is the handle name of some object. In fact it points to an object of type CDS.
The input range A2:B18 contains the input, of which the most important keys are described in the table below:
| Key | Value | Description |
| Quote Type | Spread | Defines how the swap
is quoted.
Two available choices:
Spread: According to this convention, the fair premium is used to quote the value of the traded CDS. This is in turn defined as the annualized percentage of notional that the protection buyer would have to pay on the predefined dates over the life of the swap in order for the present value of the swap to be exactly zero. Up Front: According to this convention, the so called points upfront is used to quote the value of the traded CDS. This is in turn defined as the percentage of notional that the protection buyer must pay once at inception in addition to also paying a fixed coupon rate (referred as running spread ) on the predefined dates over the life of the swap. |
| Spread | 0,004 | The contractually defined coupon rate that the protection buyer must pay during the life of the swap, in fractional annualized units, such as 0.04 for a 4% (400 bps) rate. |
| Premium Schedule | &Schedule_D1:1.1 | The schedule of the premium leg accrual periods. The dates here define only the start and end dates of each successive accrual period. The respective premium amounts are paid at the shifted end date of each period. |
| Accrual On Default | TRUE | Determines whether or not the accrued coupon in a CDS is due in the event of a default. |
| Settle On Default | TRUE | Determines when any payments triggered by a default event in a CDS are due. |
| Define Protection Start | TRUE | Allows specifying a future date after which protection starts. |
| Define Up Front Date | TRUE | Allows specifying the settlement date of the upfront payment. |
| Claim Type | Notional Claim | Defines the claim type
of the protection triggered when the default event occurs.
Two available choices:
Bond Claim: Upon a default event, the CDS holder exerts claim both upon the notional and the accrual coupon of the Bond referenced by the CDS contract. Notional Claim: Upon a default event, the CDS holder exerts claim only upon the notional referenced by the CDS contract. |
I can always access detailed information on any key-value pair by selecting the respective cell as this video shows:
Browsing the contents of a created CDS object
The contents of the object in cell A1 are immediately visible in the range below it.
But imagine I am using this object in another worksheet through a link.
Then I can still see the object's contents as soon as I select the respective cell as the following picture demonstrates.
Looking inside the wizard, I cannot only verify important parameter values, such as Spread = 0.004 but I can also browse through deeper details by clicking on the respective lens symbols.
For example, if I click on the lens symbol of the cell next to Premium Cash Flows I see the following:
Using the CDS object
Deriscope supplies through QuantLib the following three functions that can be called from a given CDS object:
Cash Flows: Returns the cash flows of the premium leg.
Price: Returns primarily the swap's price for given pricing model, yield curve and credit curve inputs.
Note that the Price function may also return several other values beyond the price of the swap by setting the value for the Output key accordingly. More on that below.
Implied Hazard Rate: Returns the flat hazard rate that would cause the given CDS to have the quoted price.
Conventional Spread: First the implied hazard rate for a target NPV = 0 is calculated using the Implied Hazard Rate function.
Then a credit curve is constructed taking as input a flat hazard rate equal to the one found above.
Finally the fair spread (i.e. the running premium that makes the CDS have zero price) is calculated and returned.
The Price function
The following video shows how I use the wizard to generate in only 8 seconds the 5 spreadsheet formulas required by the Price function:
Below is the spreadsheet image with the pasted function in cell G1 that returns a CDS price of -0,004350485. The 5 newly created blocks are clearly visible.
You notice the two bottom blocks contain the wizard-generated default credit and yield curves, both set with flat rate for simplicity.
I can obviously replace them with more realistic, market-calibrated non-flat curves.
You also notice in cell H10 the Pricing Method set to Mid Point Cds.
I may change it to another method.
If I select the key-containing cell G10, I read off the wizard that there exist the following three different methods available:
Integral Cds
Isda Cds (see at the end of this article for a YouTube video on this topic)
Mid Point Cds
Additional output data
During the price calculation, QuantLib also computes certain values that can be of interest.
When cell G1 is selected, the wizard displays the following in its Info Area:
If I click on the here hyperlink of the top sentence "To display extra pricing data click here", I see the following:
As you see, beyond the cash flows I can also see the Fair Upfront, Fair Spread, NPV and BPS as well.
I have already explained here how I may transfer these data in the spreadsheet.
Advanced Topic: Following the pricing algorithm step-by-step
It is also possible to track the pricing algorithm that has been run in order to produce the observed output and also see the actually used intermediate QuantLib structures and numerical outputs as described in the respective section of my Interest Rate Swap article.
You may download the spreadsheet produced with the above steps here.
A youtube video with voice narration on the Isda Cds method is available below.
Feel free to contact me if you want to share any thoughts with regard to this product or if you want to request any particular features. Contact info and social media links are available at my web site https://www.deriscope.com
