Cash CDO Modelling in Excel E-Book

Darren Smith

• Herausgeber: John Wiley & Sons
• Kategorie: Fachliteratur
• Sprache: Englisch

Seitenzahl: 578

Veröffentlichungsjahr: 2011

Beschreibung

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. 

Leseprobe

Contents

Cover

Half Title page

Title page

Copyright page

Dedication

Foreword

Acknowledgments

Chapter 1: Introduction

1.1 To Excel or Not to Excel?

1.2 Existing Tools and Software

Chapter 2: What are Cash CDOs?

2.1 Types of CDOs

2.2 Description of a Cash Flow CDO

2.3 Life Cycle of a Cash CDO

2.4 Contribution to the “Credit Crunch”

Chapter 3: Introduction to Modelling

3.1 Goals in Modelling

3.2 Modelling Philosophies and Trade-Offs

3.3 Flexibility

3.4 Organization and Layout of a Model

3.5 Life-Cycle Issues: Building an Adaptable Model

Chapter 4: Prerequisites to Cash Flow Modelling

4.1 Modelling Dates

4.2 Interest Rate Curve Modelling

4.3 Present Value Modelling

Chapter 5: Getting Started

5.1 Create the Input Sheet

5.2 The Value of Labelling

Chapter 6: Modelling Assets

6.1 Initial Asset Pool: Rep Line Modelling vs. Actual Assets

6.2 The Collateral Sheet in the Cash Flow Model

6.3 Modelling Defaults and Recoveries

6.4 Amortization

6.5 Modelling Reinvestment

6.6 Reinvestment Cohorts

6.7 Accounts

6.8 Timing Models vs. Actual Timing

6.9 Simple Warehouse Modelling

Chapter 7: Basic Waterfall Modelling

7.1 Basic Waterfalls

7.2 Layout and Design

7.3 Avoiding Negative Values

7.4 Timing Modelled vs. Actual Timing

7.5 Liabilities Cash Flows

7.6 Fees and Expenses Cash Flows

7.7 Interest Waterfall

7.8 Interest Waterfall (Available Funds After Payment)

7.9 Interest Waterfall Calculations

7.10 Principal Waterfall

7.11 Principal Waterfall (Available Funds After Payment)

7.12 Principal Waterfall Calculations

7.13 Adding Over-Collateralization Tests

7.14 Adding Interest Coverage Tests

7.15 Technical Issues with Coverage Tests

Chapter 8: Outputs Sheet

8.1 Purpose of the Outputs Sheet

8.2 Collating Waterfall Outputs

8.3 Present Value

8.4 Duration

8.5 Weighted Average Life and Internal Rate of Return

8.6 Equity Analysis

8.7 Basic Auditing

Chapter 9: Moody’s Rating Agency Methodology

9.1 Introduction to Agency Methodologies

9.2 The BET Approach

9.3 Evaluating the Collateral

9.4 Creating the Moody’s Sheet and Related References in the Cash Flow Model

9.5 Default Profiles

9.6 Interest Rate Profiles

9.7 Running the Analysis

9.8 Variations on the BET

9.9 2009 Methodology Update

Chapter 10: Standard & Poor’s Rating Methodology

10.1 The S&P Approach

10.2 Evaluating the Collateral

10.3 Modelling Recovery Rates

10.4 CDO Evaluator

10.5 Default Rates

10.6 Interest Rate Stresses

10.7 Amortization

10.8 Additional S&P Modelling Criteria

10.9 Building the S&P Sheet and Related References

10.10 Running the Stress Scenarios

Chapter 11: Advanced Waterfall Modelling

11.1 Hedge Agreements

11.2 Fixed Notes

11.3 Variable Funding Notes

11.4 Liquidity Facilities

11.5 Interest Reserve Accounts

11.6 Other Structural Features

11.7 Combination Notes

11.8 Collateral Manager Equity Analysis

Chapter 12: Maintaining the Cash Flow Model

12.1 Adapting Your Model for Different Capital Structures

12.2 Audit Sheet

12.3 Debugging

Chapter 13: Advanced Structuring Issues

13.1 Projecting Accrued Interest

13.2 Collating Collateral Cash Flows

Chapter 14: Sourcing and Integrating Data From External Systems

14.1 Data Requirements

14.2 Trustee Reports

14.3 Bloomberg

14.4 Loan Level Information Sources

Chapter 15: Regulatory Applications of CDO Technology

15.1 The Basel Accords

15.2 Regulatory Capital Requirements for CDO Notes

15.3 The Standardized Approach for CDOs

15.4 The Internal Ratings-Based Approach for CDOs

15.5 The Internal Ratings-Based Approach for CDOs: The Ratings-Based Approach

15.6 The Internal Ratings-Based Approach for CDOs: The Supervisory Formula Approach

15.7 The Internal Ratings-Based Approach: Liquidity Facilities, Overlapping Exposures, Credit Risk Mitigation and Early Amortization Features

15.8 Supervisory Provisions

15.9 Updates to Basel II

Chapter 16: CDO Valuation

