Modeling Structured Finance Cash Flows with Microsoft Excel E-Book

Keith A. Allman

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

Seitenzahl: 328

Veröffentlichungsjahr: 2010

Beschreibung

A practical guide to building fully operational financial cash flow models for structured finance transactions Structured finance and securitization deals are becoming more commonplace on Wall Street. Up until now, however, market participants have had to create their own models to analyze these deals, and new entrants have had to learn as they go. Modeling Structured Finance Cash Flows with Microsoft Excel provides readers with the information they need to build a cash flow model for structured finance and securitization deals. Financial professional Keith Allman explains individual functions and formulas, while also explaining the theory behind the spreadsheets. Each chapter begins with a discussion of theory, followed by a section called "Model Builder," in which Allman translates the theory into functions and formulas. In addition, the companion website features all of the modeling exercises, as well as a final version of the model that is created in the text. Note: Companion website and other supplementary materials are not included as part of eBook file.

Leseprobe

Table of Contents

Title Page

Copyright Page

Preface

Acknowledgments

About the Author

Introduction

THE THREE BASIC ELEMENTS OF A CASH FLOW MODEL

THE PROCESS OF BUILDING A CASH FLOW MODEL

HOW THIS BOOK IS DESIGNED

CHAPTER 1 - Dates and Timing

TIME PROGRESSION

DATES AND TIMING ON THE INPUTS SHEET

DAY-COUNT SYSTEMS: 30/360 VERSUS ACTUAL/360 VERSUS ACTUAL/365

MODEL BUILDER 1.1: INPUTS SHEET—DATES AND TIMING

DATES AND TIMING ON THE CASH FLOW SHEET

MODEL BUILDER 1.2: CASH FLOW SHEET—DATES AND TIMING

TOOLBOX

CHAPTER 2 - Asset Cash Flow Generation

LOAN LEVEL VERSUS REPRESENTATIVE LINE AMORTIZATION

HOW ASSET GENERATION IS DEMONSTRATED IN MODEL BUILDER

ASSET GENERATION ON THE INPUTS SHEET

MODEL BUILDER 2.1: INPUTS SHEET ASSET ASSUMPTIONS AND THE VECTORS SHEET

ASSET GENERATION ON THE CASH FLOW SHEET

MODEL BUILDER 2.2: NOTIONAL ASSET AMORTIZATION ON THE CASH FLOW SHEET

TOOLBOX

CHAPTER 3 - Prepayments

HOW PREPAYMENTS ARE TRACKED

HISTORICAL PREPAYMENT DATA FORMATS

BUILDING PREPAYMENT CURVES

PREPAYMENT CURVES IN PROJECT MODEL BUILDER

THE EFFECT OF PREPAYMENTS ON STRUCTURED TRANSACTIONS

MODEL BUILDER 3.1: HISTORICAL PREPAYMENT ANALYSIS AND CREATING A PROJECTED ...

MODEL BUILDER 3.2: INTEGRATING PROJECTED PREPAYMENTS IN ASSET AMORTIZATION

TOOLBOX

CHAPTER 4 - Delinquency, Default, and Loss Analysis

DELINQUENCIES VERSUS DEFAULTS VERSUS LOSS

THE IMPORTANCE OF ANALYZING DELINQUENCY

MODEL BUILDER 4.1: BUILDING HISTORICAL DELINQUENCY CURVES

DERIVING HISTORICAL LOSS CURVES

MODEL BUILDER 4.2: BUILDING HISTORICAL AND PROJECTED LOSS CURVES

ANALYZING HISTORICAL LOSS CURVES

MODEL BUILDER 4.2 CONTINUED

PROJECTING LOSS CURVES

MODEL BUILDER 4.2 CONTINUED

INTEGRATING LOSS PROJECTIONS

MODEL BUILDER 4.3: INTEGRATING DEFAULTS IN ASSET AMORTIZATION

CHAPTER 5 - Recoveries

MODEL BUILDER 5.1: HISTORICAL RECOVERY ANALYSIS

PROJECTING RECOVERIES IN A CASH FLOW MODEL

MODEL BUILDER 5.2: INTEGRATING RECOVERIES INTO PROJECT MODEL BUILDER

FINAL POINTS REGARDING RECOVERIES

CHAPTER 6 - Liabilities and the Cash Flow Waterfall

PRIORITY OF PAYMENTS AND THE CASH FLOW WATERFALL

TYPES OF LIABILITIES

MODEL BUILDER 6.1: CALCULATING FEES IN THE WATERFALL

MODEL BUILDER 6.2: CALCULATING INTEREST IN THE WATERFALL

MODEL BUILDER 6.3: CALCULATING PRINCIPAL IN THE WATERFALL

UNDERSTANDING BASIC ASSET AND LIABILITY INTERACTIONS

CHAPTER 7 - Advanced Liability Structures

TRIGGERS AND THEIR AFFECT ON THE LIABILITY STRUCTURE

MODEL BUILDER 7.1: INCORPORATING TRIGGERS

SWAPS

MODEL BUILDER 7.2: INCORPORATING A BASIC INTEREST RATE SWAP

FINAL NOTES ON SWAPS

RESERVE ACCOUNTS

MODEL BUILDER 7.3: INCORPORATING A CASH-FUNDED RESERVE ACCOUNT

CONCLUSION OF THE CASH FLOW WATERFALL

TOOLBOX

CHAPTER 8 - Analytics and Output Reporting

INTERNAL TESTING

MODEL BUILDER 8.1: CASH IN VERSUS CASH OUT TEST

