A Fast Track To Structured Finance Modeling, Monitoring and Valuation: Jump Start VBA (Wiley Finance)

A FAST-TRACK TO STRUCTURED FINANCE: Modeling Monitering, and Valuation......Page 1
Contents......Page 9
Preface......Page 19
About the Web Site......Page 23
Part I: Introduction......Page 25
THE IMMORTAL QUESTION(S)......Page 27
WHAT ARE THE ADVANTAGES OF LEARNING VBA?......Page 30
WHAT ARE THE DISADVANTAGES OF LEARNING VBA?......Page 32
WHAT IS A MODEL?......Page 34
WHAT ARE THE STAGES OF MODEL DESIGN AND CONSTRUCTION?......Page 36
OTHER ASPECTS OF MODELING......Page 44
PERSPECTIVE OF THIS BOOK......Page 46
STRUCTURE OF THE BOOK......Page 47
PUTTING THE DELIVERABLES “ON THE TARGET”......Page 48
DO NOT EAT ANYTHING BIGGER THAN YOUR HEAD......Page 51
YOU ONLY HURT THE ONE YOU LOVE......Page 52
IT IS OK TO BE RIGHT; JUST DO NOT BE DEAD-RIGHT......Page 53
NEXT STEPS......Page 54
Part II: The Securitization Process......Page 55
OVERVIEW......Page 57
FINANCING A LOAN PORTFOLIO......Page 58
DESCRIPTION OF THE COLLATERAL......Page 61
COLLATERAL CASH FLOWS......Page 63
EXPENSE AND LIABILITY STRUCTURE......Page 67
MEASURING THE PERFORMANCE OF THE STRUCTURE......Page 69
FUNCTIONAL REQUIREMENTS OF THE MODEL......Page 70
THE ROLE OF THE MODEL IN THE PROCESS OF SECURITIZATION......Page 71
ON THE WEB SITE......Page 73
Part III: Designing the Model......Page 75
OVERVIEW......Page 77
WATERFALLS......Page 78
STRUCTURE OF THE CASH FLOW WATERFALL......Page 79
COLLATERAL CASH FLOWS SECTION......Page 80
DEAL EXPENSES SECTION......Page 82
CONDUIT INTEREST SECTION......Page 83
EXCESS CASH TREATMENT SECTION......Page 84
CONDUIT SUMMARY SECTION......Page 85
DELINQUENCY RESERVE SECTION......Page 87
DEAL TRIGGERS SECTION......Page 89
DEFAULT TESTS SECTION......Page 90
DEBT PERFORMANCE CALCULATIONS SUPPORT SECTION......Page 91
DEAL WIND-DOWN TRIGGER......Page 92
CASH FLOW WATERFALL “BOX SCORE” SECTION......Page 94
ON THE WEB SITE......Page 96
OVERVIEW......Page 97
DELIVERABLES......Page 98
WHAT ARE THE DESIRED RESULTS?......Page 99
WHAT PROCESSES MUST THE MODEL PERFORM?......Page 100
INTRODUCTION TO TEMPLATE FILES......Page 101
COLLATERAL SELECTION REPORTS......Page 105
INELIGIBLE COLLATERAL REPORTS......Page 106
ELIGIBLE COLLATERAL REPORTS......Page 109
DESIGNING THE MENUS......Page 113
DELIVERABLES CHECKLIST......Page 118
ON THE WEB SITE......Page 119
Part IV: Learning the VBA Language......Page 121
OVERVIEW......Page 123
UNDER CONSTRUCTION......Page 125
CREATING THE EXTERNAL MODEL ENVIRONMENT......Page 126
CREATING THE INTERNAL MODEL ENVIRONMENT......Page 129
WRITING THE MAIN PROGRAM IN PSEUDO CODE......Page 136
ON THE WEB SITE......Page 138
OVERVIEW......Page 141
DELIVERABLES......Page 142
RECORDING VBA CODE......Page 143
RUNNING THE EDITED CODE......Page 152
USING RECORDED MACROS TO BUILD A SIMPLE MODEL......Page 158
DELIVERABLES CHECKLIST......Page 159
ON THE WEB SITE......Page 160
DELIVERABLES......Page 163
UNDER CONSTRUCTION......Page 164
ROLE OF THE MENU......Page 165
STRUCTURAL ELEMENTS OF A MENU......Page 169
VARIABLE TYPES......Page 171
HOW TO CREATE AND USE VARIABLES......Page 172
SCOPE OF VARIABLES......Page 176
ASSIGNING VALUES TO VARIABLES......Page 180
CONSTANTS......Page 181
PERFORMING CALCULATIONS WITH VARIABLES......Page 185
RANGES......Page 187
OBJECTS......Page 195
BUILDING A MENU FROM SCRATCH......Page 197
DELIVERABLES CHECKLIST......Page 203
ON THE WEB SITE......Page 204
DELIVERABLES......Page 207
DECISION STRUCTURES......Page 209
LOOPING STRUCTURES......Page 216
SUBROUTINES......Page 223
FUNCTIONS......Page 227
DELIVERABLES CHECKLIST......Page 229
ON THE WEB SITE......Page 230
DELIVERABLES......Page 231
PROGRESS MESSAGES......Page 232
ERROR MESSAGES......Page 235
RUNTIME OPTION ERROR CHECKING......Page 247
DELIVERABLES CHECKLIST......Page 248
ON THE WEB SITE......Page 249
UNDER CONSTRUCTION......Page 251
COLLATERAL REPORTING ACTIVITY......Page 252
INELIGIBLE COLLATERAL REPORTING......Page 253
ELIGIBLE COLLATERAL REPORTING......Page 259
CASH FLOW WATERFALL REPORT......Page 260
DEAL SUMMARY MATRIX REPORTS......Page 261
ASSUMPTIONS REPORT......Page 263
DELIVERABLES CHECKLIST......Page 264
ON THE WEB SITE......Page 265
Part V: Writing the Model......Page 267
OVERVIEW......Page 269
DELIVERABLES......Page 270
UNDER CONSTRUCTION......Page 271
MAIN PROGRAM......Page 273
THREE SUBROUTINE CALLS FROM THE MAIN PROGRAM......Page 276
BUILDING MENU ERROR CHECKING......Page 277
READING THE CONTENTS OF ALL THE MENUS......Page 281
READING THE CONTENTS OF THE COLLATERAL FILE......Page 288
DELIVERABLES CHECKLIST......Page 290
ON THE WEB SITE......Page 292
DELIVERABLES......Page 295
UNDER CONSTRUCTION......Page 296
THE COLLATERAL REPORTING PACKAGE......Page 297
INELIGIBLE COLLATERAL REPORT PACKAGE......Page 302
PRODUCING THE INELIGIBILITY REPORT PACKAGE......Page 313
HELPING MAKE IT ALL HAPPEN......Page 328
ELIGIBLE COLLATERAL REPORT PACKAGE......Page 329
WRITING OUT THE ASSUMPTIONS OF THE MODEL RUN......Page 340
ON THE WEB SITE......Page 342
DELIVERABLES......Page 345
PAUSE AWHILE AND REFLECT......Page 347
OVERVIEW OF THE CASH FLOW GENERATOR......Page 357
WRITING THE VBA CODE......Page 360
SETTING UP THE SCENARIO LOOPS......Page 379
CASH FLOW CALCULATION SEQUENCE......Page 380
BUILDING A CASH FLOW TRACE CAPABILITY......Page 385
DELIVERABLES CHECKLIST......Page 386
ON THE WEB SITE......Page 387
OVERVIEW......Page 389
DELIVERABLES......Page 390
UNDER CONSTRUCTION......Page 391
LOADING THE COLLATERAL CASHFLOWS......Page 394
CAPTURING THE WATERFALL RESULTS......Page 397
REPORTING THE SUMMARY REPORT OR “BOX SCORE” INFORMATION......Page 398
LOADING THE MATRIX REPORTS INFORMATION......Page 399
PRODUCING THE WATERFALL SPREADSHEET REPORT PACKAGE......Page 400
PRODUCING THE MATRIX REPORT PACKAGE......Page 406
DELIVERABLES CHECKLIST......Page 410
ON THE WEB SITE......Page 411
OVERVIEW......Page 413
COMPILING THE MODEL......Page 414
TYPES OF VBA ERRORS......Page 415
COMMON COMPILE ERRORS......Page 417
HOW TO RUN THE VBA DEBUGGER......Page 419
RUNTIME ERRORS......Page 428
LOGICAL ERRORS......Page 430
NEXT STEPS......Page 436
ON THE WEB SITE......Page 437
Part VI: Testing, Use, and Deployment......Page 439
DELIVERABLES......Page 441
UNDER CONSTRUCTION......Page 442
STEPS IN THE VALIDATION PROCEDURE......Page 444
VALIDATING THE MENU ERROR CHECKING CODE......Page 445
VALIDATING THE MENU INPUTS......Page 446
VALIDATING THE COLLATERAL SELECTION CODE......Page 447
VALIDATING THE ELIGIBLE COLLATERAL REPORTING CODE......Page 451
VALIDATING THE FLOATING RATE LOAN RESET PERIOD LEVELS......Page 452
VALIDATING THE CASH FLOW CALCULATION CODE......Page 463
VALIDATING THE CASH FLOW WATERFALL SPREADSHEET......Page 473
FUTURE BACK TESTING ACTIVITY......Page 476
ON THE WEB SITE......Page 477
OVERVIEW......Page 479
UNDER CONSTRUCTION......Page 480
INSTALLING A “RUN BUTTON”......Page 481
STEPS OF THE STRUCTURING PROCESS......Page 482
ORGANIZING THE OUTPUT......Page 483
STEP 1: DETERMINE COMPOSITION OF PROPOSED COLLATERAL PORTFOLIO......Page 484
STEP 2: INITIAL COLLATERAL SELECTION......Page 486
STEP 3: ADDRESSING PORTFOLIO CONCENTRATION ISSUES......Page 488
STEP 4: CORRECTING CONCENTRATION ISSUES......Page 491
STEP 5: RUNNING THE EXPECTED CASE CASH FLOWS......Page 492
STEP 6: EXPECTED CASE SENSITIVITY ANALYSIS......Page 496
TIME OUT! BUILDING A BATCH PROCESSING CAPABILITY......Page 498
SENSITIVITY ANALYSIS RESULTS......Page 501
HELP! A POST-PROCESSING PROGRAM......Page 511
STEP 7: RATING AGENCY STRESS TESTS......Page 512
STEP 8: ADJUSTING THE SELLER INTEREST......Page 514
SIZING RESULTS TABLE......Page 516
NEXT STEPS......Page 517
ON THE WEB SITE......Page 518
EXTRACTOR.XLS PROGRAM......Page 519
BATCH PROGRAM INPUTS MENU ENTRIES......Page 523
UNDER CONSTRUCTION......Page 527
PEEK-A-BOO! IMMEDIATE ACCESS TO MODEL RESULTS......Page 528
SINGLE SCENARIO REPORT FILE......Page 530
ABILITY TO RUN INTEREST RATE SENSITIVITIES......Page 537
ADDING INFORMATION TO THE ASSUMPTIONS PAGE......Page 553
ON THE WEB SITE......Page 558
Part VII: After the Model Is Written......Page 561
OVERVIEW......Page 563
ONLINE DOCUMENTATION......Page 564
EXTERNAL DOCUMENTATION......Page 569
DELIVERABLES CHECKLIST......Page 570
ON THE WEB SITE......Page 571
DELIVERABLES......Page 573
MAINTAINING THE MODEL ENVIRONMENT......Page 574
STREAMLINING THE VALIDATION AND PROMOTION PROCESS......Page 578
EVOLUTION OF THE FORM OF THE MODEL......Page 581
DELIVERABLES CHECKLIST......Page 582
ON THE WEB SITE......Page 583
Part VIII: Risk Assessment and Valuation......Page 585
DELIVERABLES......Page 587
UNDER CONSTRUCTION......Page 588
HOW MUCH OF THE STRUCTURING MODEL CAN WE USE?......Page 590
WHY TWO MODELS?......Page 591
DESIGNING THE NEW REPORT PACKAGE......Page 592
YOU CAN’T MAKE AN OMELET WITHOUT BREAKING SOME EGGS......Page 600
MODIFYING SOME OF THE CODE......Page 618
WHAT A BRAVE NEW MODEL THAT HAS SUCH FEATURES IN IT!......Page 630
VALIDATING THE MODEL......Page 637
ON THE WEB SITE......Page 638
OVERVIEW......Page 639
UNDER CONSTRUCTION......Page 640
INTRODUCTION TO PRICING A SECURITY......Page 641
ASSESSING RISK......Page 646
ADDING THE PRICING FUNCTIONALITY TO THE MONITORING MODEL......Page 651
DELIVERABLES CHECKLIST......Page 660
ON THE WEB SITE......Page 661
USING THE MONITORING MODEL TO ASSESS THE PERFORMANCE AND VALUE OF THE DEAL......Page 663
THREE MONTHS FROM ISSUANCE......Page 667
SIX MONTHS FROM ISSUANCE......Page 668
NINE MONTHS FROM ISSUANCE......Page 670
TWELVE MONTHS FROM ISSUANCE......Page 672
FIFTEEN MONTHS FROM ISSUANCE......Page 678
EIGHTEEN MONTHS FROM ISSUANCE......Page 679
TWENTY-ONE MONTHS FROM ISSUANCE......Page 683
TWENTY-FOUR MONTHS FROM ISSUANCE......Page 684
END OF THE EXERCISE......Page 686
ON THE WEB SITE......Page 688
Part IX: Farewell......Page 689
WHAT WE HAVE LEARNED......Page 691
NEXT STEPS......Page 693
OVERVIEW......Page 695
LOAN AMORTIZATION TERMS......Page 696
COMPONENTS OF THE CASH FLOWS OF A LOAN......Page 698
NEXT STEPS......Page 712
OVERVIEW......Page 713
FUTURE VALUE......Page 714
PRESENT VALUE......Page 717
TENOR MEASUREMENTS......Page 719
YIELD AND RETURN MEASUREMENTS......Page 721
LOSS AND COVERAGE MEASUREMENTS......Page 725
NEXT STEPS......Page 729
Exhibits Index......Page 731
Subject Index......Page 751