16.1 Introduction

16.2 Basic Valuation Approaches

16.3 Traditional Underwriter Analysis

16.4 Fundamental Cash Flow Analysis

16.5 Using Rating Agency Models

16.6 Transition Matrices

16.7 Conclusion

Chapter 17: In Conclusion

Index

Cash CDO Modelling in Excel

For other titles in the Wiley Finance series please see www.wiley.com/finance

This edition first published 2010© 2010 John Wiley & Sons, Ltd

Registered officeJohn Wiley & Sons Ltd, The Atrium, Southern Gate, Chichester, West Sussex, PO19 8SQ, United Kingdom

For details of our global editorial offices, for customer services and for information about how to apply for permission to reuse the copyright material in this book please see our website at www.wiley.com.

The right of the author to be identified as the author of this work has been asserted in accordance with the Copyright, Designs and Patents Act 1988.

All rights reserved. No part of this publication may be reproduced, stored in a retrieval system, or transmitted, in any form or by any means, electronic, mechanical, photocopying, recording or otherwise, except as permitted by the UK Copyright, Designs and Patents Act 1988, without the prior permission of the publisher.

Wiley publishes in a variety of print and electronic formats and by print-on-demand. Some material included with standard print versions of this book may not be included in e-books or in print-on-demand. If this book refers to media such as a CD or DVD that is not included in the version you purchased, you may download this material at http://booksupport.wiley.com. For more information about Wiley products, visit www.wiley.com

Designations used by companies to distinguish their products are often claimed as trademarks. All brand names and product names used in this book are trade names, service marks, trademarks or registered trademarks of their respective owners. The publisher is not associated with any product or vendor mentioned in this book. This publication is designed to provide accurate and authoritative information in regard to the subject matter covered. It is sold on the understanding that the publisher is not engaged in rendering professional services. If professional advice or other expert assistance is required, the services of a competent professional should be sought.

Library of Congress Cataloging-in-Publication Data

Smith, Darren.Cash CDO modelling in Excel : a step by step approach / Darren Smith and Pamela Winchie.p. cm.ISBN 978-0-470-74157-31. Collateralized debt obligations–Mathematical models. 2. Credit derivatives–Mathematical models. 3. Microsoft Excel (Computer file) I. Winchie, Pamela. II. Title.HG6024.A3S566 2010332.63′2–dc22

2010005595

A catalogue record for this book is available from the British Library.

ISBN 978-0-470-74157-3

Dedicated in loving memory to Graham, Josie and Stephen

Foreword

The fixed income markets have always been centres of innovation and creativity. This much is apparent from even a cursory glance at developments in recent and not-so-recent history. However it is only in the last thirty years or so that such innovation has really been required, as economic markets changed significantly and capital started to move freely. The bond markets have been the conduit through which vital capital has been raised; continuing product development in the markets has made a significant, and irreplaceable, contribution to global economic progress. The range of products available is vast and always growing, as the needs of both providers and users of capital continually alters in response to changing conditions. This economic dynamic means that market participants observe a state of constant learning, as they must if they are to remain effective in their work. Inevitably we are required to become specialists, as each segment of the debt markets demands increasingly complex approaches in addressing its problems and requirements.

Of course, users of capital are not limited to existing products for raising finance or hedging market risk exposure. They can ask an investment bank to design an instrument specifically to meet their individual requirements, and target it at specific groups of customers. For example it is arguable whether the growth of some of the so-called “credit-card banks” in the United States could have occurred so rapidly without the securitisation mechanism that enabled them to raise lower-cost funding. Witness also the introduction of the synthetic collateralised debt obligation (CDO), allied with a credit derivative, following rapidly on the development of more conventional CDO structures and designed to meet purely credit risk management requirements. The increasing depth and complexity of the markets requires participants to be completely up-to-date on the latest analytical and valuation techniques if they are not to risk being left behind. It is clear that we operate in an environment in which there exists a long-term interest in the application of ever more accurate valuation and analytical techniques.

The arcane and specialist nature of the structured finance markets means that as a topic they are rarely reviewed in the mainstream media. This contributed to a great deal of misunderstanding amongst legislators, journalists, the general public and even some regulators in the wake of the financial crash of 2007-2008. That “CDOs” were stated by some to have been the cause of the crisis reflects the general level of ignorance at all levels. This is unfortunate. To blame the crash on financial engineering is akin to blaming cars for road deaths. Legislation in the wake of a rise in road fatalities is usually connected with making the roads safer, not banning cars. Without a doubt, heavy losses on holdings of structured credit securities were behind the trouble at some banks, but amongst the high-profile bank failures were a number of institutions that did not hold such assets, and had instead neglected their liquidity management. The simple fact is that securitisation and financial innovation have been a force for much good in the world, particularly in an era of globalisation. To take one example, one should know that the mobile phone industry is a large user of capital markets finance. To witness, as I have done, a rickshaw puller on the streets of Dhaka, average salary $1 per day, using a mobile phone is to observe the social benefits of a free market in capital, technical innovation and financial engineering coalescing in one exotic moment.

