A Fast Track to Structured Finance Modeling, Monitoring, and Valuation

Jump Start VBA

William Preinitz

• Publisher: Wiley
• Year: 2009
• Print ISBN: 9780470398128
• eBook ISBN: 9780470446065
• Edition: 1
• Topic: Business
• Subtopic: Finance
• Index: Business

This book is designed to start with simple examples that progressively develop the reader's confidence to take on more complex tasks. There is very little theoretical discussion about computer science, operations research algorithms, mathematics, or finance. The thrust of the book is to teach the reader to break complex tasks down into simple tasks. It then looks to implement those simple tasks into VBA code using a critical subset of the features of the language.

The tentative contents is: (1) Why? What? Who? Where? and How? (2) Common Sense (3) Securitizing A Loan Portfolio (4) Understanding the Excel Waterfall (5) Designing the VBA Model (6) Laying the Model Groundwork (7) Recorded Macros: A First Look at the VBA Language (8) Writing Menus: An Introduction to Data, Ranges, Arrays, and Objects (9) Controlling the Flow of the Model (10) Building Messaging Capabilities (11) Designing the Model's Reports (12) Main Program and Menus (13) Writing the Collateral Selection Code (14) Calculating the Cash Flows (15) Running the Waterfall: Producing Initial Results (16) Debugging the Model (17) Validating the Model (18) Running the Model (19) Building Additional Capabilities (20) Documentation of the Model (21) Managing the Growth of the Model (22) Building Portfolio Monitoring Model (23) Valuation Techniques: How do we Determine Price? (24) Challenging Times For the Deal (25) Parting Admonitions

PART One

Introduction

CHAPTER 1

Why? What? Who?
Where? and How?

THE IMMORTAL QUESTION(S)

If you use the Search feature for Amazon.com and search for the combination of subjects “Excel,” “VBA,” and “Modeling,” it currently returns a total of almost 500 results. Some of these are essentially duplicate entries. A book with three editions will show up once for each of the editions.

What makes this book different?

Why should you spend the time reading it, and even much more time learning the concepts and practices laid out within?

What do you need to know even before you start?

If you are already proficient or even expert with Excel, what possible advantage can you gain by learning the Visual Basic Application (VBA) language?

What can you do with this knowledge and how will that help you?

Is learning a programming language as difficult as everyone says it is?

These questions are all reasonable and valid. Except for the specific reference to Excel, they are the broad questions that any author should be prepared to answer whatever the subject of their work.

I am prepared to do just that.

Why Is This Book Different?

This book is different because it is the only book that equally balances the mindset of modeling with a code intensive approach, and at the same time is aimed at the complete novice.

What do I mean by that?

Although this book has as its subject a structured finance model application, its intellectual focus is to get you to start thinking about dynamic problem solving. We will take a moderately complex set of rules and relationships and help you to break them down into some very small components. The process of the book will work with you to implement these discrete elements without losing sight of the bigger picture.

To accomplish this you will be exposed to VBA code, more VBA code, and even more VBA code! You will see a few, then tens, then hundreds, and finally thousands of lines of VBA code. At the end of each chapter you will see the model in a progressive and additive state of development. First there will be just the Excel workbook of a single worksheet containing the deal waterfall and nothing else. Then over the course of the following chapters we will add one piece of code and then another and another until at last we have a finished model. I will tell you now that you cannot learn VBA by just reading it. At the end of each chapter you need to take the code out and play with it. Step through its operations using the VBA debugger tool. Look at the structure and the types of VBA statements used to achieve the results of the incremental addition of the chapter. Then take the code copy it, muck around with it and see if you can replicate its functionality on your own. Start simply and start small. Don’t attempt to build an elaborate menu error-checking subroutine the first time out. Make a scaled down version of what you are seeing and then try to build up once you have it working. If you do this you will make much more progress and at a faster rate than you would if you don’t. Don’t worry, I will provide you with lots of code to look at! You will see practically every single line of VBA code used to create the model. It is up to you to dive in and start swimming. In fact it is the only way to learn.

There will be little or no theoretical discussions about anything. There will be no discussions about the Zen of design, or the Tao of programming. That can wait! It can wait until you have mastered a critical minimum subset of the VBA language. It can wait until you can fashion problem-solving code in a manner that will not be a danger to yourself or others. It can wait until you know enough to have your own informed opinions. That time is not now.

