A Fast Track to Structured Finance Modeling, Monitoring, and Valuation: Jump Start VBA

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

Extractor.xls Program 495

Batch Program Inputs Menu Entries 499

CHAPTER 19 Building Additional Capabilities 503

Overview 503

Under Construction 503

Deliverables 504

Peek-A-Boo! Immediate Access to Model Results 504

Single Scenario Report File 506

Ability to Run Interest Rate Sensitivities 513

Adding Information to the Assumptions Page 529

Next Steps 534

On the Web Site 534

PART SEVEN After the Model Is Written 537

CHAPTER 20 Documentation of the Model 539

Overview 539

Deliverables 540

Under Construction 540

Online Documentation 540

External Documentation 545

User Training Documentation 546

Deliverables Checklist 546

Next Steps 547

On the Web Site 547

CHAPTER 21 Managing the Growth of the Model 549

Overview 549

Deliverables 549

Under Construction 550

Maintaining the Model Environment 550

Streamlining the Validation and Promotion Process 554

Evolution of the Form of the Model 557

Evolution of the Structure of the Model 558

Deliverables Checklist 558

Next Steps 559

On the Web Site 559

PART EIGHT Risk Assessment and Valuation 561

CHAPTER 22 Building Portfolio Monitoring Model 563

Overview 563

Deliverables 563

Under Construction 564

How Much of the Structuring Model Can We Use? 566

Why Two Models? 567

Designing the New Report Package 568

You Can’t Make an Omelet Without Breaking Some Eggs 576

Modifying Some of the Code 594

What a Brave New Model That Has Such Features in It! 606

Validating the Model 613

Deliverables Checklist 614

Next Steps 614

On the Web Site 614

CHAPTER 23 Valuation Techniques: How Do We Determine Price? 615

Overview 615

Deliverables 616

Under Construction 616

Introduction to Pricing a Security 617

Assessing Risk 622

Adding the Pricing Functionality to the Monitoring Model 627

Deliverables Checklist 636

Next Steps 637

On the Web Site 637

CHAPTER 24 Challenging Times for the Deal 639

Overview 639

Deliverables 639

Under Construction 639

Using the Monitoring Model to Assess the Performance and Value of the Deal 639

Three Months from Issuance 643

Six Months from Issuance 644

Nine Months from Issuance 646

Twelve Months from Issuance 648

Fifteen Months from Issuance 654

Eighteen Months from Issuance 655

Twenty-One Months from Issuance 659

Twenty-Four Months from Issuance 660

End of the Exercise 662

On the Web Site 664

PART NINE Farewell 665

CHAPTER 25 Parting Admonitions 667

What We Have Learned 667

Next Steps 669

APPENDIX A Mortgage Math 671

Overview 671

Loan Amortization Terms 672

Components of the Cash Flows of a Loan 674

Next Steps 688

APPENDIX B Bond Math 689

Overview 689

Future Value 690

Present Value 693

Tenor Measurements 695

Yield and Return Measurements 697

Loss and Coverage Measurements 701

Next Steps 705

Exhibits Index 707

Subject Index 727