A Fast Track To Structured Finance Modeling, Monitoring, and Valuation – Jump Start VBA

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