Cash CDO Modelling in Excel

A Step by Step Approach

Darren Smith, Pamela Winchie

• Publisher: Wiley
• Year: 2011
• Print ISBN: 9780470741573
• eBook ISBN: 9780470971673
• Edition: 1
• Topic: Business
• Subtopic: Finance
• Index: Business

This book is an introduction to the modelling of cash collateralised debt obligations ("CDOs").  It is intended that the reader have a basic understanding of CDOs and a basic working knowledge of Microsoft Office Excel.  There will be written explanations of concepts along with understandable mathematical explanations and examples provided in Excel. 

Chapter 1

Introduction

There has been a lot written on credit derivatives during the past few years. However, much of what has been written about traditional “cash flow” collateralized debt obligations (CDOs) has been of an introductory nature. It has often been written from a research or legal point of view and there has been little discussion about the modelling and evaluation of these structures. In many books, cash CDOs are mentioned as part of a more generalized introduction to asset backed securities. According to data published by the Securities Industry and Financial Markets Association, the cash flow CDO market was over USD 400 billion in 2006. Unfortunately, the market in 2007 through 2009 was overshadowed by the “credit crunch”, largely brought on by sub-prime mortgages, a major contributing factor was structured finance CDOs and their valuation. Contagion effects in the credit market virtually caused the collapse of all lending. A major theme was the mistrust in the markets that arose because of the lack of an agreed-upon valuation technique for structured finance vehicles (including CDOs). Notwithstanding these events, the authors believe that CDOs and specifically the modeling of CDOs, deserves more serious and dedicated attention.

The aim of this book is to introduce the modelling of cash flow CDOs, including construction of cash flows for both the underlying collateral and the issued notes, the evaluation of default probabilities and expected losses for rating agencies, and techniques and approaches that investors may use to value them. A newcomer to the CDO market ideally will be able to use the ideas in this book to construct her or his own models. A wider aim of this book is to encourage and promote discussion and debate about the modelling, evaluation and valuation of cash flow CDOs.

The authors acknowledge that there is not necessarily one right way to model. Every model is a compromise between several objectives including speed, flexibility, visibility, degree of automation, ease of change and verification. The book expounds the authors’ views on best practice and utilizes their experiences in discussing the advantages and disadvantages of different approaches.

This book adopts a step-by-step approach to building a rudimentary model so that any reader who “sticks the course” will have a useful tool to evaluate cash flow CDOs and a template that can be built upon to suit personal taste and requirements.

1.1 TO EXCEL OR NOT TO EXCEL?

When cash CDOs were first being modelled, most modellers used spreadsheets as there was no dedicated software available. Over time, investment banks, large investors and collateral managers have developed or purchased licenses for dedicated CDO systems. These systems have varied from management tools to modelling and evaluation tools, depending on the needs of the users.

There are strengths and weaknesses to every system and tool. Microsoft Office Excel’s biggest strength is that it allows for a great deal of flexibility: trivial changes to a model can be done with relative ease. However, when changes are made that are more than trivial, without a disciplined and organized approach, this ease of change can quickly become Excel’s biggest weakness. One of the themes of this book is consistent application of organization to avoid the chaos that can easily creep into a workbook model making it unusable over the medium to long term. This book will discuss techniques to layer a model design, by taking advantage of the spreadsheet layout. By limiting the links between the functional parts of the model, it is easy to replace those functions in the future. The authors have replaced Collateral Sheets and Waterfall Sheets on several occasions during the time they have been using similar models without impacting the rest of the model. This is achieved by limiting links between the inputs and outputs between the functional worksheets.

Most cash flow CDOs are bespoke: although they may start from a general template, they are customized investments that are tailored to specific investor requirements. Once a modeller has created a basic model using spreadsheets, the flexibility exists with Excel to quickly model and test new CDO structures. In contrast, if software or systems are developed away from spreadsheets, extensive support from a programmer may be required to make changes or the modeller may have to learn to program in a higher-level programming language. This can significantly delay the evaluation of a new feature or structure.