Speaking personally, I stress the importance of constantly staying at the leading edge of financial market research and development to ensure that, as bankers, we continue to deliver quality and value to our clients. Much of the innovation and product development in the markets originates from an ongoing discussion with the client base, as banks seek to meet their customer requirements.

That is why this book, from two experienced practitioners, is such a welcome publication. It is a rare beast in the universe of finance literature in actually telling one how to do something, rather than being simply an academic treatise on how one does things in a classroom. It is the authors’ clarity of approach and focus that I~am most excited about. They provide insight into practical techniques and applications used in the structured finance markets today. The content also sheds light on the scope and significance of these techniques in the world of finance. I am impressed by the level of detail herein on exactly how to go about building the cash flow model, something that I believe would be of use to a wide range of finance professionals, not just those concerned with structuring CDO transactions.

Another feature about this book that I personally recommend is its value for first-time practitioners. If one is working on an asset-backed security (ABS) or CDO transaction at a bank that has not previously closed such a deal, then this is a useful reference to have on the desk. Post-credit crunch, many banks that had not previously originated ABS deals sought to close “in-house” transactions to create collateral for use at the European Central Bank and Bank of England repo windows. The contents of this book would be of great interest to such bank practitioners. As such, this book deserves a wide readership.

It is a privilege to be asked to write this foreword. The authors have produced a work of the very highest quality. As focused as it is comprehensive, this is an excellent contribution to the literature and sure to become a key reference work for anyone with an interest in the securitisation and structured finance markets. My hope is that this exciting and interesting new book spurs readers on to their own research and investigation; if they follow the application and dedication evident in this work, they will not be going far wrong!

Professor Moorad ChoudhryDepartment of EconomicsLondon Metropolitan University30 March 2010

Acknowledgments

As we discovered along the way, a book is a team effort and we have many people to thank for their contributions. These include Geoff Chaplin and Moorad Choudhry for their insight, encouragement and assistance in helping us to get this book published. We would like to express our deep gratitude to Francis Richard Pereira, Dacil Acosta and Simon Chantry for being our guinea pigs. Richard is an Investment Actuary specializing in Fixed Income and Alternative Investments. Richard is highly regarded in this field and has worked for JP Morgan as an Executive Director in the Structured Alternative Investments business area. Dacil has worked as a CDO structurer for Merrill Lynch and Dresdner. Prior to that, she worked at Standard and Poor’s as a CDO Ratings Analyst. Simon is a senior member of the Structured Credit team at Sumitomo Mitsui Banking Corporation’s European business, focusing mainly on balance sheet structures. Prior to that, he also worked as a CDO Ratings Analyst at Standard and Poor’s. All of the above people gave their personal time, and their work on the book should not be construed as an endorsement or recommendation from their employers past or present.

Darren would also like to acknowledge the following people who have helped and influenced him over the past 12 years: Pat Gallaway, Mari Kawawa, Eddie Lee, Gerrard O’Connor and Arturo Cifuentes.

Pamela would like to acknowledge the many people who have helped and inspired her over the years, including Sandra Kiss and The Honourable Mr Justice Morris Perozak.

Finally we would like to thank our families. Darren would like to thank Heather, Luke, Elizabeth and Mitchell for their patience, support and encouragement. Pamela would like to thank her parents, Terry and Diana, and her brothers, Stephen and Alexander, who have supported and encouraged her beyond words.

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

In a managed transaction, typically a third party asset manager is chosen to select the portfolio and to manage it during the life of transaction. Typically the manager has the ability to remove “credit impaired” and “credit improved” assets at any time and may also have a discretionary trading allowance, normally up to 15 or 20 per cent of the total portfolio balance per annum. Additionally, as the assets may prepay or amortize earlier than the intended life of the liabilities, the manager may reinvest the prepaid or amortized amounts from the assets during the reinvestment period. The purpose of the manager is to enhance the return to the equity investors and minimize the default risk to all the investors in the CDO, in return for a fee.

Static transactions usually have no provision for removal or replacement of assets or reference obligors, although they may sometimes permit replenishment of risk following redemption according to agreed replenishment criteria. The SPE is typically invested in the same assets or credits for the life of the transaction. Hence if there are rating downgrades or defaults in the assets, there is nothing that can be done inside the CDO to mitigate this apart from restructuring it.

After credit risk, often the largest issue for investors is reinvestment risk. Assets can often prepay principal depending on the interest rate and economic conditions at the time. The risk in a managed CDO during the reinvestment period is that market conditions can change and it can be difficult to find replacement investments on similar terms and/or with a similar risk profile. For example, a CDO can be structured with a covenant that the weighted average spread over LIBOR must meet a minimum threshold. After a few years into the reinvestment period, spreads in the market may have contracted and the portfolio manager will be unable to find reinvestment assets that allow the portfolio to meet the minimum weighted average spread covenant without increasing their risk appetite (i.e., lowering the credit quality). Thus, returns to investors can be damaged (particularly at the equity or first loss level) if large amounts of cash are being held in the transaction at a lower return or assets with higher default risk default more frequently than was initially expected at the closing date of the transaction. This risk is not present within static CDOs. (It should be noted, however, that from an investor’s point of view, with a static CDO where principal payments are being used to pay down principal of the CDO notes, an investor must look to reinvest funds outside the CDO when principal payments are recieved. Thus the reinvestment risk is still present, but from an investor’s viewpoint it has been moved outside the CDO structure and is under their control.)

