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.

Table of Contents:
Preface xi Acknowledgments xiii About the Author xv Introduction 1 The Three Basic Elements of a Cash Flow Model 3 Inputs 3 Cash Flow Structure 4 Outputs 5 The Process of Building a Cash Flow Model 5 Plan and Design 5 Obtain All Necessary Information 6 Construct Basic Framework 6 Develop Advanced Structure 6 Validate Assumptions 6 Test Model 7 How This Book Is Designed 7 Chapter 1 Dates and Timing 9 Time Progression 9 Dates and Timing on the Inputs Sheet 10 Day-Count Systems: 30/360 versus Actual/360 versus Actual/ 365 11 Model Builder 1.1: Inputs Sheet—Dates and Timing 12 Dates and Timing on the Cash Flow Sheet 14 Model Builder 1.2: Cash Flow Sheet—Dates and Timing 15 Toolbox 18 Naming Cells and Ranges 18 Data Validation Lists 19 EDATE 21 Chapter 2 Asset Cash Flow Generation 23 Loan Level versus Representative Line Amortization 23 How Asset Generation Is Demonstrated in Model Builder 27 Asset Generation on the Inputs Sheet 27 Fixed Rate Amortization Inputs 28 Floating Rate Amortization Inputs 28 Model Builder 2.1: Inputs Sheet Asset Assumptions and the Vectors Sheet 29 Asset Generation on the Cash Flow Sheet 33 Model Builder 2.2: Notional Asset Amortization on the Cash Flow Sheet 33 TOOLBOX 40 OFFSET 40 MATCH 40 MOD 41 PMT 41 Chapter 3 Prepayments 43 How Prepayments Are Tracked 43 SMM: Single Monthly Mortality 44 CPR: Conditional Prepayment Rate 44 PSA: Public Securities Association 44 ABS: Absolute Prepayment Speed 45 Historical Prepayment Data Formats 46 Building Prepayment Curves 46 Prepayment Curves in Project Model Builder 47 The Effect of Prepayments on Structured Transactions 48 Model Builder 3.1: Historical Prepayment Analysis and Creating a Projected Prepayment Curve 48 Model Builder 3.2: Integrating Projected Prepayments in Asset Amortization 53 Toolbox 56 Weighted Averages Using SUMPRODUCT and SUM 56 Chapter 4 Delinquency, Default, and Loss Analysis 59 Delinquencies versus Defaults versus Loss 59 The Importance of Analyzing Delinquency 60 Model Builder 4.1: Building Historical Delinquency Curves 62 Deriving Historical Loss Curves 64 Model Builder 4.2: Building Historical and Projected Loss Curves 67 Analyzing Historical Loss Curves 69 Model Builder 4.2 Continued 69 Projecting Loss Curves 70 Model Builder 4.2 Continued 71 Integrating Loss Projections 73 The Effects of Seasoning and Default Timing 75 Model Builder 4.3: Integrating Defaults in Asset Amortization 76 Chapter 5 Recoveries 83 Model Builder 5.1: Historical Recovery Analysis 85 Projecting Recoveries in a Cash Flow Model 86 Model Builder 5.2: Integrating Recoveries into Project Model Builder 87 Final Points Regarding Recoveries 88 Chapter 6 Liabilities and the Cash Flow Waterfall 89 Priority of Payments and the Cash Flow Waterfall 89 The Movement of Cash for an Individual Liability 90 Types of Liabilities 91 Fees 91 Model Builder 6.1: Calculating Fees in the Waterfall 91 Interest 94 Model Builder 6.2: Calculating Interest in the Waterfall 95 Principal 100 Model Builder 6.3: Calculating Principal in the Waterfall 100 Understanding Basic Asset and Liability Interactions 105 Chapter 7 Advanced Liability Structures: Triggers, Interest Rate Swaps, and Reserve Accounts 107 Triggers and Their Affect on the Liability Structure 107 Model Builder 7.1: Incorporating Triggers 108 Swaps 113 Model Builder 7.2: Incorporating a Basic Interest Rate Swap 114 Final Notes on Swaps 117 Reserve Accounts 117 Model Builder 7.3: Incorporating a Cash-Funded Reserve Account 118 Conclusion of the Cash Flow Waterfall 122 Toolbox 123 AND and OR 123 Chapter 8 Analytics and Output Reporting 125 Internal Testing 125 Cash In versus Cash Out 125 Model Builder 8.1: Cash In versus Cash Out Test 126 Balances at Maturity 128 Model Builder 8.2: Balances at Maturity Tests 128 Asset Principal Check 129 Model Builder 8.3: Asset Principal Check Test 129 Performance Analytics 130 Monthly Yield 130 Model Builder 8.4: Calculating Monthly Yield 130 Calculating the Monthly Yield 132 Bond-Equivalent Yield 133 Model Builder 8.5: Calculating Bond-Equivalent Yield 133 Modified Duration 133 Model Builder 8.6: Calculating Modified Duration 134 Output Reporting 135 Model Builder 8.7: Creating the Output Report 136 The Importance of Testing and Output 140 Toolbox 140 Conditional Formatting 140 Goal Seek 141 Array Formulas 142 Chapter 9 Understanding the Model 145 The Complete Model in Review 145 Understanding the Effects of Increased Loss 147 Varying Principal Allocation Methodologies 150 Varying Prepayment Rates 151 Varying Loss Timing 152 Varying Recovery Rate and Lag 152 The Value of a Swap 153 Additional Testing 153 Chapter 10 Automation Using Visual Basic Applications (VBA) 155 Conventions of This Chapter 155 The Visual Basic Editor 156 The Menu Bar 156 The Project Explorer and the Properties Window 157 VBA Code 157 Simple Automation for Printing and Goal Seek 158 Model Builder 10.1: Automating Print Procedures 158 Model Builder 10.2: Automating Goal Seek to Optimize Advance Rates 161 Understanding Looping to Automate the Analytics Sheet 164 Model Builder 10.3: Automating Goal Seek to Perform Transaction Analytics 164 Automated Scenario Generation 167 Model Builder 10.4: Creating a Transaction Scenario Generator 167 Working with Macros in Excel 173 Chapter 11 Conclusion 175 The Investment Banker’s Perspective 175 The Investor’s Perspective 176 The Issuer’s Perspective 176 The Financial Guarantor’s Perspective 177 The Big Picture Perspective 177 Appendix: Using This Book with Excel 2007 179 About the CD-ROM 189 Index 193