Modeling Structured Finance Cash Flows with Microsoft Excel

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 modelled 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 modelling 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 modelling 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 modelling 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 Modelling’’ 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 financial modelling 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 xi xii PREFACE 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.

Customer Reviews