Financial Simulation Modeling in Excel E-Book

Keith A. Allman

• Herausgeber: John Wiley & Sons
• Kategorie: Fachliteratur
• Serie: Wiley Finance Editions
• Sprache: Englisch

Seitenzahl: 326

Veröffentlichungsjahr: 2011

Beschreibung

"I've worked with simulation in business for over 20 years, and Allman really nails it with this book. I admit that I own his previous book on structured finance cash flows, but I was surprised by what I found in here. He addresses the fundamental questions of how decision makers react to simulations and his read was very much in accordance with what I've experienced myself. When it came to the nuts and bolts of describing the different types of simulation analysis the book becomes incredibly detailed. There is working code and models for a fantastic array of the most common simulation problems. If you're so inclined, the book very carefully steps through the tricky math needed to really understand the theory behind stochastic modeling in finance. If you're preparing models that include any kind of randomization or stochastic modeling component, this book is a must-read, a tremendous value and time-saver." -- David Brode of The Brode Group A practical guide to understanding and implementing financial simulation modeling As simulation techniques become more popular among the financial community and a variety of sub-industries, a thorough understanding of theory and implementation is critical for practitioners involved in portfolio management, risk management, pricing, and capital budgeting. Financial Simulation Modeling in Excel contains the information you need to make the most informed decisions possible in your professional endeavors. Financial Simulation Modeling in Excel contains a practical, hands-on approach to learning complex financial simulation methodologies using Excel and VBA as a medium. Crafted in an easy to understand format, this book is suitable for anyone with a basic understanding of finance and Excel. Filled with in-depth insights and expert advice, each chapter takes you through the theory behind a simulation topic and the implementation of that same topic in Excel/VBA in a step-by-step manner. * Organized in an easy-to-follow fashion, this guide effectively walks you through the process of creating and implementing risk models in Excel * A companion website contains all the Excel models risk experts and quantitative analysts need to practice and confirm their results as they progress * Keith Allman is the author of other successful modeling books, including Corporate Valuation Modeling and Modeling Structured Finance Cash Flows with Microsoft Excel Created for those with some background in finance and experience in Excel, this reliable resource shows you how to effectively perform sound financial simulation modeling, even if you've yet to do extensive modeling up to this point in your professional or academic career.

Leseprobe

Contents

Cover

Series

Title Page

Copyright

Preface

Acknowledgments

About the Authors

Chapter 1: Introduction

WHAT IS SIMULATION?

CHARACTERISTICS OF A SIMULATION

INSTRUCTIONAL METHODOLOGY

HOW THIS BOOK WORKS

ABOUT THE COMPANION WEBSITE

EXCEL 2003 AND EARLIER VERSUS EXCEL 2007/2010

A FEW WORDS ABOUT SEMANTICS

FINAL REMINDERS

Chapter 2: Random Numbers, Distributions, and Basic Simulation Setup

SEED VARIABLES AND RANDOM NUMBER GENERATION

DISTRIBUTIONS

NORMAL PSEUDORANDOM NUMBERS

QUICKLY GENERATING NORMAL PSEUDORANDOM NUMBERS USING PREBUILT EXCEL FUNCTIONS

OTHER METHODS OF GENERATING NORMAL PSEUDORANDOM NUMBERS

PUTTING TOGETHER A MORE DEVELOPED SIMULATION USING THE FUNDAMENTAL COMPONENTS

BROWNIAN MOTION AND WIENER PROCESSES

UNDERSTANDING SIMULATION RESULTS

THIS IS JUST THE BEGINNING

Chapter 3: Correlation

THE BASIC IDEA OF CORRELATION

CORRELATION IN A FINANCIAL CONTEXT

PRODUCING SETS OF CORRELATED NORMAL RANDOM NUMBERS USING MATRIX MATHEMATICS

GOING FORWARD USING OUR TOOLS

Chapter 4: Option Pricing

BINOMIAL TREES