I have strong opinions about what works and what does not work, all based on 25 years of experience. You will hear them expressed from time to time in the material to come. The focus of this book is on concrete, immediate, measurable progress toward problem solving through the use of VBA.

The two effects that this will have are:

1. You will become much more facile in jumping between the specific and the general scope of decomposing and reconstructing complex problems. In a phrase, you will increase your mental agility.

2. Upon recognizing specific problem elements, you will be able to reflexively choose the correct and concise VBA code to express its logical or computational solution.

By the end of this book I would hope that you have substantially improved your mental acumen in problem decomposition and synthesis, and that you have begun to think in VBA. Here I will make one final point. When learning a foreign language, one starts with basic vocabulary and the rudimentary syntactical rules and works from there. As one builds upon this early knowledge, the vocabulary is expanded, and certain short expressions are integrated into one’s repertoire. There, then, is a stage where entire sets of sentences spring to mind as complete entities. Final mastery of the language is accomplished when one dreams in the language.

I do not expect you to dream in VBA by the end of this book, but I do expect you to at least catnap in it.

In a word, this is not a book about a computer language.

It is not a book about structured finance per se although the applications are particularly relevant to the conditions in the financial markets today.

It is a book aimed specifically at improving your complex problem solving abilities.

It is a book about giving you the ability to translate your thoughts into a medium that is relatively easy to learn and that can be powerfully applied across a wide Range of practical problem solving processes.

It is a book whose goal is to add to the inventory of your personal tool kits an increasingly valuable, and monetarily rewarded, dimension. By the way, in case you missed it, the crucial word in the preceding sentence was the word monetarily.

I want everyone who reads this book, who does the work, and who perseveres in the pursuit of this knowledge and these skills to understand that this book will make you more valuable. It will do so by giving you an added dimension that many of your peers do not have. These skills will improve your personal market value and your career potential.

Who Is This Book Aimed At?

In order of immediacy, this book is aimed at the following people:

• Men and women working in the financial industry, especially at the levels of vice president and below, who want or need to develop their modeling skills in a self-paced environment. This especially relates to the current environment. The volumes of structured finance deals being currently created and issued is significantly less than it was last year or two years ago. Much of the analytical effort is now directed to helping risk managers understand what they have and the prospects for their positons in the current market environment. For people involved in these activities the book packs a double punch! You will see the development of a structuring model that will teach you the fundamental elements common to all structured finance deals. You will then see the transformation of the structuring model we will create into a monitoring and valuation model. This is an especially valuable twofold experience for people working in today’s marketplace.

• Intermediate level managers who supervise these people but have little or no knowledge of, or experience with modeling. They want to know what is possible, and how it is to be accomplished. More importantly, they need to be able to form realistic ideas of what is possible in a given time frame. There is a saying about tradeoff: “I can be fast, accurate, and cheap. The problem is that you can have only two of the three.” As mentioned above, in today’s environment the aspect of risk management and portfolio monitoring is critical. Intermediate level managers will need to be up-to-date as to the current health of their businesses. Modeling in VBA/Excel providers them with an excellent platform to improve their information flows and knowledge of the sensitivity of their holdings.

• Students in graduate or undergraduate programs wishing to develop modeling skills.

• Anyone that wants to have the ability to intensively explore problems that require a quick, fluid medium of analysis. This includes anyone in the financial world dealing in risk and especially those that seek to measure it and evaluate risk arbitrage through modeling.

• Anyone in any commercial, nonprofit, government, or military function that needs a tool for dynamic problem solving.

• Anyone that is a regular Excel user and wants to significantly expand the scope of their ability to address issues that the use of Excel alone cannot.

• Anyone that wants to ask the question “What If?” and needs a powerful, easy-to-learn tool to translate their thoughts into concrete quantitative reality.

• Lastly, anyone that thinks they know and are good at VBA. I am certain that you will find many useful techniques here that you may have overlooked.

What Assumptions Do I Make About You?

• Basic computer literacy.

• A working, although not expert, knowledge of Excel.

• A working knowledge of algebra is required to understand some of the bond math and mortgage amortization processes.

• A positive attitude and a willingness to mentally engage in the process. It is assumed that the reader is willing to put in the work and to think about the work that they are doing. Learning a programming language, especially VBA, is not easy. It is however, not that difficult either. This of course posits two assumptions:

1. You really want to learn.

2. You will continue to want to learn and will persevere beyond the first, second, or third setback.

• That you are a professional in the broadest sense of the word.

• That you want to produce a quality product and that you will take the time and make the effort to do so.

