Cash CDO Modelling in Excel A Step by Step Approach

by Smith, Darren; Winchie, Pamela

• ISBN13: 9780470741573
• ISBN10: 0470741570
• Edition: 1st
• Format: Hardcover
• Copyright: 2010-06-28
• Publisher: Wiley

Summary

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. A CD-ROM containing these Excel examples will accompany the book.

Author Biography

DARREN SMITH, London, UK, currently heads the Credit Strrcturing team at WestLB. He has over 12 years’ experience in the cash CDO market, and has executed numerous transactions in asset classes as diverse as loans, bonds, emerging market bonds, asset backed securities, CDOs and credit default swaps. Prior to WestLB Darren worked in CDOs at PaineWebber, UBS, and was co-head of CDOs at Dresdner Kleinwort. He has spoken at numerous conferences including the Global ABS conference, Barcelona, and CDO Europe conferences sponsored by Opal. Darren holds a bachelor’s degree in Electronic Engineering from the University of South Australia.

PAMELA WINCHIE, London, UK, is currently a Managing Director at Cross Point Capital, London. Prior to this Pamela held positions as a Director in the European CDO group at Barclays Capital, and a Director in the CDO group at Dresdner Kelinwort. She has over 10 years’ experience both as a cash CDO structurer and as a laywer involved in corporate, securities and securitization law. She has modelled and structured numerous CDOs in various currencies with a range of underlying assets and has spoken at a number of conferences including the CRE-CDO Summit, London, the European CDOs & Credit Derivatives Conference sponsored by IMN and the International Structure Product Conference. Pamela holds a bachelor’s degree in Mathematics and Statistics from the University of Western Ontario and a Juris Doctor from Osgoode Hall Law School.

Table of Contents

Acknowledgments

Introduction

To Excel or Not to Excel?

Existing Tools and Software

What is a Cash CDOs?

Types of CDOs

Description of a Cash Flow CDO

Life Cycle of a Cash CDO

Contribution to the "Credit Crunch"

Introduction to Modelling

Goals in Modelling

Modelling Philosophies and Trade-Offs

Flexibility

Organization and Layout of a Model

Life-Cycle Issues: Building an Adaptable Model

Prerequisites to Cash Flow Modelling

Modelling Dates

Interest Rate Curve Modelling

Present Value Modelling

Getting Started

Create the Input Sheet

The Value of Labelling

Modelling Assets

Initial Asset Pool: Rep Line Modelling vs. Actual Assets

The Collateral Sheet in the Cash Flow Model

Modelling Defaults and Recoveries

Amortization

Modelling Reinvestment

Reinvestment Cohorts

Accounts

Timing Models vs. Actual Timing

Simple Warehouse Modelling

Basic Waterfall Modelling

Basic Waterfalls

Layout and Design

Avoiding Negative Values

Timing Modelled vs. Actual Timing

Liabilities Cash Flows

Fees and Expenses Cash Flows

Interest Waterfall

Interest Waterfall (Available Funds after Payment)

Interest Waterfall Calculations

Principal Waterfall

Principle Waterfall (Available Funds after Payment)

Principal Waterfall Calculations

Adding Over-Collaterization Tests

Adding Interest Coverage Tests

Technical Issues with Coverage Tests

Outputs Sheet

Purpose of the Outputs Sheet

Collating Waterfall Outputs

Present Value

Duration

Weighted Average Life and Internal Rate of Return

Equity Analysis

Basic Auditing

Moody's Rating Agency Methodology

Introduction to Agency Methodologies

The Bet Approach

Evaluating the Collateral

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

Default Profiles

Interest Rate Profiles

Running the Analysis

Variations on the BET

2009 Methodology Update

Standard & Poor's Rating Methodology

The S&P Approach

Evaluating the Collateral

Modelling Recovery Rates

CDO Evaluator

Default Rates

Interest Rate Stresses

Amortization

Additional S&P Modelling Criteria

Building the S7P Sheet and Related References

Running the Stress Scenarios

Advanced Waterfall Modelling

Hedge Agreements

Fixed Notes

Variable Funding Notes

Liquidity Facilities

Interest Reserve Accounts

Other Structural Features

Combination Notes

Collateral Manager Equity Analysis

Maintaining the Cash Flow Model

Adapting Your Model for Different Capital Structures

Audit Sheet

Debugging

Advanced Structuring Issues

Projecting Accrued Interest

Collating Collateral Cash Flows

Sourcing and Integrating Data From External Systems

Data Requirements

Trustee Reports

Bloomberg

Loan Level Information Sources

Regulatory Applications of CDO Technology

The Basel Accords

Regulatory Capital Requirements for CDO Notes

The Standardized Approach for CDOS

The Internal Ratings-Based Approach for CDOS

The Internal Ratings-Based Approach for CDOS: The Ratings-Based Approach

The Internal Ratings-Based Approach for CDOS: The Supervisory Formula Approach

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

Supervisory Provisions

Updates to Basel II

CDO Valuation

Introduction

Basic Valuation Approaches

Traditional Underwriter Analysis

Fundamental Cash Flow Analysis

Using Rating Agency Models

Transition Matrices

Conclusion

In Conclusion

Index

Table of Contents provided by Publisher. All Rights Reserved.