Details

Intermediate Structured Finance Modeling

Leveraging Excel, VBA, Access, and Powerpoint
Wiley Finance, Band 573 1. Aufl.

von: William Preinitz, Matthew Niedermaier
112,99 €
Verlag:	Wiley
Format:	PDF
Veröffentl.:	03.05.2016
ISBN/EAN:	9780470928752
Sprache:	englisch
Anzahl Seiten:	1032

In den Warenkorb

Als Gutschein

DRM-geschütztes eBook, Sie benötigen z.B. Adobe Digital Editions und eine Adobe ID zum Lesen.

Beschreibungen

Titelbeschreibung

This book provides a pragmatic, hands-on approach to reaching an intermediate level of sophistication as a financial modeler. Expanding on the first book, A Fast Tract to Structured Finance Modeling, Monitoring, and Valuation, the book will guide you step-by-step through using learned principals in new and more powerful applications. These applications will build on the knowledge of Excel and VBA gained, expand the use of Access for data management tasks, as well as PowerPoint and Outlook for reporting and presentation tasks.

Inhaltsverzeichnis

Preface xv Goals of the Book xv What You Should Know xvi Setting the Context for Learning xviii The Structure of the Book xix A Fish Story xxiii A Perspective on Modeling xxiii Approaching This Material xxiv Style xxv A Parting Remark xxv On the Web Site xxvii Acknowledgments xxxv PART 1 First Steps 1 CHAPTER 1 Introduction 5 Overview 5 Why Was This Book Written? 5 Who Is the Target Audience? 9 What Is the Purpose of the Book? 10 Expanding Your Software Skills 10 Expanding Your Model Design Skills 12 Expanding Your Finance Knowledge 12 Organized to Teach 14 Chapter Organization 15 Accompanying Web Site 17 Learning the “Hard” Way 18 Note 19 CHAPTER 2 The Existing Model 21 Overview 21 Deliverables 22 Under Construction 23 Crisis du Jour 23 Overview of the Current Model 24 Current Model Environment 51 On the Web Site 51 CHAPTER 3 Conventions and Advice 55 Overview 55 Deliverables 56 VBA Conventions 56 Common Sense 70 On the Web Site 72 CHAPTER 4 Segregation of the Existing Model’s Functionality 73 Overview 73 Deliverables 73 Under Construction 73 Deliverables Checklist 74 Breaking Up Is Hard to Do 74 Accommodating Our Design Needs 76 Advantages of Functional Segregation 76 Disadvantages of Functional Segregation 79 On the Web Site 80 CHAPTER 5 Creating the Base Asset Model 81 Overview 81 Deliverables 81 Under Construction 82 The Big Picture: “Just the Assets, Ma’am” 82 Stepping through the Model 84 Testing the Completed Base Asset Model 119 On the Web Site 121 CHAPTER 6 Building the Base Liabilities Model 123 Overview 123 Deliverables 123 Under Construction 124 Liabilities Side of the Model 125 What to Leave In 126 Stepping through the Model 126 Reading the Cash Flows and Assumptions from a File 151 Testing the Completed Base Liabilities Model 162 On the Web Site 164 CHAPTER 7 Establishing the Model Environment 167 Overview 167 Deliverables 168 Under Construction 168 Importance of a Standardized Directory Structure 168 Creating Directories and Defining Their Functions 168 Operating Directories 170 Administrative Directories 175 Creating New Directories for the Model as We Need Them 176 On the Web Site 177 PART 2 Building the New Assets Model 179 CHAPTER 8 Designing the New Collateral Cash Flow Generator 181 Overview 181 Deliverables 183 Under Construction 183 Improving the CCFG Menus: Conversion to UserForms 183 Improving the CCFG Data-Handling Capabilities 199 Improving the CCFG Collateral Selection Process 209 Improving the CCFG Cash Flow Generation Process 212 Improving the CCFG Report Generation Process 215 Improving the CCFG Messaging Process 220 On the Web Site 220 CHAPTER 9 Writing the CCFG Menus and Data Sheets 221 Overview 221 Deliverables 221 Under Construction 222 Menus and UserForms 222 Menus of the CCFG 241 Main Menu 242 Run Options Menu 243 Collateral Pool Menu 255 Collateral Geographic Selection Criteria Menu 259 Financial Selection Criteria Menu 288 Cash Flow Amortization Parameters Menu 299 Collateral Reports Menu 303 On the Web Site 316 CHAPTER 10 Writing the Collateral Data Handling Code 317 Overview 317 Deliverables 318 Under Construction 318 Managing Multiple Portfolio Files 320 Initial Data Screening 335 Writing the Screening Process VBA Code 343 Writing the Initial Data Screening Reports 351 Writing the Demographic Methodology Risk Reports 360 Building a Representative Line Generator Program 365 Building the Rep Line Generator Program 369 On the Web Site 387 CHAPTER 11 Writing the Collateral Selection Code 389 Overview 389 Deliverables 390 Under Construction 390 Building the Code 391 Financial and Demographic Selection Code 392 Financial Collateral Selection Process Reporting 398 Geographic Selection Code 416 Geographic Concentration Code 419 Introduction to Geographic Reporting 433 On the Web Site 458 CHAPTER 12 Writing the Collateral Cash Flow Amortization Code 459 Overview 459 Deliverables 459 Under Construction 460 Quick Review of Existing CF Generation Code 461 Adding New Mortgage Types 462 New Prepayment and Default Methodologies 473 Uniform Methodology 474 Geographic Methodology 478 Demographic Methodology 493 Reporting the Results of the Cash Flow Calculations 503 Writing the Assumptions Report Package 511 On the Web Site 518 CHAPTER 13 Writing the CCFG Reporting Capability 519 Overview 519 Deliverables 520 Under Construction 520 Recapping the CCFG Report Package 521 CCFG Activities and Reports 522 Eligible Collateral Assessment Process 524 Geographic/Demographic Reports 555 Presentation Reports 560 PART 3 Building the New Liabilities Model 583 CHAPTER 14 Designing the Liabilities Waterfall Model 585 Overview 585 Deliverables 585 Under Construction 585 Structure of the Deal: Sources and Uses of Funds 586 Design Elements of the LWM 589 Main Menu 590 Report Package Menu UserForm 590 Structure Inputs Menu 591 Liabilities Waterfall Worksheet 592 Performance Summary Page 600 VBA Code Modules 600 Report Template Files 604 On the Web Site 616 CHAPTER 15 Writing the Liabilities Waterfall Model Spreadsheet 617 Overview 617 Deliverables 617 Under Construction 618 Deal Structure Inputs 618 Building the Liabilities Waterfall Model 623 Period Factors Worksheet 650 Total Cash Flows Section 651 Results Page Worksheet 651 On the Web Site 657 CHAPTER 16 Writing the LWM VBA Code 659 Overview 659 Deliverables 660 Under Construction 660 VBA Requirements of the LWM 665 Menus of the LWM 666 Main Program 679 Error Checking the Main Menu Inputs 681 Importing the Liability Structure Inputs 682 Importing the Cash Flows of the CCFG 686 Loading and Running the LWM 693 Reporting the Results 696 Other Functionality 718 PART 4 Access, PowerPoint, and Outlook 721 CHAPTER 17 Access: An Introduction 723 Overview 723 Deliverables 724 Under Construction 724 Basics of Access 724 Opening Access and Creating a Database 726 Access Tables 728 Getting the Data In: Entering Data 731 Getting the Data Back Out: Queries 740 Basics of Structured Query Language 754 Concluding Remarks 762 On the Web Site 762 CHAPTER 18 Implementing Access in the CCFG and LWM 763 Overview 763 Deliverables 764 Under Construction 764 Access and the Collateral Cash Flow Generator 766 Collateral Portfolio Data 767 Geographic Selection Criteria 781 Geographic Concentration Criteria 794 Initial Data Screening Criteria 796 Custom Financial Selection Criteria 805 Run Options Elections 815 Generating the Loan Portfolio 818 Monthly Cash Flows Scenarios 822 Access in CCFG Reporting 825 Access and the Liabilities Waterfall Model 827 Connecting the LWM to the CCFG 827 Structure Worksheet Objects 828 Retrieving the Monthly Cash Flow Scenarios 831 Storing LWM Run Results in the Database 832 Concluding Remarks 843 On the Web Site 843 CHAPTER 19 Implementing PowerPoint and Outlook in the CCFG 845 Overview 845 Deliverables 845 Under Construction 846 Using PowerPoint in the CCFG 846 Data Requirements 851 Adding a New Class of Reports 856 Introduction to PowerPoint in VBA 860 Preparing the Data for Use 867 First Time Homeowners Slide 869 Creating the Null Report 874 MS Outlook 876 On the Web Site 882 PART 5 Running the CCFG and the LWM 885 CHAPTER 20 Running the Models 887 Overview 887 Deliverables 887 Under Construction 888 Running the CCFG, Analyzing the Asset Side of the Deal 888 Liabilities Side of the Deal 923 Preparing a PowerPoint Presentation about Geographic Concentration 933 On the Web Site 935 Afterword 941 Exhibits Index 943 Subject Index 975

