Intermediate Structured Finance Modeling E-Book

William Preinitz

• Herausgeber: John Wiley & Sons
• Kategorie: Fachliteratur
• Serie: Wiley Finance Editions
• Sprache: Englisch

Seitenzahl: 1416

Veröffentlichungsjahr: 2010

Beschreibung

This book provides a pragmatic, hands-on approach to reaching an intermediate level of sophistication as a financial modeler. Expanding on the first book, A Fast Tract to Structured Finance Modeling, Monitoring, and Valuation, the book will guide you step-by-step through using learned principals in new and more powerful applications. These applications will build on the knowledge of Excel and VBA gained, expand the use of Access for data management tasks, as well as PowerPoint and Outlook for reporting and presentation tasks.

Leseprobe

Contents

Cover

Series

Title Page

Copyright

Dedication

Preface

GOALS OF THE BOOK

WHAT YOU SHOULD KNOW

SETTING THE CONTEXT FOR LEARNING

THE STRUCTURE OF THE BOOK

A FISH STORY

A PERSPECTIVE ON MODELING

APPROACHING THIS MATERIAL

STYLE

A PARTING REMARK

On the Web Site

OVERVIEW

Acknowledgments

Part One: First Steps

Chapter 1: Introduction

OVERVIEW

WHY WAS THIS BOOK WRITTEN?

WHO IS THE TARGET AUDIENCE?

WHAT IS THE PURPOSE OF THE BOOK?

EXPANDING YOUR SOFTWARE SKILLS

EXPANDING YOUR MODEL DESIGN SKILLS

EXPANDING YOUR FINANCE KNOWLEDGE

ORGANIZED TO TEACH

CHAPTER ORGANIZATION

ACCOMPANYING WEB SITE

LEARNING THE “HARD” WAY

NOTE

Chapter 2: The Existing Model

OVERVIEW

DELIVERABLES

UNDER CONSTRUCTION

CRISIS DU JOUR

OVERVIEW OF THE CURRENT MODEL

CURRENT MODEL ENVIRONMENT

ON THE WEB SITE

Chapter 3: Conventions and Advice

OVERVIEW

DELIVERABLES

VBA CONVENTIONS

COMMON SENSE

ON THE WEB SITE

Chapter 4: Segregation of the Existing Model’s Functionality

OVERVIEW

DELIVERABLES

UNDER CONSTRUCTION

DELIVERABLES CHECKLIST

BREAKING UP IS HARD TO DO

ACCOMMODATING OUR DESIGN NEEDS

ADVANTAGES OF FUNCTIONAL SEGREGATION

DISADVANTAGES OF FUNCTIONAL SEGREGATION

ON THE WEB SITE

Chapter 5: Creating the Base Asset Model

OVERVIEW

DELIVERABLES

UNDER CONSTRUCTION

THE BIG PICTURE: “JUST THE ASSETS, MA’AM”

STEPPING THROUGH THE MODEL

TESTING THE COMPLETED BASE ASSET MODEL

ON THE WEB SITE

Chapter 6: Building the Base Liabilities Model

OVERVIEW

DELIVERABLES

UNDER CONSTRUCTION

LIABILITIES SIDE OF THE MODEL

WHAT TO LEAVE IN

STEPPING THROUGH THE MODEL

READING THE CASH FLOWS AND ASSUMPTIONS FROM A FILE

TESTING THE COMPLETED BASE LIABILITIES MODEL

ON THE WEB SITE

Chapter 7: Establishing the Model Environment

OVERVIEW

DELIVERABLES

UNDER CONSTRUCTION

IMPORTANCE OF A STANDARDIZED DIRECTORY STRUCTURE

CREATING DIRECTORIES AND DEFINING THEIR FUNCTIONS

OPERATING DIRECTORIES

ADMINISTRATIVE DIRECTORIES

CREATING NEW DIRECTORIES FOR THE MODEL AS WE NEED THEM

ON THE WEB SITE

Part Two: Building the New Assets Model

Chapter 8: Designing the New Collateral Cash Flow Generator

OVERVIEW

DELIVERABLES

UNDER CONSTRUCTION

IMPROVING THE CCFG MENUS: CONVERSION TO USERFORMS

IMPROVING THE CCFG DATA-HANDLING CAPABILITIES

IMPROVING THE CCFG COLLATERAL SELECTION PROCESS

IMPROVING THE CCFG CASH FLOW GENERATION PROCESS

IMPROVING THE CCFG REPORT GENERATION PROCESS

IMPROVING THE CCFG MESSAGING PROCESS

ON THE WEB SITE

Chapter 9: Writing the CCFG Menus and Data Sheets

OVERVIEW

DELIVERABLES

UNDER CONSTRUCTION

MENUS AND USERFORMS

MENUS OF THE CCFG

MAIN MENU

RUN OPTIONS MENU

COLLATERAL POOL MENU

COLLATERAL GEOGRAPHIC SELECTION CRITERIA MENU

FINANCIAL SELECTION CRITERIA MENU

CASH FLOW AMORTIZATION PARAMETERS MENU

COLLATERAL REPORTS MENU

ON THE WEB SITE

