Skip to main content

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



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

William Preinitz, Matthew Niedermaier

ISBN: 978-0-470-92878-3 December 2010 1032 Pages


This book is written for financial analysts who have a working knowledge of Excel and VBA and who wish to enhance their marketability by improving their modeling expertise.  The goal of the book is to broaden the reader’s VBA skills and at the same time to progressively integrate Access, PowerPoint, and Outlook into an existing model.  Significant attention is also directed to the design and execution of an Excel/VBA interface employing detailed UserForms.  In addition, the construction of various reports, first in Excel, then Access, and finally PowerPoint is undertaken.  Regardless of your area of financial expertise, this book serves as an essential guide to mastering leveraging the effects of Microsoft products in whatever applications you choose to build.

The focus of the book is a case study containing a structured finance model.  The case study model was originally designed for the securitization of a portfolio of commercial small business loans.  This model now needs to be expanded to securitize a much larger portfolio of residential mortgages. 

The Original Structuring model will be bifurcated into a Sources of Funds model, the assets of the collateral pool, and a Uses of Funds model, the liabilities waterfall model.  This will give the reader experience working with an existing model as a base departure point of development rather than the less common “start from scratch” approach.  Once the Base Asset Model (BAM) and the Base Liabilities Models (BLM) are complete, each model will serve as the platform for future development.  From the BAM and BLM a pair of much more robust models, the Collateral Cash Flow Generator (CCFG) and the Liabilities Waterfall Model (LWM), respectively, are created.  The CCFG is designed to process a significantly greater quantity, variety, and complexity of collateral types.  It also introduces the analyst to Geographic based prepayment methods as well as those using Financial/Demographic factor approaches. The LWM, operating independently of the CCFG, reads the collateral cash flow scenarios and applies them across a two tiered, six tranche bond structure.  The liabilities waterfall contains provision for an interest rate swap, performance triggers and a variety of other credit enhancement features.

With the CCFG and LWM now complete Access is introduced.  Access will replace various Excel/VBA code modules in the CCFG such as the collateral data management, data scrubbing and reporting, data sufficiency testing, and eligibility screening.  Access is also employed to export and the various collateral cash flows.   In the LWM Access is used to import the cash flows, store the liability structure specifications, and export/save the resultant structure performance.  PowerPoint is then introduced in the CCFG to allow for the fully automated production of presentation report packages.  Last Outlook is added to the models to inform the analyst of model performance and to automatically distribute the various report packages to pre-designated mailing lists.

The book contains over 750 exhibits of reports, menus, calculation, and algorithmic examples and most of all code.  The complete Excel, VBA, Access, PowerPoint, and Outlook code for all of the models is included on the Web site.  The Web site also includes Web chapters containing detailed instructions on how to create and populate a directory environment to store this code, run the models, and organize the results.  Lastly there are other Web chapters containing supplemental information explaining the structure and function of the original model, bond and mortgage math (with calculation examples), representative line generator programs, and a tutorial on the construction and integration of UserForms.

This book is the intermediate level companion volume to A Fast-Track to Structured Finance Modeling, Monitoring, and Valuation: Jump Start VBA; Preinitz; Wiley 2009, an introductory work on the subject.

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