Structured Finance Modeling with Object-Oriented VBA

by Evan Tick

• ISBN13: 9780470130414
• ISBN10: 0470130415
• Format: eBook
• Copyright: 2007-07-01
• Publisher: Wiley

Summary

A detailed look at how object-oriented VBA should be used to model complex financial structures This guide helps readers overcome the difficult task of modeling complex financial structures and bridges the gap between professional C++/Java programmers writing production models and front-office analysts building Excel spreadsheet models. It reveals how to model financial structures using object-oriented VBA in an Excel environment, allowing desk-based analysts to quickly produce flexible and robust models. Filled with in-depth insight and expert advice, it skillfully illustrates the art of object-oriented programming for the explicit purpose of modeling structured products. Residential mortgage securitization is used as a unifying example throughout the text.

Table of Contents

Preface

List of Acronyms

Acknowledgments

About the Author

Cash-Flow Structures

Getting Started

Securitization

Synthetic Structures

Putting It All Together

Modeling

Dipping a Toe in the Shallow End

Swimming Toward the Deep End

Types

Class Architecture

Weak Inheritance

Parameterized Class

Which Is Better?

Exercises

Assets

Replines

Portfolio Optimization

Zero-One Program

Simulated Annealing

Losses, Prepayments, and Interest Rates

Cash-Flow Model

Zero-Prepay Cash Flows

Actual Cash Flows

Examples

S&P Cash-Flow Model

Model Parameters

Moody's Cash-Flow Model

Model Parameters

Algorithm

Option ARMs

Class Architecture: Multiple Inheritance

Doing It in Excel: SumProduct

Exercises

Liabilities

Getting Started

Notation

Expenses

Interest

Over-collateralization

Current Subordinated Amount

Stepdown Date

Target Subordinated Amount

Principal

Gross Principal Distributions

Detailed Principal Distributions

Writedowns and Recoveries

Derivatives

Corridors

Swaps

Excess Reserve Fund Account

Triggers

Call Features

Overcollateralization Test

Interest Coverage Test

Delinquency Trigger

Loss Trigger

Residuals: NIMs and Post-NIM

Class Architecture

Passive Approach

Active Approach

Comparison

Doing It in Excel: Data Tables

Exercises

Sizing the Structure

Senior Sizing

Subordinate Sizing

Fully Funded vs. Non-Fully Funded

Optimizations and Complexity

Example of Sizing

NIM and OTE Sizing

Class Architecture

Inheritance Revisited

Odds and Ends

Doing It in Excel: Solver

Exercises

Analysis

Risk Factors

Prefunding

Prepayments

Buybacks and Cleanup Calls

Defaults

Interest Rates

Spreads

Miscellaneous

Residual Sensitivities

Mezzanine and Subordinate Classes

NIM Classes

Putting It All Together

Exercises

Stochastic Models

Static versus Stochastic

Loss Model

Probability of Default from Transition Matrix

Probability of Default from Spread

Probability of Time to Default

Gaussian Copula

Monte Carlo Simulation

Synthetic Credit Indexes

Loss Lets

Analysis

Hedging

Doing It in Excel

Exercises

Excel and VBA

Spreadsheet Style

Code Style

Compilation

Bloomberg

Bond Math

Mortgage Payment

Yield to Price

Price to Yield

Duration

Index or Interest-Rate Duration

Discount Spread Duration

Hazard Rate

Static Credit Card Model

References

Index

Table of Contents provided by Publisher. All Rights Reserved.