Chapter 10: Writing the Collateral Data Handling Code

OVERVIEW

DELIVERABLES

UNDER CONSTRUCTION

MANAGING MULTIPLE PORTFOLIO FILES

INITIAL DATA SCREENING

WRITING THE SCREENING PROCESS VBA CODE

WRITING THE INITIAL DATA SCREENING REPORTS

WRITING THE DEMOGRAPHIC METHODOLOGY RISK REPORTS

BUILDING A REPRESENTATIVE LINE GENERATOR PROGRAM

BUILDING THE REP LINE GENERATOR PROGRAM

ON THE WEB SITE

Chapter 11: Writing the Collateral Selection Code

OVERVIEW

DELIVERABLES

UNDER CONSTRUCTION

BUILDING THE CODE

FINANCIAL AND DEMOGRAPHIC SELECTION CODE

FINANCIAL COLLATERAL SELECTION PROCESS REPORTING

GEOGRAPHIC SELECTION CODE

GEOGRAPHIC CONCENTRATION CODE

INTRODUCTION TO GEOGRAPHIC REPORTING

ON THE WEB SITE

Chapter 12: Writing the Collateral Cash Flow Amortization Code

OVERVIEW

DELIVERABLES

UNDER CONSTRUCTION

QUICK REVIEW OF EXISTING CF GENERATION CODE

ADDING NEW MORTGAGE TYPES

NEW PREPAYMENT AND DEFAULT METHODOLOGIES

UNIFORM METHODOLOGY

GEOGRAPHIC METHODOLOGY

DEMOGRAPHIC METHODOLOGY

REPORTING THE RESULTS OF THE CASH FLOW CALCULATIONS

WRITING THE ASSUMPTIONS REPORT PACKAGE

ON THE WEB SITE

Chapter 13: Writing the CCFG Reporting Capability

OVERVIEW

DELIVERABLES

UNDER CONSTRUCTION

RECAPPING THE CCFG REPORT PACKAGE

CCFG ACTIVITIES AND REPORTS

ELIGIBLE COLLATERAL ASSESSMENT PROCESS

GEOGRAPHIC/DEMOGRAPHIC REPORTS

PRESENTATION REPORTS

ON THE WEB SITE

Part Three: Building the New Liabilities Model

Chapter 14: Designing the Liabilities Waterfall Model

OVERVIEW

DELIVERABLES

UNDER CONSTRUCTION

STRUCTURE OF THE DEAL: SOURCES AND USES OF FUNDS

DESIGN ELEMENTS OF THE LWM

MAIN MENU

REPORT PACKAGE MENU USERFORM

STRUCTURE INPUTS MENU

LIABILITIES WATERFALL WORKSHEET

PERFORMANCE SUMMARY PAGE

VBA CODE MODULES

REPORT TEMPLATE FILES

ON THE WEB SITE

Chapter 15: Writing the Liabilities Waterfall Model Spreadsheet

OVERVIEW

DELIVERABLES

UNDER CONSTRUCTION

DEAL STRUCTURE INPUTS

BUILDING THE LIABILITIES WATERFALL MODEL

PERIOD FACTORS WORKSHEET

TOTAL CASH FLOWS SECTION

RESULTS PAGE WORKSHEET

ON THE WEB SITE

Chapter 16: Writing the LWM VBA Code

OVERVIEW

DELIVERABLES

UNDER CONSTRUCTION

VBA REQUIREMENTS OF THE LWM

MENUS OF THE LWM

MAIN PROGRAM

ERROR CHECKING THE MAIN MENU INPUTS

IMPORTING THE LIABILITY STRUCTURE INPUTS

IMPORTING THE CASH FLOWS OF THE CCFG

LOADING AND RUNNING THE LWM

REPORTING THE RESULTS

OTHER FUNCTIONALITY

ON THE WEB SITE

Part Four: Access, PowerPoint, and Outlook

Chapter 17: Access: An Introduction

OVERVIEW

DELIVERABLES

UNDER CONSTRUCTION

BASICS OF ACCESS

OPENING ACCESS AND CREATING A DATABASE

ACCESS TABLES

GETTING THE DATA IN: ENTERING DATA

GETTING THE DATA BACK OUT: QUERIES

BASICS OF STRUCTURED QUERY LANGUAGE

CONCLUDING REMARKS

ON THE WEB SITE

Chapter 18: Implementing Access in the CCFG and LWM

OVERVIEW

DELIVERABLES

UNDER CONSTRUCTION

ACCESS AND THE COLLATERAL CASH FLOW GENERATOR

COLLATERAL PORTFOLIO DATA

GEOGRAPHIC SELECTION CRITERIA

GEOGRAPHIC CONCENTRATION CRITERIA

INITIAL DATA SCREENING CRITERIA

CUSTOM FINANCIAL SELECTION CRITERIA

RUN OPTIONS ELECTIONS

GENERATING THE LOAN PORTFOLIO

MONTHLY CASH FLOWS SCENARIOS

ACCESS IN CCFG REPORTING

ACCESS AND THE LIABILITIES WATERFALL MODEL

CONNECTING THE LWM TO THE CCFG

