Financial Simulation Modeling in Excel

A Step-by-Step Guide

Keith A. Allman, Josh Laurito, Michael Loh

• Publisher: Wiley
• Year: 2011
• ISBN:
  9781118137222
• Edition: 1
• Topic: Commerce
• Subtopic: Finance

"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.

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.

WHAT IS SIMULATION?

In general, simulation is typically a process that attempts to imitate how events might take place in real life. Simulations can be extraordinarily simple, such as conducting a mock interview with a peer, or incredibly complex, such as using a flight simulator to mimic a Mars landing. A simulation can also be for a tangible real-life process or for something abstract. For instance, the military often engages in simulations that try to replicate real-life war scenarios. Soldiers storm faux buildings with people playing different roles in accordance with situations they would expect in a real war. However, there are also abstract simulations such as those conducted in finance.

Even though simulations in finance may be somewhat intangible, the events that we worry about are very real. Perhaps a fund manager has a portfolio of corporate exposures. The most obvious real-life event that would be of concern is the default of one or more of these corporate exposures. Simulating defaults would be an important exercise for the fund manager to undertake. Similarly, a fixed-income specialist might invest in fixed-rate products; however, the specialist might be funded by floating rate debt returns. Basis risk exists in such a system, and the evolution of interest rates is the real-life event that the specialist would worry about. A simulation of interest rates could greatly help the specialist design a portfolio to reduce risk.

CHARACTERISTICS OF A SIMULATION

Regardless if one is entering into a military simulation or creating a code-based simulation, there are similarities. The starting point for most simulations is the assumptions that go into it. For a military simulation that is preparing for urban warfare, this might include the number of soldiers per unit, the weapons and supplies that each solider carries, the standard and unique training of the soldiers, and the possible buildings, enemies, weather, and so forth that they could encounter. In a financial simulation, such as the corporate default example, you might have characteristics of the companies, such as the industry, regional operating location, historical asset levels, historical liability levels, and so forth.

Once the assumptions of the topic that we are trying to simulate are understood, a method for assembling the system and rules for how the system works are required. In our military simulation example, we would have a training area where the soldiers arrive with all of the training and gear one would expect, and then have an area with buildings and enemies they would expect to face. A mission with an objective would be established, and certain rules might be integrated to help make the simulation as real as possible. For instance, even though a soldier could theoretically leave the simulation area to get around an obstacle, a rule could define the simulation area and state that soldiers are not allowed to go beyond its perimeter. Similarly, in a financial simulation we would need a medium in which to conduct the simulation, which in modern times is done within the confines of a computer application. We program rules to guide our assumptions’ behavior through processes that simulate how real-life events might unfold.

Another characteristic of simulations is that they may be repeated to determine varying outcomes. In the military situation, soldiers may choose one path through the buildings in one iteration of the simulation and then choose a different path in another iteration. The outcomes in both scenarios could be markedly different. Similarly, in a financial simulation asset levels for the same company in a future period could be assumed to be different from one simulation iteration to the next. This could mean that the default outcomes are also different.

At the end of the simulation, there should always be an analysis. Multiple aspects of the military simulation would be analyzed, such as speed of completion of the simulation, effectiveness at achieving the mission objective, supplies used, and so forth. In the financial simulation, we would want to see the frequency of companies defaulting, which types of companies defaulted, the characteristics of those companies, the balance of exposures for the ones defaulting, the time at which they defaulted in the future, and so forth.

Finally, we should be concerned about the validity of our results. Numerous flaws could occur in the construction of the military simulation. Perhaps the individuals posing as enemy soldiers are not as aggressive as in real life or the equipment used is different. In the financial simulation, perhaps we assumed lower correlation than really exists or measured historical volatility wrong. All of these could lead to error that should be taken into account. See Figure 1.1.

Figure 1.1 Most simulations will follow a similar process of selecting or creating assumptions, constructing a simulation environment with rules, analyzing the outcome, and possibly repeating the process.

INSTRUCTIONAL METHODOLOGY

Financial simulation can be a tricky subject for readers and authors since people have a multitude of reasons for using simulation in finance. To approach this unique issue, the book is laid out in a specific manner. Chapters 2 and 3 are what I would call “tool set” chapters. They focus on core elements of simulations that are inherent to most financial simulations (and to many simulations in other fields as well). Chapter 2 works through random number generation and eventually to explaining a common term heard in finance, Brownian motion. After that, in Chapter 3, correlation between variables is explained with examples on how correlated random numbers are generated. These tools are invaluable for constructing simulations and require a thorough understanding. For instance, one of the most common errors I have noticed financial analysts make when implementing simulations for the first time is an incorrect method of generating random numbers. Similarly, incorrectly accounting for correlation can lead to massive problems in a simulation.