2.1.3 Full capital structures versus single tranches

Cash flow CDOs tend to be full capital structure deals. This means that the issuer (or SPE) will sell a similar amount of liabilities to pay for the assets that it purchases. This is in contrast to the synthetic CDO (or CSO), where only tranches representing a small fraction of the capital structure are sold, typically the middle (or mezzanine) tranches which are often initially rated from AA to BB. The underwriting investment bank or originator will often retain the more subordinated unrated first loss (or equity) risk and the risk of the most senior tranches (commonly referred to as the “super senior” liabilities).

2.1.3.1 Cash flow (asset-liability matched) or market value

Most cash flow CDOs issue debt which matches the longest maturity of the assets so there is no refinancing risk for the CDO. An alternative approach is to deliberately mismatch the maturity between assets and liabilities by using short-term debt instruments such as commercial paper or repurchase agreements. These short-term debt instruments allow for additional yield pickup by issuing debt lower on the yield curve than the assets. The mismatch between the assets and liabilities can be typically addressed by using a combination of a liquidity provider and through market value covenants.

If the assets are not particularly liquid then a liquidity provider is needed to purchase the commercial paper or provide alternative means of financing either short term or long term if the short-term debt cannot be funded in the market. This was clearly demonstrated in the unwinding of the SIV market, where originating banks were often the liquidity providers to SIVs, and consequently they were obliged to purchase the maturing commercial paper from investors and were unable to sell it into the commercial paper market.

If the underlying assets have a sufficiently liquid market then funding can be advanced against the market value less a suitable haircut for price volatility. This is often called the “advance rate”: the percentage of the market value against which funds are lent and is often only used for the most senior portion of the transaction’s liabilities. The issuer may fund the rest of the asset purchases by issuing term-funded mezzanine notes and equity or capital notes. The issuer will covenant to maintain a minimum value of the portfolio with regard to the short-term debt. In the event of a breach of these covenants then several remedies can be enforced, including: (a) directing principal cash flows from the assets to pay down the principal on the senior notes; (b) preventing the issuance of more short-term debt; and (c) liquidating some or all of the assets to repay some or all of the outstanding principal of the short-term debt.

2.1.3.2 Asset types

Cash flow CDO technology has been developed for many asset types and is being continually developed and extended. At the time of writing, CDOs have been issued backed by the following asset types:

bonds (investment grade, high yield, emerging markets);loans (high yield);asset backed securities;hedge funds;private equity;real estate investment trust debt (or REITS);trust preferred debt (insurance/bank);commodities.

2.2 DESCRIPTION OF A CASH FLOW CDO

A cash flow CDO commonly has some or all of the following elements:

a bankruptcy remote special purpose entity (SPE) which purchases the assets and issues the notes;a security trustee holds the secured interest in the assets of the SPE on behalf of the note holders and other secured creditors;a custodian holds the assets usually via a clearing system such as the DTC or Euroclear/Clearstream;a note depository holds the physical notes so that they can be “dematerialized” and traded “book entry”. This is typically the DTC or the same entity as the custodian;an account bank may hold cash in bank accounts, or these may be administered by the trustee in non-interest bearing cash accounts which are invested in cash or cash equivalent securities;a collateral administrator performs the collateral tests and reporting functions as required by the transaction documents;the issued notes are rated by one or more rating agency;the SPE purchases the assets typically from a warehouse facility provider (usually a fund or bank);the assets are typically managed by an asset manager in exchange for fees;the issuance is underwritten by an investment bank that typically places (or sells) the notes to their clients.

The notes issued by the SPE will have various ratings from AAA/Aaa to lower investment grade or even speculatively sub-investment grade. The higher the rating on the notes, the lower return they will generally earn. Table 2.1 is a sample capital structure from 2006.

Table 2.1 Sample capital structure

The notes are the SPE’s liabilities and their interest coupons are paid from the interest funds received from the assets. Both interest and principal payments are received from the assets and in turn they are paid to the liabilities in accordance with the priority of payments (colloquially known as the “waterfall”). There are usually separate priorities of payments or waterfalls, applied to the interest and principal proceeds received from the assets. The usual reason for the existence of the priority of payments waterfalls is to ensure that the holders of senior notes are paid interest and principal before the holders of more junior notes.

Table 2.2 is an example of an Interest Waterfall where interest proceeds from the assets are applied.

Table 2.2 Interest waterfall

Table 2.3 is an example of a Principal Waterfall where principal proceeds from the assets are applied.

Table 2.3 Principal waterfall