STRUCTURE WORKSHEET OBJECTS

RETRIEVING THE MONTHLY CASH FLOW SCENARIOS

STORING LWM RUN RESULTS IN THE DATABASE

CONCLUDING REMARKS

ON THE WEB SITE

Chapter 19: Implementing PowerPoint and Outlook in the CCFG

OVERVIEW

DELIVERABLES

UNDER CONSTRUCTION

USING POWERPOINT IN THE CCFG

DATA REQUIREMENTS

ADDING A NEW CLASS OF REPORTS

INTRODUCTION TO POWERPOINT IN VBA

PREPARING THE DATA FOR USE

FIRST TIME HOMEOWNERS SLIDE

CREATING THE NULL REPORT

MS OUTLOOK

ON THE WEB SITE

Part FIVE : Running the CCFG and the LWM

Chapter 20: Running the Models

OVERVIEW

DELIVERABLES

UNDER CONSTRUCTION

RUNNING THE CCFG, ANALYZING THE ASSET SIDE OF THE DEAL

LIABILITIES SIDE OF THE DEAL

PREPARING A POWERPOINT PRESENTATION ABOUT GEOGRAPHIC CONCENTRATION

ON THE WEB SITE

Afterword

Exhibits Index

Subject Index

Founded in 1807, John Wiley & Sons is the oldest independent publishing company in the United States. With offices in North America, Europe, Australia and Asia, Wiley is globally committed to developing and marketing print and electronic products and services for our customers’ professional and personal knowledge and understanding.

The Wiley Finance series contains books written specifically for finance and investment professionals as well as sophisticated individual investors and their financial advisors. Book topics range from portfolio management to e-commerce, risk management, financial engineering, valuation and financial instrument analysis, as well as much more.

For a list of available titles, please visit our Web site at www.WileyFinance.com.

Copyright © 2011 by William Preinitz and Matthew Niedermaier. All rights reserved.

Published by John Wiley & Sons, Inc., Hoboken, New Jersey. Published simultaneously in Canada.

No part of this publication may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning, or otherwise, except as permitted under Section 107 or 108 of the 1976 United States Copyright Act, without either the prior written permission of the Publisher, or authorization through payment of the appropriate per-copy fee to the Copyright Clearance Center, Inc., 222 Rosewood Drive, Danvers, MA 01923, [phone], fax [phone], or on the web at www.copyright.com. Requests to the Publisher for permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, [phone], fax [phone], or online at www.wiley.com/go/permissions.

Limit of Liability/Disclaimer of Warranty: While the publisher and author have used their best efforts in preparing this book, they make no representations or warranties with respect to the accuracy or completeness of the contents of this book and specifically disclaim any implied warranties of merchantability or fitness for a particular purpose. No warranty may be created or extended by sales representatives or written sales materials. The advice and strategies contained herein may not be suitable for your situation. You should consult with a professional where appropriate. Neither the publisher nor author shall be liable for any loss of profit or any other commercial damages, including but not limited to special, incidental, consequential, or other damages.

For general information on our other products and services or for technical support, please contact our Customer Care Department within the United States at [phone], outside the United States at [phone] or fax [phone].

Wiley also publishes its books in a variety of electronic formats. Some content that appears in print may not be available in electronic books. For more information about Wiley products, visit our web site at www.wiley.com.

Library of Congress Cataloging-in-Publication Data

Preinitz, William, 1950– Intermediate structured finance modeling : fast track VBA and access / William Preinitz and Matthew Niedermaier. p. cm. – (Wiley finance series) Includes index. ISBN 978-0-470-56239-0 (cloth); ISBN 978-0-470-92875-2 (ebk); ISBN 978-0-470-92877-8 (ebk); ISBN 978-0-92878-3 (ebk) 1. Finance–Computer simulation. 2. Finance–Mathematical models. 3. Microsoft Visual BASIC. I. Niedermaier, Matthew, 1981– II. Title. HG106.P745 2011 332.0285′5133–dc22 2010027041

William Preinitz: To Helen and Arne De Keijzer True friends are pearls beyond price. Thank you for your steadfast support, unwavering faith and loyalty.

Matthew Niedermaier: To My Parents

Preface

The preface of a book is where the author tries to make a good first impression on the reader. It is here that the author tries to entice and inform the reader as to the utility and attractiveness of the subject matter or story line. It is hoped there will be a meeting of the minds and the content of the book will meet your specific interests. The question of royalty payments aside, it is abundantly clear that authors want you to be interested because they themselves are interested in the subject matter. They have found it so interesting that they have spent a considerable amount of time and emotional and psychological energy, and have endured sore fingers and stiff wrists composing, editing, and reediting the manuscript that became the book you have before you! Get ready! I am now going to try to make that first good impression with a dual approach and try to convince you of both the practical and esthetic merits of this book.

Let us deal with the practical issues first.

GOALS OF THE BOOK

The goal of this book is to provide you with a set of examples of how to maximize the combined synergy of the four Microsoft products: the Excel spreadsheet tool, Visual Basic Application (VBA) language, the Access database, and the PowerPoint presentation software.