Another benefit to using Excel worksheets for cash flow modelling is their origin and pedigree in auditing and accounting. Worksheets still offer one of the best frameworks on which to base an audit tool. Even rating agencies use worksheets as the basis for the tools they offer.

This book assumes a certain familiarity and working knowledge of Excel. Should the reader find their knowledge insufficient, then one of the many excellent books on Exel should help remedy the situation.

1.2 EXISTING TOOLS AND SOFTWARE

What are the alternatives to using bespoke spreadsheets to evaluate CDOs? While the authors do not advocate any one of these systems and this book is not intended to be an advertisement for any of these systems, they believe it is important for the reader to know that there are alternatives available. Generally these can be broken down into:

• CDO management systems usually provided by trustees or other third parties to enable investors and asset managers to evaluate changes to the underlying asset/risk portfolio.
• Third party data and modelling systems mainly used by investors to track their portfolios without the onerous task of updating from trustee reports. Often these systems provide little or no analysis facilities but can be extended by bespoke development, either by the supplier or the licensee.
• Rating agency supplied systems, which frequently do not deal with the underlying structure and mainly model the performance of the underlying asset portfolio according to the rating agencies, criteria. At the time of writing, the exception to this is CDOEdge, which is a tool that Moody’s Investor Services sell to model cash flow transactions to their methodology.
• Analysis systems which, to be successful, typically have a mechanism to encode the priority of payments cash flows of the CDO. They will also have means to do default, interest rate or other scenario analysis either by simulation or scenarios.

These systems are often expensive and require the vendor to maintain them. The modelling explained in this book is not necessarily looking to replace these systems but complement them. Often it is useful to interface spreadsheet models to these systems to avoid duplication and maintenance of underlying data.

Chapter 2

What are Cash CDOs?

2.1 TYPES OF CDOs

This book is intended as a guide to modelling CDOs. It is not an introductory book to all aspects of CDOs. There are many existing books that discuss the legal, accounting, regulatory and other aspects of CDOs. Nevertheless, it is worthwhile from a completeness perspective to briefly discuss what a CDO is.

The first incarnations of CDOs were CBOs (collateralized bond obligations) of high yield bonds and CLOs (collateralized loan obligations) of leveraged loans. The concept of these structures was then extended to many more asset classes, including investment grade bonds, asset backed securities, real estate investment trusts (REITs), hedge fund units, private equity shares, trust preferred bonds, derivatives (such as credit default swaps) and equity (through either shares, equity default swaps, and/or options and commodities).

CDOs can be categorized by their various different attributes in many different ways, some of which are listed below:

• cash, synthetic, or hybrid assets;
• managed or static;
• full capital structures or single tranche technology;
• cash flow (asset-liability matched) or market value;
• asset classes.

Another way to categorize CDOs is by their primary function. CDO technology may be used to achieve one or more of the following goals:

• credit risk transfer;
• funding illiquid assets;
• leveraged return on credit assets;
• regulatory and/or economic capital relief.

It should be remembered that a CDO, particularly a cash CDO, is not an asset class in its own right but a financing technique particularly suited to illiquid assets. It is therefore only as robust as the assets that are put into it. It is like a “mini-bank”: it raises capital by selling debt and “equity”, and invests the money raised into assets to generate an “excess” return. Cash CDOs are often called “arbitrage” CDOs as the assets are worth more repackaged than as individual securities.

2.1.1 Cash, synthetic or hybrid CDOs

Cash CDOs involve assets that are typically securities, such as bonds, but can also include bi-lateral or multi-lateral debt contracts, such as loans. The assets are transferred to a bankruptcy remote special purpose entity (SPE) as the registered owner and are paid for by the selling of liabilities (or notes). Figure 2.1 illustrates this. The assets often do not have the same terms with regard to payment dates, redemption schedules or maturity dates. Hence much of the structuring of the CDO involves matching certain characteristics of the liabilities with certain characteristics of the assets.

