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

by Allman, Keith A.

• ISBN13: 9780470042908
• ISBN10: 0470042907
• Edition: 1st
• Format: Paperback
• Copyright: 2007-03-09
• 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.

Author Biography

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.

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

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

Table of Contents provided by Publisher. All Rights Reserved.