A set of immediately applicable modeling approaches, techniques, skills, and examples that can be employed to address a wide range of commercial and financial problems will be presented. Although the main example in the book is a structured finance application, the book itself is not intended or developed to be a text on structured finance per se. Every technique and approach that you see in this book can be widely generalized for almost any commercial activity. This is especially true of the data selection and reporting techniques. The book also discusses how to organize your development effort, keep your models coherent and flexible, and build a strong base for future work. As such, it contains valuable information, no matter what the type or scope of business application you are interested in creating.

The focus will be on the development of a set of financial models. The development effort will concentrate on the construction of a series of models to perform the cash flow evaluation and structuring activities supporting a deal financing a portfolio of residential mortgage loans. As such, it will make use of a data and computationally intense model with a developed user interface. The most important goal of this book is to teach you intermediate levels of financial modeling skills, not to train you in financial deal structuring techniques. Once you learn these modeling skills and the products used to implement them, you can apply them to a wide range of financial, or nonfinancial, problems. The best way to think about this is to conceptualize this combination of skills and product knowledge as sort of an intellectual Swiss Army knife. For the range of tasks that the knife is designed to address, there are very few that will not succumb to some combination of the available attachments!

Specifically you will learn:

To organize and build a set of directories to contain the model system you will develop.To take an existing large model and separate its two main functional components. This division will separate the Asset Pool portion of the model that calculates the collateral cash flows (the sources of funds) from the Liabilities Structure of the model (the uses of funds). From this starting point we will then develop each of the two pieces into more sophisticated analytical platforms. We will call the first model, the sources of funds model, the Collateral Cash Flow Generator (CCFG). The second model, the uses of funds model, will be called the Liabilities Waterfall Model (LWM).How to expand the capabilities of the CCFG. Doing this will involve enhancements to the model that will allow it to amortize additional mortgage types and apply improved collateral selection techniques. It will also update the model’s ability to perform prepayment and default analysis based on Geographic and Demographic Methodologies.How to expand the capabilities of the LWM by introducing a multiple tranche bond class structure and to employ other various liability structuring features.How to expand the existing reporting package to produce a series of more varied and complicated reports and to combine PowerPoint with VBA and Excel to produce reports directly in a PowerPoint format.To employ Access to handle large sets of input data for the models, such as collateral characteristics and modeling assumptions including prepayment, default, and market value decline inputs to the model.To use Access to store the results of both the models.To use Outlook to improve the messaging capabilities of the model.

WHAT YOU SHOULD KNOW

Keeping all of the above in mind, note that the word “Intermediate” is prominent in the title of the book. This is not a beginning-level book. Those of you who would like an introductory-level work on modeling are strongly encouraged to consult A Fast Track Guide to Structured Finance Modeling, Monitoring, and Valuation: Jump Start VBA by William Preinitz (Hoboken, NJ: John Wiley & Sons, 2009). The models that we will work with in this book are a direct extension of the models used to teach basic modeling skills in my earlier book. If you have some modeling experience and are confident in your mastery of the fundamental features of VBA and Excel, you can plunge right into this book.

This book is designed to address readers who have some experience under their belts already. To wit:

You have designed a basic model or two or, for better or worse, have inherited someone else’s model and modified it.You are familiar with Excel functionality in the form of functions and add-in features.You are familiar with all the basic VBA computational symbols, data types, and logical operators.You are familiar with decision structures, such as “If..Then..Else”. You are comfortable with both “For..Next” and “Do..While” or “Do..Until” loops and know when to use one rather than the other.You understand VBA variable types, give your variables reasonable names, and know how to declare their scopes.You comment your code, especially those subroutines and user-written functions that are either complex or lengthy.You can write subroutines that make sense and place these subroutines in VBA modules that organize them in a logical, obvious manner.You can run the VBA Debugger and use the 15 to 20 most commonly employed features.You know how to build a basic menu interface using VBA and Excel.You can design and implement a basic report package using Excel template files and VBA report-writing subroutines.

If you can answer an enthusiastic “YES!” to all, or most, of the above, you know the basics. The basics, however, are no longer enough! You are itching for more! There has to be a better way to do things! You are enthusiastic enough or dissatisfied enough to want to take a significant step forward.

Expanding your modeling skills now and quickly are the orders of the day. This effort will, it is hoped, not only improve the look and feel of your models but allow them to tackle problems that they cannot readily encompass now. This will mean developing the skills to make your models more capable, more effective, and at the same time more efficient, more understandable, and easier to use. Well, that is quite a to-do list! How are we going to get that done?

This book will teach you how to take the basic Excel/VBA model that was introduced in my earlier book and significantly enhance its speed, power, clarity, flexibility, and scope by taking you to the next level of model development. As specifically listed above, doing this will not only involve using the Excel and VBA products that we are familiar with but also Access, PowerPoint, and Outlook. That is not to say that you will not also improve your Excel and VBA skills. The backbone and guts of the models we will develop here are firmly Excel and VBA based. It is the creation of a much more powerful synergy by including the data handling power of Access, the presentation and communication capabilities of PowerPoint, and the messaging and monitoring aspects of Outlook that will allow you to immediately boost your models to a higher level of effectiveness and efficiency.