HULL-WHITE INTEREST RATE MODEL

HULL-WHITE TRINOMIAL TREE

TERM STRUCTURE RECOVERY USING FORWARD INDUCTION

BLACK-SCHOLES OPTION PRICING METHOD

DISCUSSION ON ERRORS

BEYOND PRICE MOVEMENTS

Chapter 5: Corporate Default Simulation

THE THEORY BEHIND THE MERTON MODEL

SHORT-TERM AND LONG-TERM LIABILITIES: THE BARRIER AND CALIBRATION

TUNING THE MODEL—DATA SETS AND DRIFT

OTHER METHODS TO DEVELOP DEFAULT PROBABILITIES

DETERMINING LOSS PROBABILITIES FROM BOND PRICES OR CREDIT DEFAULT SWAPS

BOND TERM STRUCTURE AND BOOTSTRAPPING

RECOVERY ASSUMPTIONS

SOVEREIGNS AND MUNICIPALS: NONCORPORATE ISSUERS

FROM ASSETS TO POOLS

Chapter 6: Simulating Pools of Assets

DIFFERENCES BETWEEN PROJECTING DEFAULTS AND PRICE MOVEMENTS

VALUE AT RISK (VAR)

STRUCTURED PRODUCTS AND CDOS

TRANSITION MATRICES

KNOWING YOUR ASSETS

Chapter 7: Dealing with Data Deficiencies and Other Issues

“GARBAGE IN, GARBAGE OUT”

INCONSISTENT DATA FORMATS

LACK OF DATA

Chapter 8: Advanced Topics and Further Reading

VBA AND OTHER LANGUAGES: DIFFERENT PROGRAMS FOR DIFFERENT USES

QUASI–MONTE CARLO AND COMPUTATIONAL TIME

EFFICIENT MARKET HYPOTHESIS AND ITS WEAKNESSES

DISTRIBUTIONS: NASSIM TALEB AND NORMALCY

NEW FRONTIERS OF ECONOPHYSICS

WORKING WITH IMPERFECT MODELS

Appendix A: Partial Differential Equations

PARTIAL DERIVATIVES

ORDINARY DIFFERENTIAL EQUATIONS (ODE)

BOUNDARY CONDITIONS

PARTIAL DIFFERENTIAL EQUATIONS

STOCHASTIC DIFFERENTIAL EQUATIONS

Appendix B: Newton-Raphson Method

References

Further Reading

Index

Founded in 1807, John Wiley & Sons is the oldest independent publishing company in the United States. With offices in North America, Europe, Australia, and Asia, Wiley is globally committed to developing and marketing print and electronic products and services for our customers’ professional and personal knowledge and understanding.

The Wiley Finance series contains books written specifically for finance and investment professionals as well as sophisticated individual investors and their financial advisors. Book topics range from portfolio management to e-commerce, risk management, financial engineering, valuation, and financial instrument analysis, as well as much more.

For a list of available titles, visit our website at www.WileyFinance.com.

Copyright © 2011 by Keith Allman, Joshua Laurito, Michael Loh. All rights reserved.

Published by John Wiley & Sons, Inc., Hoboken, New Jersey. Published simultaneously in Canada.

No part of this publication may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning, or otherwise, except as permitted under Section 107 or 108 of the 1976 United States Copyright Act, without either the prior written permission of the Publisher, or authorization through payment of the appropriate per-copy fee to the Copyright Clearance Center, Inc., 222 Rosewood Drive, Danvers, MA 01923, (978) 750–8400, fax [phone], or on the Web at www.copyright.com. Requests to the Publisher for permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, (201) 748–6011, fax [phone], or online at www.wiley.com/go/permissions.