Once the tools are developed, readers begin to use them for different purposes. Chapter 4 takes readers through simulating interest rate paths to price bonds using methods credited to Hull and White. Chapter 5 expands the reader's knowledge of simulation by creating a corporate default simulation based on structural and reduced form models. Default is taken further in Chapter 6 with a thorough look at simulating pools of assets. Clearly, as authors, we cannot anticipate every reader's specific need, but the topics we have chosen reflect the most frequent and current topics related to simulation.

Finally, integrated throughout the chapters, but also a focus of chapters themselves is analysis, interpretation, and advanced thoughts on the simulation process. Chapter 7 shows readers data deficiencies and how to manage data as it relates to a simulation. Exercises, in the form of Model Builder examples, are used to help demonstrate these concepts. Although not as technically demanding, these sections should not be skipped over since they focus on the proper use of simulation; which is just as important as implementing it correctly. See Figure 1.2.

Figure 1.2 The chapters in this book follow a logical and intended order.

HOW THIS BOOK WORKS

There are notable differences and many similarities between this book and the others in my Step-by-Step Guide series. All rely on theory and practical exercises to transform financial concepts into dynamic, usable models. A common theme to the other books is that they work through individual “modules” that culminate in a single complete model. While this book has readers work through similar “modules,” chapter after chapter, instead of creating a single unified model the Model Builders produce multiple, smaller models. This is not to say that they are less complex; in fact, many of the models in this book are technically and mathematically more complex than the other books. The use of multiple models is necessary because simulation has its place in many parts of finance, and using a single unified model would be illogical and inappropriate.

Whether you are familiar with the other books or new to the series, you will find that each section begins with a discussion of theory and then moves on to a Model Builder exercise, where the theory is transferred to an application in Excel. Eventually as all theoretical concepts are read and Model Builder steps completed the reader should have operational examples that are identical to the ones included on the website that accompanies this book. Readers should make every attempt at constructing the models themselves, since this is the best way to learn and understand every aspect of the models. If any part of the text seems unclear a reader should leverage the completed models on the website to understand every section.

While financial theory and implementation are two critical elements in learning proper modeling techniques, one of the biggest challenges of creating an instructional book is the different skill levels of readers. Some readers have a deep understanding of the theory and are really searching for practical techniques to create usable Excel/Visual Basic Applications (VBA) based solutions, while others may come from a very technical background and understand the mechanics of Excel/VBA but are more interested in learning what body of knowledge exists and how it ties into finance. For this reason, readers will notice various attempts at making the text applicable for the widest possible audience.

A balance has been attempted on both the theoretical and technical level. For the theory sections, enough background and mathematical formulas are provided to introduce, elucidate, and reinforce the section we are focusing on. However, this book is purposely not set up to list out and derive all formulas, nor does it intend to explicate in detail the origination of every concept. Enough theory is provided to understand what it is we are discussing, why it is important in finance, and how the analytical method that is provided can be used.

The technical level of this book starts out fairly simple, but it gets more complex in later chapters. For each chapter we strive to demonstrate the theory behind what we are discussing by first using Model Builder examples that operate entirely on the sheet without the use of VBA. However, Excel is a poor medium for simulation and VBA used within Excel's provided Visual Basic Editor (VBE) is a better environment to practically implement simulations. With this in mind we have provided VBA-based examples to many of the most important sections. We have tried to keep the coding straightforward for those who may be new to or at a beginner level of the VBA language.

Given that some readers will be on an extreme end of the spectrum, either completely new to financial simulation or advanced in the field, we have created an appendix to prevent the burden of too much off-topic or advanced information for the average reader. For instance, background mathematical concepts may be necessary for some readers, while some advanced topics discussed may pique advanced readers’ interest. Rather than leave such readers without a resource or with the thought that some sections ended too quickly, we have included background mathematics and more advanced implementations in the Appendix. The complementary, completed Excel/VBA files related to these discussions are available on the book's website.

ABOUT THE COMPANION WEBSITE

It is clear that technology is changing how we take in information. You may be reading this book in digital form via an e-reader of some type. As digital media becomes a larger market, technical books like this hav...