| |
| |
| Preface | |
| |
| |
| Acknowledgments | |
| |
| |
| About the Author | |
| |
| |
| Introduction | |
| |
| |
| The Three Basic Elements of a Cash Flow Model | |
| |
| |
| Inputs | |
| |
| |
| Cash Flow Structure | |
| |
| |
| Outputs | |
| |
| |
| The Process of Building a Cash Flow Model | |
| |
| |
| Plan and Design | |
| |
| |
| Obtain All Necessary Information | |
| |
| |
| Construct Basic Framework | |
| |
| |
| Develop Advanced Structure | |
| |
| |
| Validate Assumptions | |
| |
| |
| Test Model | |
| |
| |
| How This Book Is Designed | |
| |
| |
| |
| 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 | |
| |
| |
| |
| Inputs Sheet-Dates and Timing | |
| |
| |
| Dates and Timing on the Cash Flow Sheet | |
| |
| |
| Model Builder | |
| |
| |
| |
| Cash Flow Sheet-Dates and Timing | |
| |
| |
| Toolbox | |
| |
| |
| Naming Cells and Ranges | |
| |
| |
| Data Validation Lists | |
| |
| |
| Edate | |
| |
| |
| |
| Asset Cash Flow Generation | |
| |
| |
| Loan Level versus Representative Line Amortization | |
| |
| |
| How Asset Generation Is Demonstrated in Model Builder | |
| |
| |
| Asset Generation on the Inputs Sheet | |
| |
| |
| Fixed Rate Amortization Inputs | |
| |
| |
| Floating Rate Amortization Inputs | |
| |
| |
| Model Builder | |
| |
| |
| |
| Inputs Sheet Asset Assumptions and the Vectors Sheet | |
| |
| |
| Asset Generation on the Cash Flow Sheet | |
| |
| |
| Model Builder | |
| |
| |
| |
| Notional Asset Amortization on the Cash Flow Sheet | |
| |
| |
| Toolbox | |
| |
| |
| Offset | |
| |
| |
| Match | |
| |
| |
| MOD | |
| |
| |
| PMT | |
| |
| |
| |
| Prepayments | |
| |
| |
| How Prepayments Are Tracked | |
| |
| |
| SMM: Single Monthly Mortality | |
| |
| |
| CPR: Conditional Prepayment Rate | |
| |
| |
| PSA: Public Securities Association | |
| |
| |
| ABS: Absolute Prepayment Speed | |
| |
| |
| Historical Prepayment Data Formats | |
| |
| |
| Building Prepayment Curves | |
| |
| |
| Prepayment Curves in Project Model Builder | |
| |
| |
| The Effect of Prepayments on Structured Transactions | |
| |
| |
| Model Builder | |
| |
| |
| |
| Historical Prepayment Analysis and Creating a Projected Prepayment Curve | |
| |
| |
| Model Builder | |
| |
| |
| |
| Integrating Projected Prepayments in Asset Amortization | |
| |
| |
| Toolbox | |
| |
| |
| Weighted Averages Using SUMPRODUCT and SUM | |
| |
| |
| |
| Delinquency, Default, and Loss Analysis | |
| |
| |
| Delinquencies versus Defaults versus Loss | |
| |
| |
| The Importance of Analyzing Delinquency | |
| |
| |
| Model Builder | |
| |
| |
| |
| Building Historical Delinquency Curves | |
| |
| |
| Deriving Historical Loss Curves | |
| |
| |
| Model Builder | |
| |
| |
| |
| Building Historical and Projected Loss Curves | |
| |
| |
| Analyzing Historical Loss Curves | |
| |
| |
| Model Builder | |
| |
| |
| |
| Continued | |
| |
| |
| Projecting Loss Curves | |
| |
| |
| Model Builder | |
| |
| |
| |
| Continued | |
| |
| |
| Integrating Loss Projections | |
| |
| |
| The Effects of Seasoning and Default Timing | |
| |
| |
| Model Builder | |
| |
| |
| |
| Integrating Defaults in Asset Amortization | |
| |
| |
| |
| Recoveries | |
| |
| |
| Model Builder | |
| |
| |
| |
| Historical Recovery Analysis | |
| |
| |
| Projecting Recoveries in a Cash Flow Model | |
| |
| |
| Model Builder | |
| |
| |
| |
| Integrating Recoveries into Project Model Builder | |
| |
| |
| Final Points Regarding Recoveries | |
| |
| |
| |
| Liabilities and the Cash Flow Waterfall | |
| |
| |
| Priority of Payments and the Cash Flow Waterfall | |
| |
| |
| The Movement of Cash for an Individual Liability | |
| |
| |
| Types of Liabilities | |
| |
| |
| Fees | |
| |
| |
| Model Builder | |
| |
| |
| |
| Calculating Fees in the Waterfall | |
| |
| |
| Interest | |
| |
| |
| Model Builder | |
| |
| |
| |
| Calculating Interest in the Waterfall | |
| |
| |
| Principal | |
| |
| |
| Model Builder | |
| |
| |
| |
| Calculating Principal in the Waterfall | |
| |
| |
| Understanding Basic Asset and Liability Interactions | |
| |
| |
| |
| Advanced Liability Structures: Triggers, Interest Rate Swaps, and Reserve Accounts | |
| |
| |
| Triggers and Their Affect on the Liability Structure | |
| |
| |
| Model Builder | |
| |
| |
| |
| Incorporating Triggers | |
| |
| |
| Swaps | |
| |
| |
| Model Builder | |
| |
| |
| |
| Incorporating a Basic Interest Rate Swap | |
| |
| |
| Final Notes on Swaps | |
| |
| |
| Reserve Accounts | |
| |
| |
| Model Builder 7 | |