Limit of Liability/Disclaimer of Warranty: While the publisher and author have used their best efforts in preparing this book, they make no representations or warranties with respect to the accuracy or completeness of the contents of this book and specifically disclaim any implied warranties of merchantability or fitness for a particular purpose. No warranty may be created or extended by sales representatives or written sales materials. The advice and strategies contained herein may not be suitable for your situation. You should consult with a professional where appropriate. Neither the publisher nor author shall be liable for any loss of profit or any other commercial damages, including but not limited to special, incidental, consequential, or other damages.

Designations used by companies to distinguish their products are often claimed as trademarks. In all instances where John Wiley & Sons, Inc. is aware of a claim, the product names appear in initial capital or all capital letters. Readers, however, should contact the appropriate companies for more complete information regarding trademarks and registration.

Microsoft and Excel are registered trademarks of Microsoft Corporation.

For general information on our other products and services or for technical support, please contact our Customer Care Department within the United States at (800) 762–2974, outside the United States at (317) 572–3993 or fax [phone].

Wiley also publishes its books in a variety of electronic formats. Some content that appears in print may not be available in electronic books. For more information about Wiley products, visit our website at www.wiley.com.

Library of Congress Cataloging-in-Publication Data:

Allman, Keith A., 1977– Financial simulation modeling in Excel : a step-by-step guide / Keith Allman, Josh Laurito, and Michael Loh. p. cm. – (Wiley finance series; 18) Includes bibliographical references and index. ISBN 978-0-470-93122-6 (pbk); ISBN 978-1-118-13720-8 (ebk); ISBN 978-1-118-13721-5 (ebk); ISBN 978-1-118-13722-2 (ebk) 1. Finance–Mathematical models–Computer programs.2. Microsoft Excel (Computer file) I. Laurito, Josh, 1981–II. Loh, Michael, 1977–III. Title. HG173A437 2011 332.0285′554–dc23 2011017551

Preface

Regardless of where I work, simulation has crept into my financial career. After nearly a decade of working with it in many capacities I've found it to be a mixed blessing. In many investment companies when the term simulation is simply brought up there are a variety of reactions. The two most visible camps of thought seem to be the utilizers, who think the results of a simulation have value and the skeptics, who think simulation overcomplicates analyses.

The utilizers believe that when a concept or instrument is researched correctly, information parsed and calculated properly, and a simulation constructed in a statistically correct manner, the results can be used to make decisions. I tend to fall into this camp, with a few caveats I will mention later, because I have seen its utility in a variety of settings. Infrastructure deals that I saw early in my career that involved vehicular traffic, trade, or passenger flows, made more sense through simulation results given the wide variety of scenarios that could play out over time. A commodity company investment that I worked on at Citigroup involving soybeans seemed more appropriate after seeing the historic volatility of soybean prices and how their expected evolution might affect our exposure. In my structured finance career, the value of simulation on a very granular level for distressed mortgage-backed securities provided insight into obligor delinquency, default, and eventually expected security value loss. More recently, as I moved into private equity, simulating pools of corporate exposures and fund performance has become an important tool in assessing portfolio risk.

With all of these positives, there are some valid criticisms of simulation that are espoused by the skeptics. Relating to the overcomplication arguments is the thought that simulation is complex and that many mistakes can be made. I agree with this criticism, and one of the caveats that I alluded to earlier is that simulation must be implemented correctly for it to be useful and productive. I have seen simulations fail for a number of reasons, but most relate to poor implementation. In one transaction that I saw taken to a credit committee, the simulation implemented was purely derived from Excel's random number generator creating numbers based on a uniform distribution. No analysis was done around the appropriate distribution, and the CEO, who had an actuary background, instantly criticized the presentation.

In another transaction at an investment bank, a transaction specialist asked me to use a third-party simulation program to assist in modeling a structured product. I used the model exactly as it was intended and provided the results to the specialist. I knew that the time frame for the transaction was limited, but I was surprised that later in the day the specialist was preparing the results to use for the investment committee. The results that he had were a simulation of the asset side only and had no bearing on the liability structure being implemented. Trying to use such results in the manner he intended would have been erroneous. Luckily, the problem was caught in time and the proper analysis later done.

