A Fast Track to Structured Finance Modeling, Monitoring, and Valuation: Jump Start VBA

This book is designed to start with simple examples that progressively develop the reader's confidence to take on more complex tasks. There is very little theoretical discussion about computer science, operations research algorithms, mathematics, or finance. The thrust of the book is to teach the reader to break complex tasks down into simple tasks. It then looks to implement those simple tasks into VBA code using a critical subset of the features of the language. The tentative contents is: (1) Why? What? Who? Where? and How? (2) Common Sense (3) Securitizing A Loan Portfolio (4) Understanding the Excel Waterfall (5) Designing the VBA Model (6) Laying the Model Groundwork (7) Recorded Macros: A First Look at the VBA Language (8) Writing Menus: An Introduction to Data, Ranges, Arrays, and Objects (9) Controlling the Flow of the Model (10) Building Messaging Capabilities (11) Designing the Model’s Reports (12) Main Program and Menus (13) Writing the Collateral Selection Code (14) Calculating the Cash Flows (15) Running the Waterfall: Producing Initial Results (16) Debugging the Model (17) Validating the Model (18) Running the Model (19) Building Additional Capabilities (20) Documentation of the Model (21) Managing the Growth of the Model (22) Building Portfolio Monitoring Model (23) Valuation Techniques: How do we Determine Price? (24) Challenging Times For the Deal (25) Parting Admonitions

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