Modeling Structured Finance Cash Flows with Microsoft Excel A Step-by-Step Guide
by Allman, Keith A.Edition: 1st
Format: Paperback
Pub. Date: 2007-03-09
Publisher(s): Wiley
Other versions by this Author
List Price: $95.42
Buy New
Usually Ships in 2-3 Business Days
$90.88
Buy Used
Usually Ships in 24-48 Hours
$67.50
Rent Book
Select for Price
Rent Digital
$67.20*
How Marketplace Works:
- This item is offered by an independent seller and not shipped from our warehouse
- Item details like edition and cover design may differ from our description; see seller's comments before ordering.
- Sellers much confirm and ship within two business days; otherwise, the order will be cancelled and refunded.
- Marketplace purchases cannot be returned to eCampus.com. Contact the seller directly for inquiries; if no response within two days, contact customer service.
- Additional shipping costs apply to Marketplace purchases. Review shipping costs at checkout.
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. |
An electronic version of this book is available through VitalSource.
This book is viewable on PC, Mac, iPhone, iPad, iPod Touch, and most smartphones.
By purchasing, you will be able to view this book online, as well as download it, for the chosen number of days.
Digital License
You are licensing a digital product for a set duration. Durations are set forth in the product description, with "Lifetime" typically meaning five (5) years of online access and permanent download to a supported device. All licenses are non-transferable.
More details can be found here.
A downloadable version of this book is available through the eCampus Reader or compatible Adobe readers.
Applications are available on iOS, Android, PC, Mac, and Windows Mobile platforms.
Please view the compatibility matrix prior to purchase.