Intermediate Structured Finance Modeling: Leveraging Excel, VBA, Access, and Powerpoint, with Website

Preface xv

Goals of the Book xv

What You Should Know xvi

Setting the Context for Learning xviii

The Structure of the Book xix

A Fish Story xxiii

A Perspective on Modeling xxiii

Approaching This Material xxiv

Style xxv

A Parting Remark xxv

On the Web Site xxvii

Acknowledgments xxxv

PART 1 First Steps 1

CHAPTER 1 Introduction 5

Overview 5

Why Was This Book Written? 5

Who Is the Target Audience? 9

What Is the Purpose of the Book? 10

Expanding Your Software Skills 10

Expanding Your Model Design Skills 12

Expanding Your Finance Knowledge 12

Organized to Teach 14

Chapter Organization 15

Accompanying Web Site 17

Learning the “Hard” Way 18

Note 19

CHAPTER 2 The Existing Model 21

Overview 21

Deliverables 22

Under Construction 23

Crisis du Jour 23

Overview of the Current Model 24

Current Model Environment 51

On the Web Site 51

CHAPTER 3 Conventions and Advice 55

Overview 55

Deliverables 56

VBA Conventions 56

Common Sense 70

On the Web Site 72

CHAPTER 4 Segregation of the Existing Model’s Functionality 73

Overview 73

Deliverables 73

Under Construction 73

Deliverables Checklist 74

Breaking Up Is Hard to Do 74

Accommodating Our Design Needs 76

Advantages of Functional Segregation 76

Disadvantages of Functional Segregation 79

On the Web Site 80

CHAPTER 5 Creating the Base Asset Model 81

Overview 81

Deliverables 81

Under Construction 82

The Big Picture: “Just the Assets, Ma’am” 82

Stepping through the Model 84

Testing the Completed Base Asset Model 119

On the Web Site 121

CHAPTER 6 Building the Base Liabilities Model 123

Overview 123

Deliverables 123

Under Construction 124

Liabilities Side of the Model 125

What to Leave In 126

Stepping through the Model 126

Reading the Cash Flows and Assumptions from a File 151

Testing the Completed Base Liabilities Model 162

On the Web Site 164

CHAPTER 7 Establishing the Model Environment 167

Overview 167

Deliverables 168

Under Construction 168

Importance of a Standardized Directory Structure 168

Creating Directories and Defining Their Functions 168

Operating Directories 170

Administrative Directories 175

Creating New Directories for the Model as We Need Them 176

On the Web Site 177

PART 2 Building the New Assets Model 179

CHAPTER 8 Designing the New Collateral Cash Flow Generator 181

Overview 181

Deliverables 183

Under Construction 183

Improving the CCFG Menus: Conversion to UserForms 183

Improving the CCFG Data-Handling Capabilities 199

Improving the CCFG Collateral Selection Process 209

Improving the CCFG Cash Flow Generation Process 212

Improving the CCFG Report Generation Process 215

Improving the CCFG Messaging Process 220

On the Web Site 220

CHAPTER 9 Writing the CCFG Menus and Data Sheets 221

Overview 221

Deliverables 221

Under Construction 222

Menus and UserForms 222

Menus of the CCFG 241

Main Menu 242

Run Options Menu 243

Collateral Pool Menu 255

Collateral Geographic Selection Criteria Menu 259

Financial Selection Criteria Menu 288

Cash Flow Amortization Parameters Menu 299

Collateral Reports Menu 303

On the Web Site 316

CHAPTER 10 Writing the Collateral Data Handling Code 317

Overview 317

Deliverables 318

Under Construction 318

Managing Multiple Portfolio Files 320

Initial Data Screening 335

Writing the Screening Process VBA Code 343

Writing the Initial Data Screening Reports 351

Writing the Demographic Methodology Risk Reports 360

Building a Representative Line Generator Program 365

Building the Rep Line Generator Program 369

On the Web Site 387

CHAPTER 11 Writing the Collateral Selection Code 389

Overview 389

Deliverables 390

Under Construction 390

Building the Code 391

Financial and Demographic Selection Code 392

Financial Collateral Selection Process Reporting 398

Geographic Selection Code 416

