Modeling Structured Finance Cash Flows with Microsoft^? Excel^?: A Step-by-Step Guide

by Keith Allman

• ISBN13: 9780470117170
• ISBN10: 0470117176
• Format: eBook
• Copyright: 2007-04-01
• Publisher: Wiley

Summary

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 CD-ROM features all of the modeling exercises, as well as a final version of the model that is created in the text. Note: CD-ROM/DVD and other supplementary materials are not included as part of eBook file.

Table of Contents

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

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

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 3.1: Historical Prepayment Analysis and Creating a Projected Prepayment Curve

Model Builder 3.2: 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 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

The Effects of Seasoning and Default Timing

Model Builder 4.3: Integrating Defaults in Asset Amortization

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

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 6.1: Calculating Fees in the Waterfall

Interest

Model Builder 6.2: Calculating Interest in the Waterfall

Principal

Model Builder 6.3: 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 7.1: Incorporating Triggers

Swaps

Model Builder 7.2: Incorporating a Basic Interest Rate Swap

Final Notes on Swaps

Reserve Accounts

Model Builder 7

Table of Contents provided by Publisher. All Rights Reserved.