9780470562390

Intermediate Structured Finance Modeling, with Website : Leveraging Excel, VBA, Access, and Powerpoint

by ;
  • ISBN13:

    9780470562390

  • ISBN10:

    0470562390

  • Edition: 1st
  • Format: Hardcover
  • Copyright: 2/8/2011
  • Publisher: Wiley
  • Purchase Benefits
  • Free Shipping On Orders Over $59!
    Your order must be $59 or more to qualify for free economy shipping. Bulk sales, PO's, Marketplace items, eBooks and apparel do not qualify for this offer.
  • Get Rewarded for Ordering Your Textbooks! Enroll Now
List Price: $175.00 Save up to $57.40
  • Buy New
    $117.60
    Add to Cart Free Shipping

    USUALLY SHIPS IN 3-4 BUSINESS DAYS

Supplemental Materials

What is included with this book?

  • The New copy of this book will include any supplemental materials advertised. Please check the title of the book to determine if it should include any access cards, study guides, lab manuals, CDs, etc.

Summary

This book leads the reader through a pragmatic, hands-on approach to reaching an intermediate level of sophistication as a financial modeler. After outlining the fundamental concepts of Asset Backed Securitization the book presents a basic Structured Finance model that can be downloaded from the Web site and run in a series of cases. The stage is then set. The book takes this existing model and significantly expands both its scope and sophistication to meet a new business challenge. To accomplish this task the original model is bifurcated along its Asset/Liability lines forming two new models. These models then serve as the twin bases that will be successively developed throughout the remainder of the book.The final form of the two successor models (also available on the Web site), contain extensive interface, analytical and reporting improvements. Widespread implementation of Excel UserForms as menus, well organized cash flow analytics and extensive sets of report packages make these applications of interest to the general modeler as well as the financial specialist. Access is then introduced to streamline various input/output processes of both models. As a finishing touch the models are expanded to produce ready-to-use PowerPoint presentations and incorporate Outlook to automatically deliver the report packages.Extending the analyst's organizational, coding, modeling, and financial skills the book advances in a step-by-step manner to new and more powerful applications. Building on the reader's established knowledge of Excel and VBA you will progressively develop a pair of broader and more sophisticated models. The book contains 6 fully developed models with their supporting data, input, and reporting files as the instructional platform. The book contains over 725 exhibits of reports, examples, and code samples, designed to provide the analyst with a clear, concise, but detailed development plan. The Web site contains 5 additional chapters covering bond math, mortgage math, understanding Excel waterfalls, fundamental securitization principles and how to run the base model. Provides a code intensive approach to improving basic VBA and Excel skills and learning how to integrate them with a real life application. Outlines and implements a series of analytical subroutines to calculate uniform, geographic, and demographic analysis of mortgage cash flows. Also included are extensive stratification reporting and collateral concentration sizing algorithms. Also by William Preinitz: A Fast Track to Structured Finance Modeling, Monitoring and Valuation: Jump Start VBADeveloping Intermediate Financial Models - Leveraging Excel, VBA, Access, PowerPoint and Outlook; a Structured Finance Approach is a straightforward, no-nonsense approach to developing advanced skills as a financial modeler.

Author Biography

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.

Table of Contents

