Rights Contact Login For More Details
More About This Title Intermediate Structured Finance Modeling + Website:Leveraging Excel, VBA, Access, and PowerPoint
English
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.
English
WILLIAM PREINITZ has thirty years' experience in investment banking and structured finance analytics. A former managing director at Citigroup, highlights of his career include the creation and marketing of securitizations backed by 12b-1 mutual fund fees. With a team of ten people over a fifteen-year period, his group concurrently provided analytical structuring support for the over 100 different asset classes contained in the ABCP program of the bank. These included such varied items as movie revenues, fast-food franchise financing, and communication satellite leasing. In conjunction with the bank's corporate training program, he taught Excel/VBA modeling classes to over 300 people ranging from associates to managing directors.
MATTHEW NIEDERMAIER is currently a vice president in the Structured Finance Group at Markit, where he is a member of a pricing and valuation group focused on agency mortgages and CMOs. Within Markit he has also taught courses in Excel/VBA globally. Prior to Markit he modeled U.S. and Latin American ABS deals at Citigroup, including auto loans, credit cards,and timeshares.
English
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