Even worse are systemic failures of simulation that we have recently seen. Before the 2007/2008 global financial crisis, the market assumed a lower correlation level for mortgage-backed securities than was actually intrinsic to the system. Simulations were run, and securities were poorly sized against default partly relating to this correlation underestimation. As the crisis evolved, the correlations were rerun and noticeably higher, meaning that the securities structured via simulations using lower correlations were much riskier than originally thought.

The intent of exposing my negative experiences with simulation is by no means to dissuade readers from using it and therefore throwing into question what the many pages that follow this preface could possibly be about. The purpose is to show that many of the problems related to financial simulation are caused by improper construction, use, or interpretation. Historical data that provides probabilities, volatility, or correlations might not be scrubbed and analyzed correctly, the implementation of simulation methods might be against the wrong distribution or structurally incorrect, and interpretation of results could be construed to arrive at fallacious conclusions.

The problems seem surmountable when enough time is taken to use simulation correctly. To be able to do this in a financial context, many people encounter difficulties because the bulk of the texts that explain simulation methodologies are extremely dense and theoretical. Few try to distill the important concepts into a readily accessible format with meaningful and practical examples. Like the other books in my step-by-step series, this book attempts to bridge the gap between basic technical implementation and purely theoretical explanations.

A noticeable difference with this book compared to my others is the appearance of two other names on the cover: Michael Loh and Josh Laurito. Simulation is a highly complex topic, and to thoroughly dig into the details their unique experiences and abilities were absolutely necessary. Michael's technical background in physics and finance brings a high mathematical acumen, which is reflected in the most difficult Model Builders seen on the website and throughout many sections of the text. Josh has deep industry experience and firsthand experience using simulation in a variety of contexts on Wall Street. Frequently we will use the terms “I” and “we” throughout the book. In both cases we are referring to all three of us from a collective perspective.

It's my belief that the combination of our skills and experience has been conveyed in an approachable, unintimidating, and clear manner. I hope that the pedagogical approach allows readers to walk away with a new tool in their analytical skill set and a feeling of personal value addition. If readers feel that something is still not clear or that they have found a possible typo or error, I encourage them to check the book's website for errata or to contact me personally at [email protected].

Keith Allman

Acknowledgments

I can definitively state that this book would not have been possible without my coauthors, Michael Loh and Josh Laurito. At times it was difficult, but both persisted through complex Excel/VBA work, tedious explanations, and long nights writing. Thank you, Mike and Josh. I must also thank the staff at John Wiley & Sons once again for allowing me the opportunity to add to the body of financial knowledge that the Wiley Finance series offers. Specifically, Bill Falloon, Jennifer MacDonald, and Tiffany Charbonier were critical to this book printing.

—Keith Allman

I would like to thank Keith Allman for providing me with this opportunity to help him write this book. Working with my coauthors on this project has been an amazing and enlightening experience for me. I am grateful, especially to Josh Laurito, for the patience my coauthors have shown me and for all the fantastic work that they have put into this book. I would also like to thank our publisher, John Wiley & Sons, because without their support none of this would have been possible.

—Michael Loh

Writing a book under almost any circumstances is a time-consuming endeavor. But writing one with three authors, on two continents, across eight time zones involves an extraordinary amount of dedication and patience. Mike and Keith were fantastic through the entire process, and I want to thank them for all the time and expertise they devoted to putting together the best text possible. I also want to thank the people at Wiley for their guidance and openness through the process of writing this book. In addition, special thanks to my partners, Gregg and Tim, as well as the whole team at Lumesis: Abdullah, Alex, Chong, Jacob, Justin, Lev, and Louis, for supporting me and our vision despite the hours this project took away from our venture. Most importantly, I want to thank my family and friends for their encouragement and patience as I disappeared for weeks at a time to work through what seemed like an endless project. Mom, Dad, Aaron, Becca, Jess, Shruti, and everyone else: thank you so much for your love and support.