MODEL BUILDER 8.2: BALANCES AT MATURITY TESTS

MODEL BUILDER 8.3: ASSET PRINCIPAL CHECK TEST

PERFORMANCE ANALYTICS

MODEL BUILDER 8.4: CALCULATING MONTHLY YIELD

MODEL BUILDER 8.5: CALCULATING BOND-EQUIVALENT YIELD

MODIFIED DURATION

MODEL BUILDER 8.6: CALCULATING MODIFIED DURATION

OUTPUT REPORTING

MODEL BUILDER 8.7: CREATING THE OUTPUT REPORT

TOOLBOX

CHAPTER 9 - Understanding the Model

THE COMPLETE MODEL IN REVIEW

UNDERSTANDING THE EFFECTS OF INCREASED LOSS

VARYING PRINCIPAL ALLOCATION METHODOLOGIES

VARYING LOSS TIMING

VARYING RECOVERY RATE AND LAG

THE VALUE OF A SWAP

ADDITIONAL TESTING

CHAPTER 10 - Automation Using Visual Basic Applications (VBA)

CONVENTIONS OF THIS CHAPTER

THE VISUAL BASIC EDITOR

VBA CODE

SIMPLE AUTOMATION FOR PRINTING AND GOAL SEEK

MODEL BUILDER 10.1: AUTOMATING PRINT PROCEDURES

MODEL BUILDER 10.2: AUTOMATING GOAL SEEK TO OPTIMIZE ADVANCE RATES

UNDERSTANDING LOOPING TO AUTOMATE THE ANALYTICS SHEET

MODEL BUILDER 10.3: AUTOMATING GOAL SEEK TO PERFORM TRANSACTION ANALYTICS

AUTOMATED SCENARIO GENERATION

MODEL BUILDER 10.4: CREATING A TRANSACTION SCENARIO GENERATOR

WORKING WITH MACROS IN EXCEL

CHAPTER 11 - Conclusion

THE INVESTMENT BANKER’S PERSPECTIVE

THE INVESTOR’S PERSPECTIVE

THE ISSUER’S PERSPECTIVE

THE FINANCIAL GUARANTOR’S PERSPECTIVE

THE BIG PICTURE PERSPECTIVE

APPENDIX - Using This Book with Excel 2007

About the CD-ROM

Index

Founded in 1807, John Wiley & Sons is the oldest independent publishing company in t he 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 © 2007 by Keith A. Allman. All rights reserved.

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

Published simultaneously in Canada.

Wiley Bicentennial Logo: Richard J. Pacifico.

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 http://www.wiley.com/go/permission.

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.

Designations used by companies to distinguish their products are often claimed as trademarks. In all instances where John Wiley & Sons, Inc. is aware of a claim, the product names appear in initial capital or all capital letters. Readers, however, should contact the appropriate companies for more complete information regarding trademarks and registration.

Microsoft and Excel are registered trademarks of Microsoft Corporation.

Microsoft Excel screenshots reprinted with permission from Microsoft Corporation.

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:

Allman, Keith A., 1977-

Modeling structured finance cash flows with Microsoft Excel : a step-by-step guide / Keith A. Allman.

p. cm.—(Wiley finance series)

Includes bibliographical references and index.

ISBN 978-0-470-04290-8 (paper/cd-rom)

1. Cash management—Mathematical models. 2. Cash flow—Mathematical models. 3. Microsoft Excel (Computer file) 4. Corporations—Finance—Mathematical models. I. Title.

HG4028.C45A.15’50285554--dc22

2006025757

Preface

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 financial modeling 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.

KEITH A. ALLMAN

New York, New YorkDecember 2006

Acknowledgments

My career in finance began at MBIA, Inc., a leading financial guarantor and provider of specialized financial services. There three individuals provided an excellent introduction to financial modeling, namely Henry Wilson, William Devane, and Melissa Brice-Johnson. In particular, I would like to thank Henry for giving me the opportunity to work on a variety of transactions and William for showing me many fundamental techniques. After leaving MBIA, I wrote the first three chapters of this book as part of a proposal to John Wiley & Sons, where I would like to thank my peer editors Maria Costa for her in-depth review as well as Lionel Beehner for his editorial suggestions. Further editorial suggestions were made by Omar Haneef and Matthew Niedermaier as the book developed, both whom I would like to thank especially for their work on the text and Model Builder exercises. Also, this book could not possibly have been brought to market without the amazing support of William Preinitz, who read through, approved, and was a driving force in receiving Citigroup’s compliance approval. Lastly, I am very grateful for Siobhan Devine, whose patience and encouragement kept me centered throughout everything.

Also at Wiley, I would like to thank Bill Falloon for working with me from taking the proposal to a signed contract, Emilie Herman for her consistent involvement in every aspect, Laura Walsh and her team for the cover and marketing work, and Mary Daniello and her team for copyediting such a detail-oriented book.

K. A. A.

About the Author

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 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.

Introduction

The basic idea behind any financial model is to bring order and understanding to the numerous variables and complex information that financial transactions present. Learning to build one from a blank spreadsheet is often a daunting task to newcomers because of the sheer amount of information and nearly infinite methods of manipulating data. This book seeks to bring a systematic, well-explained method to constructing a particularly popular and adaptable type of model—the cash flow model. Through the use of thorough explanation, graphical examples, and the simultaneous application of learned methods featured in the exercises, anyone with a background in finance and basic spreadsheet understanding can develop and understand a fully functioning financial model.