Geographic Concentration Code 419

Introduction to Geographic Reporting 433

On the Web Site 458

CHAPTER 12 Writing the Collateral Cash Flow Amortization Code 459

Overview 459

Deliverables 459

Under Construction 460

Quick Review of Existing CF Generation Code 461

Adding New Mortgage Types 462

New Prepayment and Default Methodologies 473

Uniform Methodology 474

Geographic Methodology 478

Demographic Methodology 493

Reporting the Results of the Cash Flow Calculations 503

Writing the Assumptions Report Package 511

On the Web Site 518

CHAPTER 13 Writing the CCFG Reporting Capability 519

Overview 519

Deliverables 520

Under Construction 520

Recapping the CCFG Report Package 521

CCFG Activities and Reports 522

Eligible Collateral Assessment Process 524

Geographic/Demographic Reports 555

Presentation Reports 560

PART 3 Building the New Liabilities Model 583

CHAPTER 14 Designing the Liabilities Waterfall Model 585

Overview 585

Deliverables 585

Under Construction 585

Structure of the Deal: Sources and Uses of Funds 586

Design Elements of the LWM 589

Main Menu 590

Report Package Menu UserForm 590

Structure Inputs Menu 591

Liabilities Waterfall Worksheet 592

Performance Summary Page 600

VBA Code Modules 600

Report Template Files 604

On the Web Site 616

CHAPTER 15 Writing the Liabilities Waterfall Model Spreadsheet 617

Overview 617

Deliverables 617

Under Construction 618

Deal Structure Inputs 618

Building the Liabilities Waterfall Model 623

Period Factors Worksheet 650

Total Cash Flows Section 651

Results Page Worksheet 651

On the Web Site 657

CHAPTER 16 Writing the LWM VBA Code 659

Overview 659

Deliverables 660

Under Construction 660

VBA Requirements of the LWM 665

Menus of the LWM 666

Main Program 679

Error Checking the Main Menu Inputs 681

Importing the Liability Structure Inputs 682

Importing the Cash Flows of the CCFG 686

Loading and Running the LWM 693

Reporting the Results 696

Other Functionality 718

PART 4 Access, PowerPoint, and Outlook 721

CHAPTER 17 Access: An Introduction 723

Overview 723

Deliverables 724

Under Construction 724

Basics of Access 724

Opening Access and Creating a Database 726

Access Tables 728

Getting the Data In: Entering Data 731

Getting the Data Back Out: Queries 740

Basics of Structured Query Language 754

Concluding Remarks 762

On the Web Site 762

CHAPTER 18 Implementing Access in the CCFG and LWM 763

Overview 763

Deliverables 764

Under Construction 764

Access and the Collateral Cash Flow Generator 766

Collateral Portfolio Data 767

Geographic Selection Criteria 781

Geographic Concentration Criteria 794

Initial Data Screening Criteria 796

Custom Financial Selection Criteria 805

Run Options Elections 815

Generating the Loan Portfolio 818

Monthly Cash Flows Scenarios 822

Access in CCFG Reporting 825

Access and the Liabilities Waterfall Model 827

Connecting the LWM to the CCFG 827

Structure Worksheet Objects 828

Retrieving the Monthly Cash Flow Scenarios 831

Storing LWM Run Results in the Database 832

Concluding Remarks 843

On the Web Site 843

CHAPTER 19 Implementing PowerPoint and Outlook in the CCFG 845

Overview 845

Deliverables 845

Under Construction 846

Using PowerPoint in the CCFG 846

Data Requirements 851

Adding a New Class of Reports 856

Introduction to PowerPoint in VBA 860

Preparing the Data for Use 867

First Time Homeowners Slide 869

Creating the Null Report 874

MS Outlook 876

On the Web Site 882

PART 5 Running the CCFG and the LWM 885

CHAPTER 20 Running the Models 887

Overview 887

Deliverables 887

Under Construction 888

Running the CCFG, Analyzing the Asset Side of the Deal 888

Liabilities Side of the Deal 923

Preparing a PowerPoint Presentation about Geographic Concentration 933

On the Web Site 935

Afterword 941

Exhibits Index 943

Subject Index 975