Master your Minecraft
 
 

Special Offers see all

Enter to WIN a $100 Credit

Subscribe to PowellsBooks.news
for a chance to win.
Privacy Policy

Tour our stores


    Recently Viewed clear list


    What I'm Giving | December 2, 2014

    Cary Elwes: IMG Cary Elwes: What I'm Giving



    At Powell's, we feel the holidays are the perfect time to share our love of books with those close to us. For this special blog series, we reached... Continue »

    spacer
Qualifying orders ship free.
$85.00
New Trade Paper
Ships in 1 to 3 days
Add to Wishlist
available for shipping or prepaid pickup only
Available for In-store Pickup
in 7 to 12 days
Qty Store Section
25 Remote Warehouse Business- Accounting and Finance

Other titles in the Wiley Finance series:

Wiley Finance #370: Modeling Structured Finance Cash Flows with Microsoft Excel: A Step-By-Step Guide

by

Wiley Finance #370: Modeling Structured Finance Cash Flows with Microsoft Excel: A Step-By-Step Guide Cover

 

Synopses & Reviews

Publisher Comments:

Praise for Modeling Structured Finance Cash Flows with Microsoft® Excel®

"You can roll up your sleeves, dive into this book, and shortly thereafter, you have a full-blown honest-to- goodness working model of a transaction. This sure beats the old method of slaving at the feet of gurus for years in the hopes that they'll throw you a scrap. It almost feels as though Allman has given away a shortcut through the 'hazing' period of learning, but then I am grateful I have a resource with which to train new analysts or remind myself how some things work."

—Omar Haneef, Global Securitized Markets, Citigroup

"Allman has written a book that reveals to the reader/user a robust framework for using Excel to model structured finance transactions. Both seasoned cash flow modelers and those new to, or curious about, how structured finance transactions are analyzed can benefit from this book."

—Henry Wilson, Bond Insurance Company Analyst

"A powerful, concise, and tiered approach. It's a bedrock foundation to build on for both the art and science of structured finance modeling. An excellent platform from which to leverage your financial knowledge and personal capital."

—William Preinitz, Managing Director, Citigroup

"An essential tool for any credit officer wishing to gain a general or in-depth understanding of cash flow modeling for new or existing deals."

—Joseph B. Keener, Structured Portfolio Management, Citigroup London

Written by industry expert Keith Allman, this well-rounded resource provides detailed guidance on constructing one of today's most adaptable models—the cash flow model. Filled with in-depth insight, expert advice, and illustrative examples, Modeling Structured Finance Cash Flows with Microsoft Excel explains individual functions and formulas, while also examining 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.

By combining specific application instruction with theory, Modeling Structured Finance Cash Flows with Microsoft Excel will teach you skills that can be instantly applied to professional-level modeling.

Synopsis:

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.

About the Author

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

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 40

OFFSET 40

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

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

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

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

Product Details

ISBN:
9780470042908
Author:
Allman, Keith A.
Publisher:
John Wiley & Sons
Author:
Allman, Keith
Subject:
Finance
Subject:
Corporate Finance
Subject:
Corporations
Subject:
Corporations -- Finance -- Mathematical models.
Subject:
Cash flow -- Mathematical models.
Subject:
Institutional & Corporate Finance
Subject:
Business-Accounting and Finance
Copyright:
Edition Description:
WebSite Associated w/Book
Series:
Wiley Finance
Series Volume:
370
Publication Date:
20070427
Binding:
TRADE PAPER
Grade Level:
General/trade
Language:
English
Illustrations:
Y
Pages:
224
Dimensions:
9.19x7.57x.47 in. .90 lbs.

Related Subjects

Business » Accounting and Finance
Computers and Internet » Personal Computers » Applications
Health and Self-Help » Psychology » General

Wiley Finance #370: Modeling Structured Finance Cash Flows with Microsoft Excel: A Step-By-Step Guide New Trade Paper
0 stars - 0 reviews
$85.00 In Stock
Product details 224 pages John Wiley & Sons - English 9780470042908 Reviews:
"Synopsis" by , 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.

spacer
spacer
  • back to top

FOLLOW US ON...

     
Powell's City of Books is an independent bookstore in Portland, Oregon, that fills a whole city block with more than a million new, used, and out of print books. Shop those shelves — plus literally millions more books, DVDs, and gifts — here at Powells.com.