—Josh Laurito

About the Authors

KEITH ALLMAN is an investment manager at Bamboo Finance, a private equity fund that invests in for-profit, commercially viable companies that provide a good or service that beneficially impacts the lives of low-income individuals. Mr. Allman is primarily responsible for generating new investment opportunities and managing existing investments. In addition, he manages the risk-monitoring process of the portfolio. Previously, Mr. Allman was the director of analytics and modeling at Pearl Street Capital Group, where he focused on private equity fund of funds, capital relief transactions, and venture debt funds. He also founded Enstruct, which services clients worldwide in capital markets and equity valuation, distressed valuation, and quantitative-based training. His analytical training originated at Citigroup, where he modeled structured products for their conduits and eventually emerging market transactions for its Principal Finance group. He has published three books with John Wiley & Sons, including Modeling Structured Finance Cash Flows in Excel: A Step-by-Step Guide, Reverse Engineering Deals on Wall Street: A Step-by-Step Guide, and Corporate Valuation Modeling: A Step-by-Step Guide. He is also an active volunteer for Relief International, for which he provided on-the-ground training for credit analysts at microfinance institutions in the Middle East. He is currently a director on Relief International's board. He holds bachelor's degrees from UCLA and a master's degree from Columbia University.

JOSHUA LAURITO, CFA, is a cofounder and principal of Lumesis, a leading provider of credit-analysis software and solutions to the municipal finance market. He also heads the analytics and data science divisions at CrowdTwist. Previously, he directed corporate modeling for Hexagon Securities, a boutique merchant bank that advises and invests in banks and specialty finance companies. Mr. Laurito held a managing directorship at RangeMark Financial Services, a credit-risk management and capital-markets firm specializing in structured finance. At RangeMark, he headed analysis of structured-asset resecuritizations and esoteric interest rate products, as well as municipal and financial institution credits and derivatives. He started his career in finance as part of the Global Structured Credit Products group at Merrill Lynch, where he assisted in the underwriting and modeling of securities backed by corporate and structured credits. Mr. Laurito is a CFA charterholder and holds a degree in chemistry and mathematics from Columbia University.

MICHAEL LOH is a software developer at Tech-X Corporation in Boulder, Colorado. He is working on electrostatic and electromagnetic particle-in-cell simulation code with applications in particle-beam physics, plasma fusion, and electron-hole transport in semiconducting detectors. Before joining Tech-X, he was a software developer and quantitative analyst at RangeMark Financial Services, where he developed the theory and application software to simulate the performance of residential and commercial mortgage-backed securities. Mr. Loh has a background in physics and was engaged in a Ph.D. program at the University of Chicago before joining RangeMark. His research focused on determining the evolution of matter distribution throughout the universe from observations of galaxy clusters at high redshifts. He left with a masters in physics. He also has a bachelor's degree in physics from UCLA.

CHAPTER 1

Introduction

Projecting future performance in finance is rarely an endeavor that will lead to results that exactly mimic reality. Equity products vary as the market evolves, seemingly simple fixed-income products may fluctuate in value due to changing interest rates, and overall most financial products have an ebb and flow of value. None of this is shocking, since much of finance is about the risk of the unknown. Understanding, measuring, and making decisions with future performance risk in mind is the focus of most financial professionals’ day-to-day jobs. To understand this risk, models can be built to project what would happen given a set of certain circumstances. Depending on the sophistication of the financial analyst and the level of detail justified for a transaction, a range of techniques are available. The most basic isolated calculations form the starting point for these techniques, which then become more complicated when interconnected concepts are tied together in a deterministic model, and eventually a simulation may be constructed when a simple closed form solution is not appropriate or even possible. This book intends to focus on the last of those three methods, simulation, by taking readers through basic theory and techniques that can be instantly applied to a variety of financial products.