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.

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