Modeling Structured Finance Cash Flows with Microsoft Excel

Keith Allman is currently a vice president in the Global Special Situations Group at Citigroup, where he focuses on emerging market analysis.

He has created, audited, and used hundreds of cash flow models for mortgages, autos, equipment leases, credit cards, project finance, and multiple esoterics.

Prior to his current role, he worked in the Structured Finance group at Citigroup modeling transactions for their conduits.

Mr. Allman began his career in finance at MBIA, Inc., a leading financial guarantor, where he was a senior analyst in its quantitative analytics group.

Outside of corporate work, Mr. Allman has written a computer curriculum and provides instruction for low-income individuals through Streetwise Partners.

His education includes a master’s degree in international affairs with a concentration in finance and banking from Columbia University and bachelor degrees in political science and psychology from UCLA.

Modeling Structured Finance Cash Flows Contents

• Introduction
• Dates and Timing
• Asset Cash Flow Generation
• Prepayments
• Delinquency, Default, and Loss Analysis
• Recoveries
• Liabilities and the Cash Flow Waterfall
• Advanced Liability Structures: Triggers, Interest Rate Swaps, and Reserve Accounts
• Analytics and Output Reporting
• Understanding the Model
• Automation Using Visual Basic Applications (VBA)
• Conclusion

Preface to 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 modeled transactions.

He answered with a grin: ‘‘Trial by fire.’’ From then on, I could not have counted the gray hairs 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 about 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, the graduate school classes provide context for everyday application, but typically on very specialized subjects that still left me with no framework to build a financial model.

My next step 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 financial modeling knowledge, expertise, and fluidity 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 basics 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 give the reader enough depth to work proficiently 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 of 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.

Download Modeling Structured Finance Cash Flows with Microsoft Excel: A Step-by-Step Guide PDF 

Author(s): Keith A. Allman

Publisher: Wiley, Year: 2007

ISBN: 9780470042908,0470042907

Download Modeling Structured Finance Cash Flows with Microsoft Excel in PDF Format For Free.