Preface
Goals of the Book
What You Should Know
Setting the Context for Learning
The Structure of the Book
A Fish Story
A Perspective on Modeling
Approaching This Material
A Parting Remark
About the Web Site
First Steps
Introduction
Overview
Why Was This Book Written?
Who Is the Target Audience?
What Is the Purpose of the Book?
Expanding Your Software Skills
Expanding Your Model Design Skills
Expanding Your Finance Knowledge
Organized to Teach
Chapter Organization
The Accompanying Web Site
Learning the "Hard" Way
The Existing Model
Overview
Deliverables
Under Construction
The Crisis du Jour
Overview of the Current Model
Current Model Environment
On the Web Site
Conventions and Advice
Overview
Deliverables
VBA Conventions
Common Sense
On The Web Site
Segregation of the Model's Functionality
Overview
Deliverables
Under Construction
Deliverables Checklist
Breaking Up Is Hard to Do
Accommodating Our Design Needs
Advantages of Functional Segregation
Disadvantages of Functional Segregation
On the Web Site
Creating the Base Asset Model
Overview
Deliverables
Under Construction
The Big Picture: "Just the Assets, Ma'am"
Stepping Through the Model
Testing the Completed Model
On the Web Site
Building the Base Liabilities Model
Overview
Deliverables
Under Construction
Liabilities Side of the Model
What to Leave In
Stepping Through the Model
Reading the Cash Flows and Assumptions from a File
On the Web Site
Establishing the Model Environment
Overview
Deliverables
Under Construction
Importance of a Standardized Directory Structure
Creating Directories and Defining Their Functions
Operating Directories
Administrative Directories
Creating New Directories for the Model as We Need Them
On the Web Site
Building the New Assets Model
Designing the New Collateral Cash Flow Generator
Overview
Deliverables
Under Construction
Improving the CCFG Menus: Conversion to User Forms
Improving the CCFG Data Handling Capabilities
Improving the CCFG Collateral Selection Process
Improving the CCFG Cash Flow Generation Process
Improving the CCFG Report Generation Process
Improving the CCFG Messaging Process
On the Web Site
Writing the CCFG Menus and Data Sheets
Overview
Deliverables
Under Construction
Menus and User Forms
Menus of the CCFG
Main Menu
Run Options Menu
Collateral Pool Menu
Collateral Geographic Selection Criteria Menu
Financial Selection Criteria Menu
Cash Flow Amortization Parameters Menu
Collateral Reports Menu
On The Web Site
Writing the Collateral Data Handling Code
Overview
Deliverables
Under Construction
Managing Multiple Portfolio Files
Initial Data Screening
Writing the Initial Data Screening Reports
Writing the Demographic Methodology Risk Reports
Building a Representative Line Generator Program
Building the Rep Line Generator Program
On the Web Site
Writing the Collateral Selection Code
Overview
Deliverables
Under Construction
Building the Code
Financial and Demographic Selection Code
Financial Collateral Selection Process Reporting
Geographic Selection Code
Geographic Concentration Code
Introduction to Geographic Reporting
On the Web Site
Writing the Collateral Cash Flow Amortization Code
Overview
Deliverables
Under Construction
Quick Review of Existing CF Generation Code
Adding New Mortgage Types
New Prepayment and Default Methodologies
Uniform Methodology
Geographic Methodology
Demographic Methodology
Reporting the Results of the Cash Flow Calculations
Writing the Assumptions Report Package
On The Web Site
Writing the CCGF Reporting Capability
Overview
Deliverables
Under Construction
Recapping the CCFG Report Package
CCFG Activities and Reports
Eligible Collateral Assessment Process
Geographic/Demographic Reports
Presentation Reports
On The Web Site
Building the New Liabilities Model
Designing the Liabilities Waterfall Model
Overview
Deliverables
Under Construction
Structure of the Deal: Sources and Uses of Funds
Design Elements of the LWM
Main Menu
Report Package Menu User Form
Structure Inputs Menu
Liabilities Waterfall Worksheet
Performance Summary Page
VBA Code Modules
Report Template Files
On the Website
Writing the Liabilities Waterfall Model
Overview
Deliverables
Under Construction
Deal Structure Inputs
Building the Liabilities Waterfall Model
Period Factors Worksheet
Total Cashflows Sections
Results Page Worksheet
On the Web Site
Writing the VBA Code of the Liabilities Waterfall Model
Overview
Deliverables
Under Construction
VBA Requirements of the LWM
Menus of the LWM
Main Program
Error Checking the Main Menu Inputs
Importing the Liability Structure Inputs
Importing the Cash Flows of the CCFG
Loading and Running the LWM
Reporting the Results
Other Functionality
On The Web Site
Access, PowerPoint, and Outlook
Access: An Introduction
Overview
Deliverables
Under Construction
Basics of Access
Opening Access and Creating a Database
Access Tables
Getting the Data In: Entering Data
Getting the Data Back Out: Queries
Basics of Structured Query Language
Concluding Remarks
On The Web Site
Implementing Access in the CCFG and LWM
Overview
Deliverables
Under Construction
Access and the Collateral Cash Flow Generator
Collateral Portfolio Data
Geographic Selection Criteria
Geographic Concentration Criteria
Initial Data Screening Criteria
Custom Financial Selection Criteria
Run Options Elections
Generating the Loan Portfolio
Monthly Cash Flows Scenarios
Access in CCFG Reporting
Access and the Liabilities Waterfall Model
Connection Subroutines
Structure Worksheet Objects
Retrieving the Cash Flows
Storing LWM Runs in the Database
Linked Excel Tables
LWM Data to Access DB
Concluding Remarks
On The Web Site
(to come)
(to come)
(to come)
Exhibit Index
Index
Table of Contents provided by Publisher. All Rights Reserved.

Rewards Program

Write a Review