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

by Preinitz, William; Niedermaier, Matthew

• ISBN13: 9780470562390
• ISBN10: 0470562390
• Edition: 1st
• Format: Hardcover
• Copyright: 2011-02-08
• Publisher: Wiley

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.