SETTING THE CONTEXT FOR LEARNING

This book is a companion volume to the previously mentioned Fast Track Guide to Structured Finance Modeling, Monitoring, and Valuation: Jump Start VBA. In that book we assumed that the reader was a financial analyst who had been recently assigned to a structured finance group in an investment bank. The first task was to work with someone who would be rotating out of the department at the end of the following month. This person would be handing off a nearly complete Excel waterfall model spreadsheet. The assets in the deal were a portfolio of small business loans and represented the part of the financing that was comprised of an owner note with no guarantee from the federal government. The challenge before our newcomer was to turn the waterfall spreadsheet into a fully completed model equipped with menus, a collateral cash flow generator, and a report package. The need was immediate, and fortunately several things were working in favor of a successful conclusion to the exercise. The loan portfolio was small and fairly straightforward. It consisted of approximately 2,800 loans each of which had only 26 data fields. The collateral selection process was limited, unchanging, and specifically expressed. The reports that were needed were, for the most part, working reports, and were relatively limited in both sophistication of the informational content and presentation format. The greatest factor in favor of success was that the reader had the Fast Track book that showed him or her, in a clear and heavily illustrated manner, what to do and how to do it.

In this book we will assume that the passage of time, one year, has brought both success and additional responsibility. Our newcomer is now becoming a seasoned structured finance modeler with a basic skill set sufficient unto the routine tasks at hand. The modeler probably, however, needs more information to be successful in attacking the next big step up the ladder of more complex and computationally burdensome potential future modeling.

The first issue that concerns our modeler is the number of apparent shortcomings in the current model and its environment that now need to be addressed. These issues initially centered around the limited capability of the model to change demand. The model users are not able to create custom collateral selection criteria or rules. They are unable to create specialized reports. The report package the model currently produces still requires significant additional effort to reformat its appearance to produce presentation-quality material. The demand for these presentation-quality reports for either internal management or external groups such as regulators, investors, or rating agencies is growing. There also appears to be growing dissatisfaction with the model’s execution speed. Although it is sufficient for small portfolios, say 1,000 to 3,000 loans, it has proven less satisfactory for a larger portfolio the department was asked to bid on that contained over 5,000 loans. To evaluate even the smallest changes to the Liabilities Structure, the entire collateral portfolio needs to be recalculated for each model run. Doing this proved frustrating to the structuring personnel and consumed large amounts of machine run time, spent mostly watching progress messages churn to completion.

You have recently been informed that the department routinely will be looking at a set of much larger portfolios, each loan of which will contain significantly more data. These portfolios are collections of current issuance mortgages on single-family residential properties. It is clear that you will also need to segregate the collateral cash flow generator from the liabilities waterfall functionality in the model to improve the efficiency of both. Doing this will also allow you to expand the capabilities of both, now separated, pieces of the original model, without the whole becoming too large and unwieldy. You will need to provide for a more flexible and interactive model. You will need to be able to sort through these larger collections of collateral using interactively specified collateral selection criteria. The reporting requirements will also change, and a much more flexible report package able to produce a wider range of report package configurations will be needed. The appearance and formats of these reports will become more sophisticated and more targeted to specific audiences. As the model becomes more complex, you will need to improve the messaging capabilities of the model.

The department is looking to you to come up with the Next New Great Solution. Fortunately you have some time, approximately three months, to get ready for the next onslaught. You will obviously need to consider a design to meet these challenges, which are beyond the limits of the current model’s framework.

Despite these challenges, you are both eager and optimistic! You have the current model to use as a base development platform. While much of the code will have to be broken down, examined, revised, and then subsequently rebuilt in an expanded form, the model is a firm base from which to proceed. You have recently been advised that you will also be able to draw on the time and expertise of another member of the department who has experience building Access databases. Last, but (I hope) not least, you have this book. Opportunity beckons!

I am sympathetic to your situation. I have been there myself on numerous occasions! Expectations run high, especially if you have been successful in the past.

THE STRUCTURE OF THE BOOK

Part One: First Steps

Part One contains Chapters 1 through 7.

Chapter 1, “Introduction,” is designed to impart the spirit and approaches that will be employed in the book. It lists the reasons the book was written, which are threefold. The first is to educate the reader regarding the challenges of developing larger-scale models. The second is to describe a real-world solution that many analysts will face in their careers. The third is to discuss the evolutionary paths of models and to present an example of significant evolution from a more simple model to a more sophisticated one. This chapter will also describe the intended audience of the book and the Excel/VBA skills you will need to be comfortable with the material.

In Chapter 2, “The Existing Model,” we review the state of the current model that forms the basis of the instructional plan of the remainder of the book. In this scenario you have previously written a model that has performed well for some period of time. A new business opportunity has arisen that is simply beyond the capabilities of the current model. You need to use the existing model as a base from which to develop a more powerful model that addresses the requirements of this new analytical task. You need to conduct a review of the current model to determine what you are going to be able to salvage from it. You will also need to review the current model directory system to determine it is also adequate.

