Credit Default Swap Calculator in Excel

This article introduces credit default swap (CDS) contracts, and offers a free Excel spreadsheet that employs the CreditGrades model to price CDS spreads.

A CDS is a derivative contract that insures an investor against non-payment of a debt (usually a bond). The purchaser of the contract (an investor) makes peridodic payments to the seller (a bank) of the contract. The seller agrees to to pay off a debt (owed to the purchaser) if the debtor defaults. More specifically, if the debtor defaults, the seller has to purchase the bond at par, or pays the purchaser the difference between the bond value and the bond recovery rate.

CDS contracts are priced in interest rate spreads (in basis points) per year of the contract’s notional value. A CDS spread of 100 basis points over five year contract for a notional amount of $1,000,000, for example,  costs £10,000 per year. This premium is often paid every quarter.

Credit default swap contracts are often purchased speculatively as a hedging instrument, or as a form of arbitrage.

CreditGrades CDS spread calculator in Excel

In the 1970s, structural models for estimating credit default risk emerged, based on work by Merton (1973) and Black & Scholes (1974). These structural models assumed that equity and debt can be interpreted as options on a company’s assets, with the the company’s value acting under diffusion. Default happens when the stock prices reaches zero and the company asset value falls below the debt value (the default barrier). The default barrier is simply the debt-per-share multiplied by the average debt recovery rate.

Building from this early work, several investment firms (J.P. Morgan, Goldman Sachs, Deutche Bank and RiskMetrics) published the CreditGrades model in 1997 to predict CDS interest rate spreads. The model accounts for the fact that a company’s liabilities are not known until the company defaults, and that the company can default at any time.

The CreditGrades model employs these parameters.

  • company specific recovery rate
  • global recovery rate
  • standard deviation of the global recovery rate
  • stock price
  • stock volatility
  • debt per share
  • risk-free rate

These parameters are easily obtained, either from the market or from a company balance sheet. Hence, the CreditGrades approach is more practical than other models. However, several researchers have found that implied volatility give more accurate predictions than historic volatility.

This Excel spreadsheet implements the CreditGrades model.

 Download Excel Spreadsheet to Calculate CDS Spread with CreditGrades Model


-->

11 thoughts on “Credit Default Swap Calculator in Excel

  1. Nice little valuation. I am not sure how to calcualte some of the standard dev stuff so am leaving them as set. What does the code do with the equity vol.

    I can’t seem to be able to get into the code.
    Regards
    Kevin

  2. Hi. I’m not how I get the calculator to price after adjusting the inputs? Is there a “calculate” button? Thanks.

      1. Hi Samir. I did enable them, but somehow it is not working. If I use Excel, then it shows up with an error of “#NAME?”

        If I use Numbers (I’m on a Macintosh), then the outputs are hardcoded. Seems that potentially the program automatically did that.
        Is there a way we can speak? Or, can you email me an excel file directly?

        Thanks,
        Peter
        [email protected]

        1. The spreadsheet definitely works on Excel 2010 and Windows 7 – I’ve just tried this. I could email you the spreadsheet but it’s the one posted here, so that won’t make a difference.

          I remember reading that VBA was removed from the Mac release of Excel 2008. Perhaps this is reason the spreadsheet won’t work for you.

  3. Hi!I’m very new to modelling and I’m trying to collect quarterly data from Bloomberg to implement model and I’m just at a loss of where to start.Can anyone offer me any help on how I can go about this. I’m really desperate and in need and would appreciate the help.

  4. I’m having a problem implementing the calculator.The survival probability and credit spread adjsut accordingly when I input the different parameters but everytime i attempt to change the equity volatility the output is #### for probability and zero for spread. Any idea why?Please help urgently.

  5. Hi Samir. Thanks for this file – it was very useful! I was wondering if you could give me the password to unprotect the sheet? I needed to make a few adjustments to the code and was hoping that you could help me out here?

    Kind regards,
    Kamran

Leave a Reply

Your email address will not be published. Required fields are marked *

What is 14 + 2 ?
Please leave these two fields as-is:
IMPORTANT! To be able to proceed, you need to solve the following simple math (so we know that you are a human) :-)