Credit Default Swap Calculator in Excel

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.

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.

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?

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.

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.

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?