• That you will feel a sense of responsibility to yourself and others not to produce a model that is hard to work with, easy to make mistakes with, or inaccurate in its function.

• That you will take responsibility for your work by completely testing and validating the model results.

• Lastly, that you will take the time to protect yourself and others by providing a minimum of documentation necessary to prevent the model from being inappropriately applied.

WHAT ARE THE ADVANTAGES OF LEARNING VBA?

When I was ten years old I announced to my grandfather, who was a plumber, that I felt that I had mastered enough mathematics to last me the rest of my life. The scene of this revelation was a modest summer cottage by a lake. I was feeling rather reflective. It was a hot day in July, and the reality of returning to school was a distant idea. My conclusions seemed quite reasonable.

He smiled at this and made little in the way of return comment.

The next day he asked me to dig a hole off to the side of the front yard. He gave me a small plastic coffee scoop and an old table knife. He led me over to a stake he had placed in the ground about three feet from a hedge. He told me the hole must be exactly one foot on each side with sharp clean corners. I was to do nothing else until I had finished the task. Nothing else specifically included playing with friends, swimming, and fishing with him.

At first everything went smoothly. I cut through the sod and the first six inches of topsoil. I then hit the roots of the hedge, a rock the size of dinner plate, and lastly a mixture of clay and pebbles. Two-and-a-half hot days later, complaining, bored, frustrated, and angry I finished.

The next day he had me repeat the process using any tool I wanted from the shed. I was finished in 15 minutes. That, he said, was the difference between life with and without the knowledge of mathematics.

I did not think about this incident for years until I was called to travel to the London office of the investment bank with which I worked to debrief a leasing specialist who was leaving the firm. He had created an Excel model to run the cash flows of a structured finance asset-backed securitization (ABS) deal. This model was viewed by everyone in that department with a mixture of awe and reverence. The fastest PC in the office was dedicated solely to the running of this model.

We spent the first two days reviewing it. It consisted of over 65 spreadsheets that amortized 1,900 individual leases. He indicated to me that he had made some changes to it recently. These changes had decreased the runtime from its original eight to nine hours to the present six and a half hours. At the moment, however, we needed to produce 15 scenarios for one rating agency and nine for another. Eight more were required for internal credit approval and four by the credit wrap provider of the deal. In total, that was 216 hours of runtime, not factoring the setup times for each scenario, and assuming that everything went well thereafter. The vast majority of the computational time was taken up in determining the monthly cash flows from the existing lease agreements and then calculating additional cash flows from a releasing agreement that served to extend the terms of the original contract. The uses of the cash from the collateral were straightforward and represented a small portion of the computational burden of the program.

I began by replacing the thousands of columns of Excel that calculated the individual leases. Using amortization subroutines that I had previously written in VBA (very similar to those in this book), we were able to eliminate all but seven of the spreadsheets. Five of these spreadsheets were menus; one displayed the monthly collateral cash flow summary and one the performance of the debt supported by those cash flows. This moved the vast majority of the computational burden of the model from the Excel spreadsheets and into the VBA code.

The runtime of the new program, (even before we tried to optimize the VBA calculation sequences), was now reduced to eleven minutes per scenario. This was a 97% reduction in the time it took to produce a single scenario. We next implemented a simple looping structure that allowed the model to run groups of related scenarios without human intervention. Finally we separated the output reports from the model itself into standalone Excel files that used a common report format. This allowed us to produce up to 50 variants of a base scenario without human intervention. Now we could put in the scenario specifications, turn the model on, and go to a series of well-deserved extended lunches. Upon our return we would find various sets of files, each containing a unique scenario. The model had finished, printed the files including graphics, sorted the scenario files based on their characteristics into particular directories, and produced a summary report resident in the model.

Some holes can be dug far more efficiently with knowledge of VBA than without such knowledge. There are also entire classes of problems that cannot be solved by E...

Table of contents

• Title Page
• Copyright Page
• Dedication
• Preface
• About the Web Site
• PART One - Introduction
• PART Two - The Securitization Process
• PART Three - Designing the Model
• PART Four - Learning the VBA Language
• PART Five - Writing the Model
• PART Six - Testing, Use, and Deployment
• PART Seven - After the Model Is Written
• PART Eight - Risk Assessment and Valuation
• PART Nine - Farewell
• APPENDIX A - Mortgage Math
• APPENDIX B - Bond Math
• Exhibits Index
• Subject Index