Preface xvii

About the Web Site xxi

PART ONE Introduction 1

CHAPTER 1 Why? What? Who? Where? and How? 3

The Immortal Question(s) 3

What Are the Advantages of Learning VBA? 6

What Are the Disadvantages of Learning VBA? 8

What Is a Model? 10

Why Is Modeling a Valuable Skill? 12

What Are the Stages of Model Design and Construction? 12

Other Aspects of Modeling 20

Perspective of This Book 22

Structure of the Book 23

Putting the Deliverables "On the Target" 24

CHAPTER 2 Common Sense 27

Overview 27

Deliverables 27

Do Not Eat Anything Bigger Than Your Head 27

You Only Hurt the One You Love 28

It is OK to Be Right; Just Do Not Be Dead-Right 29

Know When to Hold 'Em 30

Next Steps 30

PART TWO The Securitization Process 31

CHAPTER 3 Securitizing a Loan Portfolio 33

Overview 33

Deliverables 34

Financing a Loan Portfolio 34

Description of the Collateral 37

Collateral Cash Flows 39

Expense and Liability Structure 43

Measuring the Performance of the Structure 45

Functional Requirements of the Model 46

The Role of the Model in the Process of Securitization 47

Deliverables Checklist 49

Next Steps 49

On the Web Site 49

PART THREE Designing the Model 51

CHAPTER 4 Understanding the Excel Waterfall 53

Overview 53

Deliverables 54

Under Construction 54

Waterfalls 54

Structure of the Cash Flow Waterfall 55

Collateral Cash Flows Section 56

Deal Expenses Section 58

Conduit Interest Section 59

Conduit Principal Section 60

Excess Cash Treatment Section 60

Conduit Summary Section 61

Delinquency Reserve Section 63

Deal Triggers Section 65

Debt Costs Section 66

Default Tests Section 66

Debt Performance Calculations Support Section 67

Deal Wind-down Trigger 68

Cash Flow Waterfall "Box Score" Section 70

Deliverables Checklist 72

Next Steps 72

On the Web Site 72

CHAPTER 5 Designing the VBA Model 73

Overview 73

Deliverables 74

Under Construction 75

What Are the Desired Results? 75

What Processes Must the Model Perform? 76

Introduction to Template Files 77

Collateral Selection Reports 81

Ineligible Collateral Reports 82

Eligible Collateral Reports 85

Cash Flow Waterfall Reports 89

Cash Flow Matrix Reports 89

Designing the Menus 89

Designing the Collateral Selection Output Screen 94

It Should Look Like This 94

Deliverables Checklist 94

Next Steps 95

On the Web Site 95

PART FOUR Learning the VBA Language 97

CHAPTER 6 Laying the Model Groundwork 99

Overview 99

Deliverables 101

Under Construction 101

Creating the External Model Environment 102

Creating the Internal Model Environment 105

Writing the Main Program in Pseudo Code 112

Deliverables Checklist 114

Next Steps 114

On the Web Site 114

CHAPTER 7 Recorded Macros: A First Look at the VBA Language 117

Overview 117

Deliverables 118

Under Construction 119

Recording VBA Code 119

Running the Edited Code 128

Using Recorded Macros to Build a Simple Model 134

Deliverables Checklist 135

Next Steps 136

On the Web Site 136

CHAPTER 8 Writing Menus: An Introduction to Data, Ranges, Arrays, and Objects 139

Overview 139

Deliverables 139

Under Construction 141

Role of the Menu 142

Structural Elements of a Menu 145

Introduction to VBA Variables 147

Variable Types 147

How to Create and Use Variables 148

Scope of Variables 152

Assigning Values to Variables 156

Constants 157

Option Explicit Statement 161

Performing Calculations with Variables 161

Ranges 163

Objects 171

Building a Menu from Scratch 173

Deliverables Checklist 179

Next Steps 180

On the Web Site 180

CHAPTER 9 Controlling the Flow of the Model 183

Overview 183

Deliverables 183

Under Construction 185

Decision Structures 185

Looping Structures 192

Subroutines 199

Functions 203

Deliverables Checklist 205

Next Steps 206

On the Web Site 206

CHAPTER 10 Building Messaging Capabilities 207

Overview 207

Deliverables 207

Under Construction 208

Progress Messages 208

Error Messages 211

Run-time Option Error Checking 223

Deliverables Checklist 224

Next Steps 225

On the Web Site 225