In order to achieve the desired ratings, the Issuer will usually be required to maintain certain ratios such as “assets to liabilities” and “interest from assets to interest to notes”, i.e., over collateralization ratios and interest coverage ratios. If these ratios are not above certain predetermined thresholds, payments due to lower rated classes will be diverted to the most senior classes in order to pay down the principal in order to try to restore the ratio to the target level.

CDOs are often bespoke, complex structures which can involve various structures and types of notes on the liability side and all sorts of combinations of asset (or reference) pools, each with differing timings, frequency of payments and ultimate redemptions.

2.3 LIFE CYCLE OF A CASH CDO

The life cycle of a CDO can broadly be split into the following phases:

1. pre-close;

2. closing date;

3. pre-effective date;

4. reinvestment period;

5. amortization period; and

6. redemption/call

During the pre-close period, the asset manager selects the assets and instructs the warehouse provider (usually the sponsoring investment bank) to purchase the assets and hold them until the closing date. The sponsoring investment bank usually structures and manages the rating process, and markets the issued notes of the SPE. The culmination of the pre-close period is the pricing date, where investors place their orders to purchase notes on the closing date. Prior to that date, the investors typically have received the preliminary prospectus (sometimes referred to as the “red herring” or the “red” for short).

On the closing date various things happen: (a) the warehouse is typically closed; (b) the assets are transferred to the issuer; (c) the issuer (or SPE) issues the notes, which are bought by the initial purchaser, usually the underwriter; and (d) the notes are sold on to the end investors. Also at the closing, rating letters are issued by the rating agencies, but are often subject to the deal being declared “effective”. Often by the closing date, the transaction has not purchased the full target amount of assets. It is typical for a transaction to close with only 70 to 90 per cent of the final transaction size of assets. If a deal closes without purchasing all of the target notional assets, the ratings issued are preliminary ratings.

A deal goes “effective” once it has purchased the assets necessary to meet the characteristics previously documented and modelled. This typically occurs at the earlier of either the purchase of the entire target notional of assets or six months (also referred to as the “ramp-up period”). On the effective date, the issuer declares that it has reached the target asset amount and that the deal, as rated on the close, is complete. The agencies affirm their ratings if the portfolio has met the target profile. If insufficient assets are purchased by the end of the ramp-up period, then the rating agency may not affirm its ratings and the transaction may have to reduce the notes outstanding by paying junior cash flows to the senior notes until the conditions necessary to maintain the initial ratings are met, unless an alternative plan is accepted by the rating agencies.

After the effective date, the transaction is typically in the reinvestment period. During this time, repayments of the principal amounts of assets are used to purchase replacement assets. The reinvestment period (if it exists) is typically three to five years, provided that the transaction is performing within the documented criteria and covenants.

At the end of the reinvestment period the transaction will start to pay down the outstanding principal of the notes, or “amortize”. As principal amounts on the assets are paid, the proceeds are used to redeem the notes. Typically, CDOs amortize sequentially, paying the most senior notes first, and paying down the junior notes in order of seniority. However, in some static deals and commonly in ABS CDOs, a pro-rata amortization is allowed under certain conditions. A pro-rata amortization is where redemptions are used to pay a portion of each of the notes outstanding, usually with regard to the relevant ratio of the outstanding amount of each note, as a fraction of the total amount of outstanding notes.

A transaction is rarely envisaged to reach its maturity date. Most managed CDOs grant the equity investor or lowest class of notes the right (a call option) to terminate the transaction early. These call rights are usually conditional on the repayment of the more senior notes, and may require the issuer to pay additional payments to make the investor whole with regard to promised return hurdles, particularly for fixed rate liabilities. In addition, many CDOs have “auction call” mechanisms that automatically try to liquidate the underlying assets (as long as the notes can be repaid in full), after a predetermined period. Auction calls usually occur after the end of the reinvestment period, e.g., seven to 10 years. Other call mechanisms include “clean up” call mechanisms to redeem the transaction once the asset balance falls below a certain level, e.g., 20 to 30 per cent of the effective date portfolio size.

2.4 CONTRIBUTION TO THE “CREDIT CRUNCH”

There are many different books available on the causes and consequences of the “credit crunch”, and the authors feel that it makes some sense to touch upon the subject and the role of CDOs in it.

As most readers will be aware, the root cause of the credit crunch was lending by banks and mortgage brokers to finance house purchases by borrowers with poor or non-existent credit history, in a housing market bubble. These mortgages were bought by investment banks and packaged into asset backed securities, called residential mortgage-backed securities or RMBS. These RMBS bonds were rated by the rating agencies and then sold to investors. The relatively high spread and high rating meant that these bonds were attractive investments for both CDOs and banks.

2.4.1 The role of CDOs and credit derivatives

