Modeling structured finance cash flows with Microsoft Excel : a step-by-step guide.

Abstract

During my first analytics position after graduate school, I asked a vice president at our company what the best way was to learn how his group modeled transactions. He answered with a grin: ‘‘Trial by fire.’’ From that point on, I could not have counted the gray hairs that I developed trying to figure out the most precise and efficient method of modeling a transaction. I am pleased to say those days are behind me and it no longer takes me hours to construct a powerful, accurate model. Nevertheless I am dismayed when I speak with finance peers who convey their desire to learn better financial modeling and are intimidated by the task or simply at a loss for where to begin. At those moments, I often think how I came to acquire the knowledge and skills necessary to model a diverse array of financial transactions. I recalled hours spent poring over ‘‘how-to’’ books about Excel that were filled with hundreds of functions and formulas and left me feeling like I didn’t have any idea where to start modeling a transaction. The how-to books provide excellent basics of application operation yet they do not offer any context for applying those skills. My next thought was graduate school, where many courses such as Statistics, Economics, Corporate Finance, Capital Markets, and Decision Making utilize Excel for assignments and examinations. Unfortunately, for everyday application, the graduate school classes provide context, but typically on very specialized subjects that still left me with no framework to build a financial model. The next step I took was to purchase more advanced books with the words ‘‘Financial Modeling’’ in the title. With these, I found the topics highly theoretical or applicable to extremely focused fields that do not translate into a practical model oriented towards cash flow analysis. I realized that most of my knowledge, expertise, and fluidity in financialmodeling came from working in analytics groups. There I focused on interpreting structures from documents and benefited by learning from others about how to convert the deal structure into a working model. Between the insurance and banking industries, I’ve seen and built numerous models—from the very basic that are little more than a balance sheet with formulas to incredibly complex models involving stochastic simulations. With every model on which I have worked, I have tried to take away what I have felt to be the best attributes and incorporate those features into my current modeling. As my experience with financial models continues to grow, I definitely feel that I am at a point where I have worked with enough models to distinguish trends, common practices, and characteristics of exceptional financial modeling. My personal experience has been with cash-flow-based models seen in most fixed income, structured, asset-based, or project finance transactions. To avoid trial by fire, this book teaches the framework and specifics of cash-flow-based modeling using structured finance as a context. If examples are followed from beginning to end, the result will be a fully operating cash flow model that the reader built step by step. Aside from being able to create a model from the ground up, understanding how each component is built and interacts will aid a reader who needs to work with other peoples’ models. I often find working with another person’s model more difficult than building a new one from scratch. It takes time to discern the core components and functionality of the model. However, most well-thought-out models have similar basic elements that can be understood and manipulated. This book intends to cover each of those elements and provide the reader with enough depth to proficiently work with existing models. Looking back at the moment when I had that trial-by-fire response, I certainly do not feel that has to be the standard that anyone should have to rely on. Regardless if the reader is a new finance professional who wants to learn how to build a model, a seasoned professional who works with others’ models, a structured finance professional looking for analyses specific to the field, or simply anyone interested in understanding financial modeling better, I feel that passing on my experience in the form of a book with practical examples can help make the learning process easier and more efficient.