Modeling Structured Finance Cash Flows with Microsoft Excel - Keith A Allman

. .

Auteur(s) : Keith A Allman

Editeur : John Wiley & Sons

Langue : Anglais

Parution : 01/02/2007

Format : Moyen, de 350g à 1kg

Nombre de pages : 224.0

Expédition : 403

ISBN : 0470042907

Résumé :
A practical guide to building fully operational financial cash flow models for structured finance transactions

Structured finance and securitization deals are becoming more commonplace on Wall Street. Up until now, however, market participants have had to create their own models to analyze these deals, and new entrants have had to learn as they go. Modeling Structured Finance Cash Flows with Microsoft Excel provides readers with the information they need to build a cash flow model for structured finance and securitization deals. Financial professional Keith Allman explains individual functions and formulas, while also explaining the theory behind the spreadsheets. Each chapter begins with a discussion of theory, followed by a section called Model Builder, in which Allman translates the theory into functions and formulas. In addition, the companion website features all of the modeling exercises, as well as a final version of the model that is created in the text.

Note: Companion website and other supplementary materials are not included as part of eBook file.

...

Biographie:
Keith Allman is currently the principal trainer and founder of Enstruct, a structured finance analytics training company. Previously he was a Vice President at Citigroup in their Global Fixed Income, Structured Finance Division. He has built and reviewed hundreds of models from many different sectors. Allman received his master's degree in international affairs from Columbia University....

Sommaire:

Preface xi

Acknowledgments xiii

About the Author xv

Introduction 1

The Three Basic Elements of a Cash Flow Model 3

Inputs 3

Cash Flow Structure 4

Outputs 5

The Process of Building a Cash Flow Model 5

Plan and Design 5

Obtain All Necessary Information 6

Construct Basic Framework 6

Develop Advanced Structure 6

Validate Assumptions 6

Test Model 7

How This Book Is Designed 7

Chapter 1 Dates and Timing 9

Time Progression 9

Dates and Timing on the Inputs Sheet 10

Day-Count Systems: 30/360 versus Actual/360 versus Actual/ 365 11

Model Builder 1.1: Inputs Sheet-Dates and Timing 12

Dates and Timing on the Cash Flow Sheet 14

Model Builder 1.2: Cash Flow Sheet-Dates and Timing 15

Toolbox 18

Naming Cells and Ranges 18

Data Validation Lists 19

EDATE 21

Chapter 2 Asset Cash Flow Generation 23

Loan Level versus Representative Line Amortization 23

How Asset Generation Is Demonstrated in Model Builder 27

Asset Generation on the Inputs Sheet 27

Fixed Rate Amortization Inputs 28

Floating Rate Amortization Inputs 28

Model Builder 2.1: Inputs Sheet Asset Assumptions and the Vectors Sheet 29

Asset Generation on the Cash Flow Sheet 33

Model Builder 2.2: Notional Asset Amortization on the Cash Flow Sheet 33

TOOLBOX 40

OFFSET 40

MATCH 40

MOD 41

PMT 41

Chapter 3 Prepayments 43

How Prepayments Are Tracked 43

SMM: Single Monthly Mortality 44

CPR: Conditional Prepayment Rate 44

PSA: Public Securities Association 44

ABS: Absolute Prepayment Speed 45

Historical Prepayment Data Formats 46

Building Prepayment Curves 46

Prepayment Curves in Project Model Builder 47

The Effect of Prepayments on Structured Transactions 48

Model Builder 3.1: Historical Prepayment Analysis and Creating a Projected Prepayment Curve 48

Model Builder 3.2: Integrating Projected Prepayments in Asset Amortization 53

Toolbox 56

Weighted Averages Using SUMPRODUCT and SUM 56

Chapter 4 Delinquency, Default, and Loss Analysis 59

Delinquencies versus Defaults versus Loss 59

The Importance of Analyzing Delinquency 60

Model Builder 4.1: Building Historical Delinquency Curves 62

Deriving Historical Loss Curves 64

Model Builder 4.2: Building Historical and Projected Loss Curves 67

Analyzing Historical Loss Curves 69

Model Builder 4.2 Continued 69

Projecting Loss Curves 70

Model Builder 4.2 Continued 71

Integrating Loss Projections 73

The Effects of Seasoning and Default Timing 75

Model Builder 4.3: Integrating Defaults in Asset Amortization 76

Chapter 5 Recoveries 83

Model Builder 5.1: Historical Recovery Analysis 85

Projecting Recoveries in a Cash Flow Model 86

Model Builder 5.2: Integrating Recoveries into Project Model Builder 87

Final Points Regarding Recoveries 88

Chapter 6 Liabilities and the Cash Flow Waterfall 89

Priority of Payments and the Cash Flow Waterfall 89

The Movement of Cash for an Individual Liability 90

Types of Liabilities 91

Fees 91

Model Builder 6.1: Calculating Fees in the Waterfall 91

Interest 94

Model Builder 6.2: Calculating Interest in the Waterfall 95

Principal 100

Model Builder 6.3: Calculating Principal in the Waterfall 100

Understanding Basic Asset and Liability Interactions 105

Chapter 7 Advanced Liability Structures: Triggers, Interest ...