From 2003 until 2007, CDOs, particularly structured finance CDOs, were significant buyers of both RMBS tranches and the risk (synthetically) of RMBS tranches. Often CDOs purchased the tranches that the sponsoring bank had the most difficulty selling. The buyers of the tranches of CDOs largely based their purchase decision upon the ratings on the tranches issued by the rating agencies. In addition, the development of pay-as-you-go (PAUG) credit default swaps on RMBS and CDOs allowed for banks and hedge funds to transfer risk synthetically on tranches they may or may not have held. This allowed banks to hold the senior positions of RMBS CDOs by buying protection in the form of a CDS from a bank or insurance company. This was particularly true for the mezzanine tranches of RMBS bonds and CDO tranches. Another significant factor in the poor price performance and subsequent poor credit performance was inclusion in many CDOs of a large pool of other CDOs tranches, the “CDO squared” problem. The inclusion of CDOs in troubled CDOs made valuation much more difficult and opaque.

2.4.2 The credit crunch

As the first news of poor remittance data on RMBS bonds became known, banks (both investment and commercial) began to tighten lending criteria to clients, mainly hedge funds, on RMBS and CDOs backed by RMBS. Some of these funds were highly leveraged (often 50 to 100 times) and were funded at very low rates. The new lending criteria reduced the hedge fund margins, requiring hedge funds to borrow less and pay more to borrow. This triggered the first sales by these funds to meet the margin calls. However, a lack of buyers for these securities, because of credit concerns, caused the price of these securities to effectively free-fall, as wave upon wave of margin calls resulted in more and more sales. This directly brought about the failure of several large funds as investors in those funds became nervous at the falling NAV (net asset value) of their investments and started to withdraw their funds, causing more sales. Purchases of money market securities (in particular asset-backed commercial paper) fell dramatically as the managers of money market funds, especially 2A7 funds which are supposedly very safe investments, became nervous and refused to refinance maturing asset-backed commercial paper. This initially mainly affected issuers that had large exposures to RMBS and CDOs and caused the default of several large conduits and structured investment vehicles. After this, many of these funds refused to refinance or roll any asset-backed commercial paper at all. This triggered the next phase of the credit crunch with more asset sales which eventually resulted in the collapse of the entire SIV (structure investment vehicle) market (at the time of writing all SIVs had either been consolidated by a sponsor bank or were in administration).

The failure of these large conduits effectively caused their sponsoring banks to default, which then raised the concerns of all banks in lending to other banks. Banks then started to hoard liquidity and refuse to lend to institutions that had any hint of suspect assets on their balance sheets. Central banks began to increase liquidity into the system in an attempt to avert a liquidity crisis. However, institutions without direct access to this liquidity were still vulnerable and it was this lack of liquidity that was the root cause behind the failure of Northern Rock (a mortgage bank in the UK) and Bear Stearns.

Alongside these liquidity concerns, the rating agencies began a massive downgrade programme on structured finance that was linked to the troubled RMBS vintages. This triggered more sales and forced liquidations as so-called events of default linked to ratings downgrades were triggered. Additionally, it caused the market and the agencies to review the mono-line insurers as some of the mono-lines had wrapped (or provided credit default swap protection) on the senior tranches of structured finance CDOs. Most of the mono-line insurers lost their Aaa/AAA ratings and many of them became sub-investment grade or defaulted.

2.4.3 Root causes

The lack of market agreed valuation methods and the complexity of the valuation issues of asset backed securities (including CDOs) made it difficult for counterparties to agree on values. The increase of both mark to market accounting and banks putting credit into their trading books meant that the banks took heavy write-downs as trading book losses directly fed through the income statements. Bids effectively disappeared on some products, leaving banks with valuation problems. This caused wider contagion effects as credit became more difficult for banks and brokers to buy (i.e., lend money) for the fear of taking further write-downs.

2.4.4 The role of fair value accounting

The role of fair value accounting cannot be underplayed. During the development of the credit derivatives markets, banks increased the amount of exposure on their trading books to credit, as there seemed to be a “liquid market” in credit derivatives. These positions were held relatively long term and hedged rather than traded, particularly in the credit derivative space. This treatment tended to carry over for related instruments such as CDOs. Prior to this, only relatively small amounts of credit were on trading books, for market making purposes only. The advantage of trading book treatment was that typically only a small VaR (value at risk) amount plus a counterparty add-on were required from a regulatory capital requirement. Adjustments in price, either positive or negative, fed through the income statement and affected the bank’s earnings and retained reserves.

Prior to this, the majority of credit was seen as illiquid and held in banking books; this typically required more capital and was not “marked to market”or fair valued but held at purchase price with impairment reserves held against them if impaired. Adjustments were through either impairment accounts held against equity, i.e., a balance sheet adjustment, or did not directly affect the balance sheet or income at all until actual losses were incurred. Fair value accounting had a double setback effect on banks in that it initially allowed for significantly increased leverage. However, as credit became increasingly illiquid, the lack of prices increased the capital requirement significantly and reduced the banks reserves as write-downs fed through the bank’s income statements. The main weakness was the lack of a mechanism to move from trading book to banking book, often forcing banks to sell assets well below an intrinsic value.

A further problem is the current treatment of credit in Basel II which requires banks to set capital according to the credit rating. As long as the credit rating is investment grade or higher, banks can allocate a fraction of their capital against the position. However, if the credit rating falls below investment grade, particularly B/B ratings, then a deduction from capital is required. The bank therefore has impairment plus the full capital against that impairment. While reasonable perhaps for corporate credit, this seems especially harsh for large or senior tranches of granular securitizations, as a significant ultimate payment may be expected, i.e., it is not all likely to completely fail, particularly if it is well diversified.

