Modeling Structured Finance Cash Flows with Microsoft Excel: A Step-by-Step Guide

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 Rate Swaps, and Reserve Accounts 107

Triggers and Their Affect on the Liability Structure 107

Model Builder 7.1: Incorporating Triggers 108

Swaps 113

Model Builder 7.2: Incorporating a Basic Interest Rate Swap 114

Final Notes on Swaps 117

Reserve Accounts 117

Model Builder 7.3: Incorporating a Cash-Funded Reserve Account 118

Conclusion of the Cash Flow Waterfall 122

Toolbox 123

AND and OR 123

CHAPTER 8 Analytics and Output Reporting 125

Internal Testing 125

Cash In versus Cash Out 125

Model Builder 8.1: Cash In versus Cash Out Test 126

Balances at Maturity 128

Model Builder 8.2: Balances at Maturity Tests 128

Asset Principal Check 129

Model Builder 8.3: Asset Principal Check Test 129

Performance Analytics 130

Monthly Yield 130

Model Builder 8.4: Calculating Monthly Yield 130

Calculating the Monthly Yield 132

Bond-Equivalent Yield 133

Model Builder 8.5: Calculating Bond-Equivalent Yield 133

Modified Duration 133

Model Builder 8.6: Calculating Modified Duration 134

Output Reporting 135

Model Builder 8.7: Creating the Output Report 136

The Importance of Testing and Output 140

Toolbox 140

Conditional Formatting 140

Goal Seek 141

Array Formulas 142

CHAPTER 9 Understanding the Model 145

The Complete Model in Review 145

Understanding the Effects of Increased Loss 147

Varying Principal Allocation Methodologies 150

Varying Prepayment Rates 151

Varying Loss Timing 152

Varying Recovery Rate and Lag 152

The Value of a Swap 153

Additional Testing 153

CHAPTER 10 Automation Using Visual Basic Applications (VBA) 155

Conventions of This Chapter 155

The Visual Basic Editor 156

The Menu Bar 156

The Project Explorer and the Properties Window 157

VBA Code 157

Simple Automation for Printing and Goal Seek 158

Model Builder 10.1: Automating Print Procedures 158

Model Builder 10.2: Automating Goal Seek to Optimize Advance Rates 161

Understanding Looping to Automate the Analytics Sheet 164

Model Builder 10.3: Automating Goal Seek to Perform Transaction Analytics 164

Automated Scenario Generation 167

Model Builder 10.4: Creating a Transaction Scenario Generator 167

Working with Macros in Excel 173

CHAPTER 11 Conclusion 175

The Investment Banker's Perspective 175

The Investor's Perspective 176

The Issuer's Perspective 176

The Financial Guarantor's Perspective 177

The Big Picture Perspective 177

Appendix: Using This Book with Excel 2007 179

Index 193