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

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.

Table of Contents:
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