Chapter 3

Introduction to Modelling

3.1 GOALS IN MODELLING

Models have to be able to satisfy many different goals for potentially different users.

Rating agencies use models to determine the credit risk and determine the credit ratings assigned to the notes that are issued. Investors use models to determine returns and note sensitivities to defaults and ratings migration. Mono-line insurers have used their own models to determine the capital requirements to maintain their ratings.

3.2 MODELLING PHILOSOPHIES AND TRADE-OFFS

The aim of any model is to optimize a number of (often contradictory) goals. These goals can include:

speed;flexibility;visibility/audit-ability/verification;degree of automation;ease of change.

Let’s discuss these aims and how balancing these will affect the approach to modelling.

3.2.1 Speed

Speed in this instance refers to the speed of execution or calculations in the model. Various things can be done to improve this and, while not all of them are recommended, if the main goal is to execute many scenarios of one transaction (or group of transactions) in as little time as possible, then they may be worth considering.

Contemplating the likely steps that Excel executes in calculation without seeing the algorithm or code, it can be surmised that Excel may use some version of a tree algorithm to determine both what it needs to calculate and the order of which to calculate it. This means that, in general, Excel will recalculate only those cells that need to be recalculated. When making changes to a cell in a worksheet, Excel will recalculate the cell that has changed and the cells that refer to or depend on that cell. However, there are functions in Excel which will recalculate every time a change is made to any cell in a worksheet. These functions include TODAY(), NOW(), OFFSET() and other array formulas. It is best to avoid them or use them sparingly. In addition to this, when using VLOOKUP() and HLOOKUP() with exact match utilized, the calculation time will be proportional to the number of cells required to be scanned before the solution is found.

3.3 FLEXIBILITY

Unless the plan is to invest in or structure only one type of deal with no variation whatsoever in either collateral type or capital structure some degree of flexibility will be required. Maintaining flexibility in the model will tend to increase the size and complexity of the model and reduce the execution speed. While it is important to be flexible, the cost should not be so high as to saddle the model with too poor a performance, resulting in it taking too long to run effectively. For example, if only single currency deals are to be modelled, building in several other currencies introduces an unnecessary level of complexity and size into the model.

3.3.1 Visibility/audit-ability/verification

If the model is to be readily examined or shared among a number of users, then often it is useful to be able to quickly and easily examine the calculations and data, rather than just the inputs and outputs. For example, it may be useful to examine the construction of the interest coverage and over-collateralization coverage test ratios and the logic before altering the priority of payments. It is also quite reassuring to be able to prove that the model is verifiably accounting for all of the cash flows and is not double counting cash flows or missing cash flows. This can be particularly important to have in place after making significant changes to a model.

There are two main principles to follow in order to gain confidence in a model once changes have been made. One method is the inclusion of an audit process of some sort. The authors are rather big fans of having an “audit” or “verification” sheet. The purpose of an audit sheet is to crosscheck the inputs, outputs and verify the allocation of cash to the appropriate liabilities. While cash flow models are not necessarily very complicated from a calculation point of view, they are generally extremely detailed. Thus, a certain degree of confidence is required when changes are made. All cash must be accounted for correctly and there must be no missing cash or double counting of cash. An audit sheet is used to cross check and verify the model and can also be useful as a summary of the cash flows. The other method is by pervading the model with visible “forensics”, for example, visible ratios, shortfalls and cures, thus making it easier to evaluate shortcomings in the structure.

3.3.2 Degree of automation

Modelling can be performed using Excel at a number of different levels. The authors have devised a naming system to be clear about the degree of sophistication required both for the creator and the user of such models. Generally these are complementary: the more sophisticated the model, the less sophisticated the user is assumed to be. “Level 1” applies to models that use only the functionality native to Excel, i.e., the built-in functions and the Excel engine. A “Level 2” model is mainly written using VBA and spreadsheet use is limited to providing the inputs and outputs of that model. A “Level 3” model is built using a compiled language such as C++, C# or Visual Basic to interface with in Excel, with Excel mainly used for input and output and most of the calculations internal to the compiled code.

Each level involves certain trade-offs and no one approach is necessarily right for any given situation. However, the authors believe that models should be built in such a way that they can evolve to the optimum level to solve the problems at hand.

By using solely what is available in Excel and only modelling at “Level 1”, the model can be operated and understood by anyone familiar with Excel. There is no need to understand programming languages. It is also easier to debug and audit the model as all the logic and intermediate cash flows are visible and accessible. The main disadvantage is that the model tends to be larger and slower and can be tedious to use for repetitive tasks. To meet investor demands or rating agency criteria, quite often a multitude of different stress scenarios on the collateral cash flows are required to be run in the model. Without providing some degree of automation, obtaining the required, often large, number of results can be a slow and tedious procedure, and one prone to human error.

