Fast Track to Structured Finance Modeling, Monitoring, and Valuation

William Preinitz is a Managing Director with Citigroup Global Markets' Global Special Situations Group. He oversees new business origination-structured finance issuance, serv-icing operations, rating agency relationships, credit wrap providers, investors, analytical development, internal risk control, audit activities, hedging, and residual arbitrage activities.

• Preface xviiAbout the Web Site xxiPART ONE Introduction 1CHAPTER 1 Why? What? Who? Where? and How? 3The Immortal Question(s) 3What Are the Advantages of Learning VBA? 6What Are the Disadvantages of Learning VBA? 8What Is a Model? 10Why Is Modeling a Valuable Skill? 12What Are the Stages of Model Design and Construction? 12Other Aspects of Modeling 20Perspective of This Book 22Structure of the Book 23Putting the Deliverables “On the Target” 24CHAPTER 2 Common Sense 27Overview 27Deliverables 27Do Not Eat Anything Bigger Than Your Head 27You Only Hurt the One You Love 28It is OK to Be Right; Just Do Not Be Dead-Right 29Know When to Hold ‘Em 30Next Steps 30PART TWO The Securitization Process 31CHAPTER 3 Securitizing a Loan Portfolio 33Overview 33Deliverables 34Financing a Loan Portfolio 34Description of the Collateral 37Collateral Cash Flows 39Expense and Liability Structure 43Measuring the Performance of the Structure 45Functional Requirements of the Model 46The Role of the Model in the Process of Securitization 47Deliverables Checklist 49Next Steps 49On the Web Site 49PART THREE Designing the Model 51CHAPTER 4 Understanding the Excel Waterfall 53Overview 53Deliverables 54Under Construction 54Waterfalls 54Structure of the Cash Flow Waterfall 55Collateral Cash Flows Section 56Deal Expenses Section 58Conduit Interest Section 59Conduit Principal Section 60Excess Cash Treatment Section 60Conduit Summary Section 61Delinquency Reserve Section 63Deal Triggers Section 65Debt Costs Section 66Default Tests Section 66Debt Performance Calculations Support Section 67Deal Wind-down Trigger 68Cash Flow Waterfall “Box Score” Section 70Deliverables Checklist 72Next Steps 72On the Web Site 72CHAPTER 5 Designing the VBA Model 73Overview 73Deliverables 74Under Construction 75What Are the Desired Results? 75What Processes Must the Model Perform? 76Introduction to Template Files 77Collateral Selection Reports 81Ineligible Collateral Reports 82Eligible Collateral Reports 85Cash Flow Waterfall Reports 89Cash Flow Matrix Reports 89Designing the Menus 89Designing the Collateral Selection Output Screen 94It Should Look Like This 94Deliverables Checklist 94Next Steps 95On the Web Site 95PART FOUR Learning the VBA Language 97CHAPTER 6 Laying the Model Groundwork 99Overview 99Deliverables 101Under Construction 101Creating the External Model Environment 102Creating the Internal Model Environment 105Writing the Main Program in Pseudo Code 112Deliverables Checklist 114Next Steps 114On the Web Site 114CHAPTER 7 Recorded Macros: A First Look at the VBA Language 117Overview 117Deliverables 118Under Construction 119Recording VBA Code 119Running the Edited Code 128Using Recorded Macros to Build a Simple Model 134Deliverables Checklist 135Next Steps 136On the Web Site 136CHAPTER 8 Writing Menus: An Introduction to Data, Ranges, Arrays, and Objects 139Overview 139Deliverables 139Under Construction 141Role of the Menu 142Structural Elements of a Menu 145Introduction to VBA Variables 147Variable Types 147How to Create and Use Variables 148Scope of Variables 152Assigning Values to Variables 156Constants 157Option Explicit Statement 161Performing Calculations with Variables 161Ranges 163Objects 171Building a Menu from Scratch 173Deliverables Checklist 179Next Steps 180On the Web Site 180CHAPTER 9 Controlling the Flow of the Model 183Overview 183Deliverables 183Under Construction 185Decision Structures 185Looping Structures 192Subroutines 199Functions 203Deliverables Checklist 205Next Steps 206On the Web Site 206CHAPTER 10 Building Messaging Capabilities 207Overview 207Deliverables 207Under Construction 208Progress Messages 208Error Messages 211Run-time Option Error Checking 223Deliverables Checklist 224Next Steps 225On the Web Site 225CHAPTER 11 Designing the Model’s Reports 227Overview 227Deliverables 227Under Construction 227Collateral Reporting Activity 228Ineligible Collateral Reporting 229Eligible Collateral Reporting 235Cash Flow Waterfall Report 236Deal Summary Matrix Reports 237Matrix Report Package 239Assumptions Report 239Deliverables Checklist 240Next Steps 241On the Web Site 241PART FIVE Writing the Model 243CHAPTER 12 Main Program and Menus 245Overview 245Deliverables 246Under Construction 247Main Program 249Typical Subroutine Called by the Main Program 252Three Subroutine Calls from the Main Program 252Building Menu Error Checking 253Reading the Contents of All the Menus 257Reading the Contents of the Collateral File 264Deliverables Checklist 266Next Steps 268On the Web Site 268CHAPTER 13 Writing the Collateral Selection Code 271Overview 271Deliverables 271Under Construction 272The Collateral Reporting Package 273Ineligible Collateral Report Package 278Producing the Ineligibility Report Package 289Helping Make It All Happen 304Eligible Collateral Report Package 305Writing Out the Assumptions of the Model Run 316Deliverables Checklist 318Next Steps 318On the Web Site 318CHAPTER 14 Calculating the Cash Flows 321Overview 321Deliverables 321Under Construction 323Pause Awhile and Reflect 323Overview of the Cash Flow Generator 333Writing the VBA Code 336Setting Up the Scenario Loops 355Cash Flow Calculation Sequence 356Building a Cash Flow Trace Capability 361Concluding Remarks 362Deliverables Checklist 362Next Steps 363On the Web Site 363CHAPTER 15 Running the Waterfall: Producing Initial Results 365Overview 365Deliverables 366Under Construction 367Running the Waterfall Spreadsheet 370Loading the Collateral Cashflows 370Capturing the Waterfall Results 373Reporting the Summary Report or “Box Score” Information 374Loading the Matrix Reports Information 375Producing the Waterfall Spreadsheet Report Package 376Producing the Matrix Report Package 382Deliverables Checklist 386Next Steps 387On the Web Site 387CHAPTER 16 Debugging the Model 389Overview 389Deliverables 390Under Construction 390Compiling the Model 390Types of VBA Errors 391Common Syntax Errors 393Common Compile Errors 393How to Run the VBA Debugger 395Runtime Errors 404Logical Errors 406Deliverables Checklist 412Next Steps 412On the Web Site 413PART SIX Testing, Use, and Deployment 415CHAPTER 17 Validating the Model 417Overview 417Deliverables 417Under Construction 418Steps in the Validation Procedure 420Validating the Menu Error Checking Code 421Validating the Menu Inputs 422Validating the Code for Reading the Portfolio Data File 423Validating the Collateral Selection Code 423Validating the Eligible Collateral Reporting Code 427Validating the Floating Rate Loan Reset Period Levels 428Validating the Cash Flow Calculation Code 439Validating the Cash Flow Waterfall Spreadsheet 449Future Back Testing Activity 452Validation Activities by Third Parties 453Deliverables Checklist 453Next Steps 453On the Web Site 453CHAPTER 18 Running the Model 455Overview 455Deliverables 456Under Construction 456Installing a “Run Button” 457Steps of the Structuring Process 458Organizing the Output 459Step 1: Determine Composition of Proposed Collateral Portfolio 460Step 2: Initial Collateral Selection 462Step 3: Addressing Portfolio Concentration Issues 464Step 4: Correcting Concentration Issues 467Step 5: Running the Expected Case Cash Flows 468Step 6: Expected Case Sensitivity Analysis 472Time Out! Building a Batch Processing Capability 474Sensitivity Analysis Results 477Help! A Post-Processing Program 487Step 7: Rating Agency Stress Tests 488Step 8: Adjusting the Seller Interest 490Sizing Results Table 492We Are Done! 493Deliverables Checklist 493Next Steps 493On the Web Site 494Extractor.xls Program 495Batch Program Inputs Menu Entries 499CHAPTER 19 Building Additional Capabilities 503Overview 503Under Construction 503Deliverables 504Peek-A-Boo! Immediate Access to Model Results 504Single Scenario Report File 506Ability to Run Interest Rate Sensitivities 513Adding Information to the Assumptions Page 529Next Steps 534On the Web Site 534PART SEVEN After the Model Is Written 537CHAPTER 20 Documentation of the Model 539Overview 539Deliverables 540Under Construction 540Online Documentation 540External Documentation 545User Training Documentation 546Deliverables Checklist 546Next Steps 547On the Web Site 547CHAPTER 21 Managing the Growth of the Model 549Overview 549Deliverables 549Under Construction 550Maintaining the Model Environment 550Streamlining the Validation and Promotion Process 554Evolution of the Form of the Model 557Evolution of the Structure of the Model 558Deliverables Checklist 558Next Steps 559On the Web Site 559PART EIGHT Risk Assessment and Valuation 561CHAPTER 22 Building Portfolio Monitoring Model 563Overview 563Deliverables 563Under Construction 564How Much of the Structuring Model Can We Use? 566Why Two Models? 567Designing the New Report Package 568You Can’t Make an Omelet Without Breaking Some Eggs 576Modifying Some of the Code 594What a Brave New Model That Has Such Features in It! 606Validating the Model 613Deliverables Checklist 614Next Steps 614On the Web Site 614CHAPTER 23 Valuation Techniques: How Do We Determine Price? 615Overview 615Deliverables 616Under Construction 616Introduction to Pricing a Security 617Assessing Risk 622Adding the Pricing Functionality to the Monitoring Model 627Deliverables Checklist 636Next Steps 637On the Web Site 637CHAPTER 24 Challenging Times for the Deal 639Overview 639Deliverables 639Under Construction 639Using the Monitoring Model to Assess the Performance and Value of the Deal 639Three Months from Issuance 643Six Months from Issuance 644Nine Months from Issuance 646Twelve Months from Issuance 648Fifteen Months from Issuance 654Eighteen Months from Issuance 655Twenty-One Months from Issuance 659Twenty-Four Months from Issuance 660End of the Exercise 662On the Web Site 664PART NINE Farewell 665CHAPTER 25 Parting Admonitions 667What We Have Learned 667Next Steps 669APPENDIX A Mortgage Math 671Overview 671Loan Amortization Terms 672Components of the Cash Flows of a Loan 674Next Steps 688APPENDIX B Bond Math 689Overview 689Future Value 690Present Value 693Tenor Measurements 695Yield and Return Measurements 697Loss and Coverage Measurements 701Next Steps 705Exhibits Index 707Subject Index 727