Description
Book SynopsisProvides a code intensive, progressive example introductory work to structured finance modeling in VBA Provides a concise, immediate, practical, and pragmatic guide to quickly develop a meaningful level of modeling expertise on a problem in structured finance.
Table of ContentsPreface 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