“Level 2” modelling involves delegating some or all of the calculation to logic developed in VBA using Class Modules, Functions and Subroutines. The Excel engine is mainly used to formulate inputs and outputs and does very little of the intermediate work. By using VBA, the extent of calculation can be controlled and there is less likelihood of circular references. Additionally, complicated formulae that may not easily fit into a model, such as interpolation or simulations, can be easily programmed in VBA. The downside of VBA is that it can be harder to change and less transparent to users than an Excel worksheet. Finding and correcting errors can also be tedious and it is much more of a “black-box” approach. Other than the developer, the other users do not know what is going on inside the program. However, anyone familiar with VBA can see and modify the code.

“Level 3” modelling replaces some or all of the VBA code with compiled C/C++/C# code in the form of add-ins. Add-ins can be created either using the Excel ToolKit addin or using OLE/COM/.NET (or other incarnations of Microsoft’s object communication protocol). C++/C/C# replaces some of the shortfalls of using VBA alone, including persistence between calculations, and improved performance for intensive calculations and improved overall performance. However, the cost and time involved in changes to the model are more pronounced. In this instance, the code is more separate from the Excel application and would not be readily available for modification to the end user.

Each change in graduation of level in modelling requires additional degrees of knowledge and skills; with Levels 2 and 3 the skill set required is more computer science than banking. This book will concentrate on developing a “Level 1.5” model. A “Level 1.5” model is mostly implemented using the core Excel functionality and the Excel engine, but adds some limited functionality in VBA to assist in automating repetitive tasks. It has the advantages listed for a Level 1 model but aims to eliminate the tedium and the errors associated with having to manually operate it for repeated operations.

3.3.3 Ease of change

There are many ways to make it easier to make changes or adaptations to the model. For example, when creating worksheets it is always best to avoid merging cells. It is often tempting to merge “title” cells. However, if later columns or rows are added where merged cells are included, it is then more time-consuming to do so. Better practice is to go to the Format menu, choose “Cells”, then choose “Alignment”, then under “Horizontal” choose “Centre Across Selection”.

Another general point is to try to keep the distance and navigation between related cells as short as possible. Ideally, they should be within one screen width or height of each other to allow for rapid movement back and forth between dependent cells.

In addition, taking care that the model is well organized and efficiently laid out will go a long way towards making changes more manageable.

3.4 ORGANIZATION AND LAYOUT OF A MODEL

3.4.1 Organization of a model

A well-organized approach to the design and layout of the worksheets in the model will enable its users to know where to expect to find things. It can be extremely frustrating to have to spend vast amounts of time looking for various parts of a model. It is poor modelling to not model with other users in mind (or even with the view that a particular model may not be looked at for a long period of time) and some of the original reasoning may be forgotten; why, for example, there was an input or calculation in the Excel spreadsheet “wilderness” (such as cell IR5648 on a random worksheet, or worse still, on a hidden worksheet).

By designing worksheets to have specific functions the model can be incrementally improved, modified and adapted. Our model will start with four basic worksheets:

inputs;outputs;collateral/assets;waterfall.

Additional worksheets are often useful for:

hedges;curves;look up tables;rating agency-specific analysis;simulations;equity/break-even analysis.

By breaking out the model into these various worksheets, it allows the modeller to quickly and efficiently make changes to the model. For example, if the modeller wants to update a (“rep line”) asset sheet with actual assets, then that sheet should be relatively easy to replace with minimum impact on the rest of the model. Another example might be where rating agency methods change. If each rating agency’s analysis is on a particular worksheet, then changes can be made with minimal impact on the model.

3.4.2 Layout of the model worksheets

The layout of the waterfall sheet always comes down to a choice of either (1) horizontal or (2) vertical modelling.

A horizontal waterfall sheet is one in which each successive period calculation is in the cells right to left, and the ordering of the waterfall is top to bottom. This has the advantage of clear labelling in the left-hand column as the user scrolls down.

In a vertical waterfall sheet, the waterfall goes from left to right and the periods go from top to bottom. The authors believe that this has the advantage of being easier to read. As there tend to be more rows available than columns, this has the effect of reducing the amount of navigation.

Although each layout has its advantages and disadvantages, the authors recommend a vertical layout and proceed in this book to model in this manner.

3.5 LIFE-CYCLE ISSUES: BUILDING AN ADAPTABLE MODEL

Since Excel 5.0 in 1993, Excel workbooks have been organized as a collection of worksheets in a single workbook format. This provides a sound basis for a functional organization of the model. It also allows for the model to be incrementally improved without altering and compromising the entire existing functionality. An important consideration in designing each functional part of the model is to assess the likelihood of change. By intentionally limiting the links between the different functional parts of the model, it becomes easier to replace the functional modules, as required, in the future. For example, the authors have rewritten and replaced the Collateral Sheets and Waterfall Sheets repeatedly over the time they have been using similar models and have been able to do so without seriously impacting the rest of the model. Minimizing the impact of such changes is possible by linking only the relevant inputs and outputs between the functional worksheets.