CHAPTER 11 Designing the Model's Reports 227

Overview 227

Deliverables 227

Under Construction 227

Collateral Reporting Activity 228

Ineligible Collateral Reporting 229

Eligible Collateral Reporting 235

Cash Flow Waterfall Report 236

Deal Summary Matrix Reports 237

Matrix Report Package 239

Assumptions Report 239

Deliverables Checklist 240

Next Steps 241

On the Web Site 241

PART FIVE Writing the Model 243

CHAPTER 12 Main Program and Menus 245

Overview 245

Deliverables 246

Under Construction 247

Main Program 249

Typical Subroutine Called by the Main Program 252

Three Subroutine Calls from the Main Program 252

Building Menu Error Checking 253

Reading the Contents of All the Menus 257

Reading the Contents of the Collateral File 264

Deliverables Checklist 266

Next Steps 268

On the Web Site 268

CHAPTER 13 Writing the Collateral Selection Code 271

Overview 271

Deliverables 271

Under Construction 272

The Collateral Reporting Package 273

Ineligible Collateral Report Package 278

Producing the Ineligibility Report Package 289

Helping Make It All Happen 304

Eligible Collateral Report Package 305

Writing Out the Assumptions of the Model Run 316

Deliverables Checklist 318

Next Steps 318

On the Web Site 318

CHAPTER 14 Calculating the Cash Flows 321

Overview 321

Deliverables 321

Under Construction 323

Pause Awhile and Reflect 323

Overview of the Cash Flow Generator 333

Writing the VBA Code 336

Setting Up the Scenario Loops 355

Cash Flow Calculation Sequence 356

Building a Cash Flow Trace Capability 361

Concluding Remarks 362

Deliverables Checklist 362

Next Steps 363

On the Web Site 363

CHAPTER 15 Running the Waterfall: Producing Initial Results 365

Overview 365

Deliverables 366

Under Construction 367

Running the Waterfall Spreadsheet 370

Loading the Collateral Cashflows 370

Capturing the Waterfall Results 373

Reporting the Summary Report or "Box Score" Information 374

Loading the Matrix Reports Information 375

Producing the Waterfall Spreadsheet Report Package 376

Producing the Matrix Report Package 382

Deliverables Checklist 386

Next Steps 387

On the Web Site 387

CHAPTER 16 Debugging the Model 389

Overview 389

Deliverables 390

Under Construction 390

Compiling the Model 390

Types of VBA Errors 391

Common Syntax Errors 393

Common Compile Errors 393

How to Run the VBA Debugger 395

Runtime Errors 404

Logical Errors 406

Deliverables Checklist 412

Next Steps 412

On the Web Site 413

PART SIX Testing, Use, and Deployment 415

CHAPTER 17 Validating the Model 417

Overview 417

Deliverables 417

Under Construction 418

Steps in the Validation Procedure 420

Validating the Menu Error Checking Code 421

Validating the Menu Inputs 422

Validating the Code for Reading the Portfolio Data File 423

Validating the Collateral Selection Code 423

Validating the Eligible Collateral Reporting Code 427

Validating the Floating Rate Loan Reset Period Levels 428

Validating the Cash Flow Calculation Code 439

Validating the Cash Flow Waterfall Spreadsheet 449

Future Back Testing Activity 452

Validation Activities by Third Parties 453

Deliverables Checklist 453

Next Steps 453

On the Web Site 453

CHAPTER 18 Running the Model 455

Overview 455

Deliverables 456

Under Construction 456

Installing a "Run Button" 457

Steps of the Structuring Process 458

Organizing the Output 459

Step 1: Determine Composition of Proposed Collateral Portfolio 460

Step 2: Initial Collateral Selection 462

Step 3: Addressing Portfolio Concentration Issues 464

Step 4: Correcting Concentration Issues 467

Step 5: Running the Expected Case Cash Flows 468

Step 6: Expected Case Sensitivity Analysis 472

Time Out! Building a Batch Processing Capability 474

Sensitivity Analysis Results 477

Help! A Post-Processing Program 487

Step 7: Rating Agency Stress Tests 488

Step 8: Adjusting the Seller Interest 490

Sizing Results Table 492

We Are Done! 493

Deliverables Checklist 493

Next Steps 493

On the Web Site 494

[...] Program 495

Batch Program Inputs Menu Entries 499

CHAPTER 19 Building Additional Capabilities 503

Overview 503

Under Construction 503