In Chapter 3, “Conventions and Advice,” we step away from the modeling process for a moment. We have completed our initial assessment of the existing model and will now give some thought to how we will work before we begin the rebuilding/construction process. In this chapter there are five pieces of hard-won general advice. Take the time to avail yourself of these little nuggets. They are useful admonitions and can save you both headaches and heartaches as you proceed about your task.

In Chapter 4, “Segregation of the Existing Model’s Functionality,” we discuss the organization of the base model. We examine its structure, menus, data, and modular organization. In the next two chapters we break this model into two parts. The first is the cash-generating portion, the sources of funds side, which we will call the Base Asset Model (BAM). The second model is the liabilities structure portion, the uses of funds side, which we will call the Base Liabilities Model (BLM), that applies those cash flows to a liability structure.

In Chapter 5, “Creating the Base Asset Model,” we start with the original small business model and strip out everything not related to the evaluation of the collateral. It contains all the model functionality that screens and selects the collateral that will be eligible to be included in the asset portfolio of the deal. It also contains the calculation routines that produce sets of cash flow projections based on various prepayment and default methodologies.

In Chapter 6, “Building the Base Liabilities Model,” we find ourselves back at the start again, working from a second copy of the original model. In this instance we focus on segregating and discarding all portions of the model not related to the liabilities side of the deal. When we complete this exercise, we will have a pair of compiled working models that will replicate the earlier, original model’s complete functionality. These two new models serve as the springboard for the subsequent modeling development for the rest of the book. The Base Asset Model will be developed into the Collateral Cash Flow Generator Model. The Base Liabilities Model will be developed into the Liabilities Waterfall Model.

Finally in Chapter 7, “Establishing the Model Environment,” we design and implement the directory environment to accommodate the new models once they are written. We also create other portions of the environment to accommodate the sources of data, the template files of the reports, and preservation and organization of all of the output of the model.

Thus by the end of Part One we have split two complete models from the original. This pair of models forms the departure points for the new development efforts to follow. We have also created the environment to house and organize these future models and all of their supporting files and data bases.

Part Two: Building the New Assets Model

Part Two consists of Chapters 8 through 13. In this part we design and implement the new Asset model. The name of this model is the Collateral Cash Flow Generator (CCFG). This model performs the tasks of collateral screening, selection, and amortization.

In Chapter 8, “Designing the New Collateral Cash Flow Generator,” we do just that. With the BAM as out point of departure, we consider what changes we need to prepare the model for the asset class that we next analyze, that of residential mortgage loans. We look at the different types of collateral that we need to amortize. We develop a new approach to organizing and applying assumptions regarding collateral performance. We also look at the construction of a collateral pool from all or parts of other collateral pools. We also address the subject of using aggregated collateral data known as representative lines. Last we revise and expand the collateral reporting capabilities of the model.

Chapter 9, “Writing the CCFG Menus and Data Sheets,” addresses the design and implementation of the new menus of the CCFG, their supporting VBA code (including extensive error checking), and first appearance of UserForms in menus.

In Chapter 10, “Writing the Collateral Data Handling Code,” we describe the changes needed to read, merge, select, and report on either single or combinations of multiple collateral portfolios.

Chapter 11, “Writing the Collateral Selection Code,” focuses on changes to the process of determining eligible collateral on both a loan-by-loan and a portfolio basis. These eligibility tests can be based on the financial, demographic, or geographic characteristics of the collateral or any combination of those factors. This chapter will also discuss the issue and treatment of geographic concentration issues.

Chapter 12, “Writing the Collateral Cash Flow Amortization Code,” addresses writing the VBA code that performs the amortization calculations for all the types of loans found in the collateral pool. These include a number of new mortgage types that the earlier model was incapable of handling. The chapter also introduces the concepts of geographic and demographic prepayment and default calculation methodologies.

Chapter 13, “Writing the CCFG Reporting Capability,” addresses changes to the cash flow reporting process. Here we implement the capability to produce everything from simple working group reports, to stratification and cross-tabulation reports, and later presentation reports in the same model run.

At the conclusion of Part Two we will have completed the CCFG.

Part Three: Building the New Liabilities Model

Part Three consists of Chapters 14, 15, and 16. These chapters are the mirror image of Chapters 9 to 13 described above. They describe the design and construction of the Liabilities Model that will be the consumer of the collateral cash flows of Part Two.

In Chapter 14, “Designing the Liabilities Waterfall Model,” we examine the wish list of additional capabilities we would like to see on the structuring side. These will include a multiple tranche bond structure and the concept of subordination in which some tranches of the debt absorb the effects of collateral underperformance to preserve the performance of senior notes. We also look at revisions to the reporting package to detail the performance of each of these now very different bonds.

In Chapter 15, “Writing the Liabilities Waterfall Model Spreadsheet,” we structure and construct the Liabilities Waterfall spreadsheet and several menus to accommodate both inputs and outputs to the spreadsheet.

In Chapter 16, “Writing the LWM VBA Code,” we write all the supporting code needed to manage both the input and output activities of the LWM. This includes finding, opening, and reading collateral cash flow files; managing the run operations of the model; and specifying and producing various structure performance reports.

At the conclusion of Part Three we will have completed the Liabilities Waterfall Model.