Figure 2.1 Cash CDO structure

By contrast, synthetic CDOs are based on the transfer of risk, typically by the use of credit derivative contracts (usually using standardized terms from the International Swap Dealers Association (ISDA)). The underlying assets/contracts that they refer to may be held by a sponsor bank or other financial institution, but increasingly are sourced from the credit derivatives market. The underlying contracts usually have the same terms with regard to payment dates and maturity date, so there is little or no mismatch between the risk and the protection. Recent developments in the establishment of centralized clearing, and the “big bang” protocols which amongst other things standardized premiums, have homogenized the market further.

A cash transaction has a more complex “Priority of Payments” (colloquially called a “waterfall”), than a synthetic transaction. Synthetic CDOs or CSOs normally rely solely upon subordination to support the credit ratings of the notes, hence the reason they are sometimes referred to as “write down” structures.

Cash flow transactions typically have covenants regarding interest coverage (i.e., interest income versus interest liability servicing) and over-collateralization coverage (i.e., assets to liabilities ratio). Failure of these covenants diverts income from the assets due to the junior notes to accelerate the senior notes. These covenant tests effectively provide contingent additional subordination so that the initial subordination in a cash flow transaction is usually less than the subordination on a synthetic transaction on an equivalent portfolio. In addition, cash flow CDOs often require additional structuring to address risks that are not present in synthetic CDOs, such as interest timing mismatches, currency risks, prepayment risks and reinvestment risks.

In a synthetic (or “write-down”) structure, the notes are used to support the credit protection sold. Once a claim on that credit protection is made, the notional of the note is reduced by the default amount and is written-down immediately. Recoveries (if any), when they are realized, are used to pay down the super senior swap. This payment effectively increases the detachment point of the most junior swap by the recovery amount. In contrast to a cash flow transaction, the notes are not written down until the maturity of the transaction because losses can be redeemed from excess interest proceeds (Figure 2.2).

Figure 2.2 Synthetic (write-down) structure

Hybrid transactions incorporate both synthetic and cash CDO features and allow for both cash assets and synthetic securities, and cash and synthetic liabilities. Additionally, hybrid transactions allow for the ratio of cash and synthetic assets and liabilities to change. As many cash flow transactions allow for a bucket of 10 to 20 per cent synthetic securities, to be considered truly hybrid a transaction typically has more than 20 per cent synthetic securities. Additionally, many hybrid transactions allow for “short” buckets as well, which means that the SPE can effectively hedge risk positions by buying protection on obligors. This also allows for “basis trades”.

Basis trades are long–short positions on the same obligor risk where the “long” default risk is completely offset by a short “protection” position, ideally at a lower spread. This allows for an earned income that is independent of the underlying credit risk, although there is still, typically much lower, risk from counterparty credit.

Figure 2.3 Elements of a cash flow CDO

2.1.2 Managed or static CDOs

I...

Table of contents

• Cover
• Half Title page
• Title page
• Copyright page
• Dedication
• Foreword
• Acknowledgments
• Chapter 1: Introduction
• Chapter 2: What are Cash CDOs?
• Chapter 3: Introduction to Modelling
• Chapter 4: Prerequisites to Cash Flow Modelling
• Chapter 5: Getting Started
• Chapter 6: Modelling Assets
• Chapter 7: Basic Waterfall Modelling
• Chapter 8: Outputs Sheet
• Chapter 9: Moody’s Rating Agency Methodology
• Chapter 10: Standard & Poor’s Rating Methodology
• Chapter 11: Advanced Waterfall Modelling
• Chapter 12: Maintaining the Cash Flow Model
• Chapter 13: Advanced Structuring Issues
• Chapter 14: Sourcing and Integrating Data From External Systems
• Chapter 15: Regulatory Applications of CDO Technology
• Chapter 16: CDO Valuation
• Chapter 17: In Conclusion
• Index