Modeling Structured Finance Cash Flows with Microsoft Excel

Keith Allman is currently the principal trainer and founder of Enstruct, a structured finance analytics training company. Previously he was a Vice President at Citigroup in their Global Fixed Income, Structured Finance Division. He has built and reviewed hundreds of models from many different sectors. Allman received his master's degree in international affairs from Columbia University.

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