Modeling Structured Finance Cash Flows with Microsoft Excel – Keith A. Allman

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
v
vi CONTENTS
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 39
OFFSET 39
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
Contents vii
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
viii CONTENTS
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
Contents ix
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