Part Four: Access, PowerPoint, and Outlook

Part Four consists of Chapters 17, 18, and 19. These chapters announce the introduction of the Access Database product into the modeling process.

Chapter 17, “Access: An Introduction,” provides a concise but firm grounding in the basics of the product, its forms and commands, and a foreshadowing of the specific areas of the model we apply it to.

Chapter 18, “Implementing Access in the CCFG and LWM,” illustrates the areas where we employ database technology to improve both the CCFG and the LWM. A partial list includes the management of collateral data, the collection and reporting of collateral selection results, and the capture and storage of amortized collateral cash flows. We also apply Access to the task of supplying the collateral cash generated by the CCFG to the LWM and to record the performance of the liabilities of the deal under various scenarios.

Chapter 19, “Implementing PowerPoint and Outlook in the CCFG,” addresses the use of Excel and VBA in combination with PowerPoint to produce presentation appearance reports directly from a model run. It also introduces the use of MS Outlook to improve the communications of the model with the outside world.

Part Five: Running the CCFG and the LWM

Part Five consists of Chapter 20.

Chapter 20, “Running the Models,” presents a step-by-step model run employing both the CCFG and the LWM. This model run starts with a collateral cash flow file, a set of collateral selection constraints, and various collateral performance assumptions. From these the CCFG generates a series of cash flow files. These files, in conjunction with a file containing inputs describing the Liabilities Structure, are used to run the LWM.

Indices

This book also contains an Exhibits Index and a Subject Index. The former is an index of all exhibits in the book by their financial, functional, and computational contents. We hope this index will quickly and painlessly provide the reader with a reference to any exhibit seen in the journey through the book. Under the auspice of “A single picture is worth 1,000 words,” this index will serve as a fast reference to that one picture that you need to see!

A FISH STORY

These are very difficult times for the global financial markets. The need for trained people to model, measure, and manage financial risk has never been greater. The needs of governments to understand and quantify existing risk in the holdings of their financial communities, whether they are investors, issuers, or sellers of assets, has never been greater. The need for competent modelers has never been greater. The skills that you will learn in this book will be applicable to all forms of financial products and activities. Keeping the above in mind, I wish to reemphasize that while this book uses a structured finance example, it is not a book primarily concerned with structured finance theory or practice. It is a book designed to show you how to apply the skills and techniques you will learn here to any large data and computationally intense problem. The goal of the book is to provide you with a broad-based design and implementation skills, not to teach you how to finance the receivables from a revolving credit card portfolio.

My grandfather was an avid freshwater fisherman. He had a sign on the garage wall of our summer cottage in Illinois that read “God does not subtract from a man’s allotted time the number of hours that he spends fishing.”

One day when I was quite young, he was in the process of cleaning his equipment in preparation of renewing the struggle with the fish for the upcoming summer season. It was the first time that I had seen the full panoply of hooks, lines, bobbers, sinkers, and the complete battery of artificial lures. All was now fully revealed! Among the lures was one fully 9 inches long that mounted five sets of razor-sharp hooks. It was his “muskie” (muskellunge) lure. The muskellunge is the largest and most ferocious member of the pike family. Looking in awe at the size of this lure, I asked why it was so much bigger than anything else he had in the box.

My grandfather said simply, “If you want to catch big fish, you need big bait.”

It is the same with modeling. The ideas, the techniques, and above all the combination of the five products—Excel, VBA, Access, PowerPoint, and Outlook—will allow you to confront the most forbidding of modeling assignments. I am here to give you the “big bait” and the right tackle. The combination of programming techniques and software products will allow you to take on the large, complex, and time-critical problems that you will face in the marketplace today.

A PERSPECTIVE ON MODELING

Each one of us is an individual, and we all experience the world through the lens of our own personal viewpoints and desires. The ancient adage from Aesop’s Fables, “One man’s meat is another man’s poison,” is a universal truth.

Having said that, one of the first things that I would like to convey to you is the tremendous intellectual pleasure and challenge that I have experienced over the last 30 years of my time spent in this pursuit. Modeling can be stimulating in a number of different ways and is one of the few activities that are both creative and analytical at the same time. As we humans over time have employed ever more complex methodologies to understand the world around us, modeling has grown from the simple yet elegant geometry of the Euclidian Greeks, through the calculus of Newton and Leibnitz, to Descartes and the founders of probability theory, and on to Turing and the advent of electronic computing. The current combination of easily learned software products and high-capacity hardware allow us to combine both sophisticated computational and logical structures and apply them to large amounts of data. This capability has exponentially expanded the scope of problems that we can now model.

In the 1970s we lived in a world where most of the structured finance analysis we perform today would have been impossible. Data entry was slow and tedious; the amount of available memory to hold information and results was limited. Processing speeds of the machines themselves were snail-like compared with even the most bargain basement laptop available today. We are now able to command far more power and carry it around with us wherever we go!

One of the fascinating things about modeling is that once you learn how to do it, and do it in an organized and rigorous manner, the numbers of problems you can investigate are almost unlimited. I have personally modeled over 70 different types of cash flow–producing asset types and have managed people who have added 50 to 60 types to that list.