Autorenportrait

WILLIAM PREINITZ has thirty years' experience in investment banking and structured finance analytics. A former managing director at Citigroup, highlights of his career include the creation and marketing of securitizations backed by 12b-1 mutual fund fees. With a team of ten people over a fifteen-year period, his group concurrently provided analytical structuring support for the over 100 different asset classes contained in the ABCP program of the bank. These included such varied items as movie revenues, fast-food franchise financing, and communication satellite leasing. In conjunction with the bank's corporate training program, he taught Excel/VBA modeling classes to over 300 people ranging from associates to managing directors. MATTHEW NIEDERMAIER is currently a vice president in the Structured Finance Group at Markit, where he is a member of a pricing and valuation group focused on agency mortgages and CMOs. Within Markit he has also taught courses in Excel/VBA globally. Prior to Markit he modeled U.S. and Latin American ABS deals at Citigroup, including auto loans, credit cards,and timeshares.

Back cover copy

A PRAGMATIC, HANDS-ON APPROACH TO REACHING AN INTERMEDIATE LEVEL OF SOPHISTICATION AS A FINANCIAL MODELER Intermediate Structured Finance Modeling is packed with insight and tips to teach you step by step to expand your current level of organizational, coding, modeling, and financial skills to enhance and develop new and more powerful applications. It expertly employs a code-intensive approach to improve your VBA and Excel skills and demonstrates how to integrate them with a real-life application. Providing you with a set of immediately applicable modeling approaches, techniques, skills, and examples with a focus on large model development, you will learn how to: <ul> <li>Organize and build a set of directories to contain the model system you will develop</li> <li>Separate an existing basic structured finance model into two special-purpose models, an Asset (collateral) model and a Liabilities Structure (bonds) model</li> <li>Expand the computational, data management, and reporting capabilities of both new models</li> <li>Employ Excel UserForms to build a sophisticated model interfaces</li> <li>Introduce Access databases and code to manage model data requirements and outputs</li> <li>Dramatically expand existing reporting packages, producing more sophisticated analytical results</li> <li>Automate the production of ready-to-use PowerPoint presentations</li> <li>Improve your model's ability to distribute its results by integrating Outlook mailing lists</li> </ul> If you have some modeling experience and are confident in your mastery of the fundamental features of VBA and Excel, Intermediate Structured Finance Modeling is the book for you. With working solutions to the challenges of developing larger-scale models, you will learn successful intermediate modeling techniques that can be applied to any real-world situation and activity. This book is the intermediate-level companion volume to A Fast-Track to Structured Finance Modeling, Monitoring, and Valuation: Jump Start VBA, an introductory work on the subject.