The focus of the book is a case study containing a structured finance model. The case study model was originally designed for the securitization of a portfolio of commercial small business loans. This model now needs to be expanded to securitize a much larger portfolio of residential mortgages.
The Original Structuring model will be bifurcated into a Sources of Funds model, the assets of the collateral pool, and a Uses of Funds model, the liabilities waterfall model. This will give the reader experience working with an existing model as a base departure point of development rather than the less common “start from scratch” approach. Once the Base Asset Model (BAM) and the Base Liabilities Models (BLM) are complete, each model will serve as the platform for future development. From the BAM and BLM a pair of much more robust models, the Collateral Cash Flow Generator (CCFG) and the Liabilities Waterfall Model (LWM), respectively, are created. The CCFG is designed to process a significantly greater quantity, variety, and complexity of collateral types. It also introduces the analyst to Geographic based prepayment methods as well as those using Financial/Demographic factor approaches. The LWM, operating independently of the CCFG, reads the collateral cash flow scenarios and applies them across a two tiered, six tranche bond structure. The liabilities waterfall contains provision for an interest rate swap, performance triggers and a variety of other credit enhancement features.
With the CCFG and LWM now complete Access is introduced. Access will replace various Excel/VBA code modules in the CCFG such as the collateral data management, data scrubbing and reporting, data sufficiency testing, and eligibility screening. Access is also employed to export and the various collateral cash flows. In the LWM Access is used to import the cash flows, store the liability structure specifications, and export/save the resultant structure performance. PowerPoint is then introduced in the CCFG to allow for the fully automated production of presentation report packages. Last Outlook is added to the models to inform the analyst of model performance and to automatically distribute the various report packages to pre-designated mailing lists.
The book contains over 750 exhibits of reports, menus, calculation, and algorithmic examples and most of all code. The complete Excel, VBA, Access, PowerPoint, and Outlook code for all of the models is included on the Web site. The Web site also includes Web chapters containing detailed instructions on how to create and populate a directory environment to store this code, run the models, and organize the results. Lastly there are other Web chapters containing supplemental information explaining the structure and function of the original model, bond and mortgage math (with calculation examples), representative line generator programs, and a tutorial on the construction and integration of UserForms.
This book is the intermediate level companion volume to A Fast-Track to Structured Finance Modeling, Monitoring, and Valuation: Jump Start VBA; Preinitz; Wiley 2009, an introductory work on the subject.
Keywords: model evolution, tiered reporting, balloon mortgages, hybrid adjustable rate mortgages, market value decline, recovery lag period, liabilities waterfall, multiple tranche structure, interest rate swap, financial selection criteria, demographic selection criteria, geographic selection criteria, geographic concentration criteria, eligible collateral, floater rate index, pricing spreads, structured finance, VBA module organization, Constant variables, Excel UserForms, stratification reports, cross-tabulation reports, dynamic report generation, representative lines, demographic risk factors, data sufficiency testing, CPR, PSA, Geographic Prepayment/Default Methodology, Demographic Default Methodology, representative line generator program, menu data sheets, Metropolitan Statistical Areas, assumption reporting, demographic risk factors, geographic prepayment/default curves, PowerPoint presentation reports, payment subordination, credit enhancement, targeted subordination, excess spread, step down characteristics, cleanup call, performance triggers, overcollateralization, loss allocations, Access data bases, SQL, ADODB connections, null reports, stress case modeling, William Preinitz, Matthew Niedermaier, structured finance modeling