APPROACHING THIS MATERIAL

The teaching approach I employ is based on achieving the goals of the above scenario. This is especially true when it comes to use of the existing model as the base for development. It is extremely rare that a modeling exercise is begun without any reference to the existing software resident in either the firm as a whole or your own department. All models share a large degree of functional commonality. Data moves into the model in a variety of formats from the environment. The model goes through a computational assessment process and the results of the process are produced.

It is by far the most likely circumstance that the base of a new development effort will be the corpus of an existing model. This is not as straightforward a procedure as it might seem. Modeling is a skill that needs to be developed along with the other skills you will learn here. To this end, the new model development we embark on here starts by using as much as is possible from the existing model. General George Patton once said, “I never pay for the taking the same ground twice!” and neither should you. Use your earlier development efforts to accelerate the progress of the model.

Learning to create new and different applications, or creating new features and capabilities within the context of existing models, is an exercise that will prove valuable. The responsibility for undertaking an incremental or substantial enhancement to a new or existing models is a circumstance that you will find yourself in over and over again. By the way, in case you think that statement is made in a pejorative context, it is not! As you accumulate a larger and larger body of code, you will integrate more techniques and products into your work. You will find that reuse of this code will allow you to become more and more productive with each successive assignment. If the code is well designed, you can reuse greater and greater portions of it, saving yourself quite a lot of work!

I hope that this approach will prove to be both clear and productive. You will notice that I did not say easy. If you find it easy, well, all the better! Remember that you will get out of this book what you put into it. There is a lot of material, both in the form of code and explanatory material on the Web site. Although there are many ways to utilize this material, the effort can be summarized in three simple levels:

1 Low. Do nothing.

2 Medium. Download the code and run it.

3 High. Make an effort to replicate the design and application efforts in each chapter. Think hard about the best way to do things, study the examples closely, and run the code to replicate and understand all the results.

If you download each of the code samples from the Web site and simply run them, you will get far less out of the material than if you try to replicate the work first on your own and then compare your results. Try to understand not just the line-by-line content of the examples; also read the chapters with the specific intention of understanding the design and implementation concepts behind the selection of these products and techniques. Read, think, and then code.

STYLE

One of the biggest challenges when two authors contribute to a single work is insuring the book has a cohesive voice. I, Bill Preinitz, took on the role of being the primary writer of this book. I am also solely responsible for the small interjections in the material that you will see from time to time, such the “A Fish Story” above. Please feel free to blame me exclusively for any of the humor or personal observations, such as all the material in Chapter 3, that does not meet with your approval! I would like to thank Matt for his critical assistance in several key areas of the work. He designed and built the Liabilities Waterfall spreadsheet in Chapter 15, which is the foundation of the Liabilities Waterfall Model. He also designed and built the Access code in Chapters 17 and 18. His early drafts of these chapters shaped their contents and exhibits. He is also responsible for the Outlook application in Chapter 19. In addition, Matt provided a current market perspective as an industry practitioner.

A PARTING REMARK

It is never easy to look at someone else’s code. It is, however, more often the case that that is the situation in which you will find yourself. If you have some initial difficulty, try to treat it as an exercise in decryption, a decipherment of a lost language, or any other framework to lend interest and novelty to the exercise.

I will close with this thought. It is verbatim from my earlier work:

“Modeling can be real fun. It can instill in you a sense of accomplishment and self-worth. There is nothing so satisfying as a job well done, especially if none of your peers can accomplish as much! I have had a lot of fun modeling over the years and I would like you to share in the experience.”

To the extent that I can help you down the path, avoiding the potholes and the speed bumps (and sometimes the other crazy drivers), I will consider the time I spent on this book quite worthwhile!

Just remember one thing. No model, no matter how sophisticated, efficient, and informative, can ever replace human judgment and insight. All models have limitations. These limitations are inherent in the quality and completeness of their assumptions, data, and analytical perspective. Do not produce results merely to see numbers on a piece of paper or graphs on a presentation slide. You must, first and foremost, seek to understand all aspects of the business situation you are to analyze. Be cognizant of both its quantitative and the qualitative aspects. It is also imperative that you, as an analyst, recognize that any model is merely a tool and not a means unto itself. It is your intelligence and creativity that, in conjunction with the judicious use of the model, produces the final intellectual product you offer.

WILLIAM PREINITZ December 2010

On the Web Site

OVERVIEW

With this book you get access to a Web site (www.wiley.com/go/vba02).

The Web site for this book contains supplemental material that corresponds to the contents of the chapters. Each chapter section will describe the files on the Web site and their relationship to the chapter material.

Form of the Material

The material in support of a book chapter on the Web site can take the form of any or all of the following content:

Models and auxiliary programs (either partially or fully developed).Input files needed by the model. These files could contain such items as the assumptions needed to run the model, data files containing raw or processed information, or files saved from previous runs that will allow you use the models more efficiently.Any report files produced by the model. These files are especially useful if you are replicating the model runs with the downloaded software and wish to check your results against those in the book.Economic performance information relating to the collateral of the deal. The assets used by the models of this book are residential mortgages. In this case, such information will be various prepayment and default estimates.