Intermediate Structured Finance Modeling, with Website: Leveraging Excel, Vba, Access, and PowerPoint

This book is written for financial analysts who have a workingknowledge of Excel and VBA and who wish to enhance theirmarketability by improving their modeling expertise. The goalof the book is to broaden the reader's VBA skills and at thesame time to progressively integrate Access, PowerPoint, andOutlook into an existing model. Significant attention is alsodirected to the design and execution of an Excel/VBA interfaceemploying detailed UserForms. In addition, the constructionof various reports, first in Excel, then Access, and finallyPowerPoint is undertaken. Regardless of your area offinancial expertise, this book serves as an essential guide tomastering leveraging the effects of Microsoft products in whateverapplications you choose to build.

The focus of the book is a case study containing a structuredfinance model. The case study model was originally designedfor the securitization of a portfolio of commercial small businessloans. This model now needs to be expanded to securitize amuch larger portfolio of residential mortgages.

The Original Structuring model will be bifurcated into a Sourcesof Funds model, the assets of the collateral pool, and a Uses ofFunds model, the liabilities waterfall model. This will givethe reader experience working with an existing model as a basedeparture point of development rather than the less common"start from scratch" approach. Once the BaseAsset Model (BAM) and the Base Liabilities Models (BLM) arecomplete, each model will serve as the platform for futuredevelopment. From the BAM and BLM a pair of much more robustmodels, the Collateral Cash Flow Generator (CCFG) and theLiabilities Waterfall Model (LWM), respectively, are created. The CCFG is designed to process a significantly greater quantity, variety, and complexity of collateral types. It alsointroduces the analyst to Geographic based prepayment methods aswell as those using Financial/Demographic factor approaches. TheLWM, operating independently of the CCFG, reads the collateral cashflow scenarios and applies them across a two tiered, six tranchebond structure. The liabilities waterfall contains provisionfor an interest rate swap, performance triggers and a variety ofother credit enhancement features.

With the CCFG and LWM now complete Access is introduced. Access will replace various Excel/VBA code modules in the CCFG suchas the collateral data management, data scrubbing and reporting, data sufficiency testing, and eligibility screening. Accessis also employed to export and the various collateral cash flows. In the LWM Access is used to import the cash flows, storethe liability structure specifications, and export/save theresultant structure performance. PowerPoint is thenintroduced in the CCFG to allow for the fully automated productionof presentation report packages. Last Outlook is added to themodels to inform the analyst of model performance and toautomatically distribute the various report packages topre-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 forall of the models is included on the Web site. The Web sitealso includes Web chapters containing detailed instructions on howto create and populate a directory environment to store this code, run the models, and organize the results. Lastly there areother Web chapters containing supplemental information explainingthe structure and function of the original model, bond and mortgagemath (with calculation examples), representative line generatorprograms, and a tutorial on the construction and integration ofUserForms.

This book is the intermediate level companion volume to AFast-Track to Structured Finance Modeling, Monitoring, andValuation: Jump Start VBA
Preinitz
Wiley 2009, anintroductory work on the subject.