EXCEL MODELING AND ESTIMATION IN INVESTMENTS,
FOURTH
EDITION
Preface
Contents,
Desk
Copy,
Purchase on Amazon,
Free
Samples.
For more than 25 years, since the emergence of PCs, Lotus 1-2-3, and Microsoft Excel® in the 1980’s, spreadsheet models have been the dominant vehicles for finance professionals in the business world to implement their financial knowledge. Yet even today, most Investments textbooks have little or no coverage of how to build Excel models. This book fills that gap. It teaches students how to build financial models in Excel. It provides step-by-step instructions so that students can build and estimate models themselves (active learning), rather than being handed already completed spreadsheets (passive learning). It progresses from simple examples to practical, real-world applications. It spans nearly all quantitative models in investments, including nearly all niche areas of investments.
My goal is simply to change finance education from being calculator based to being Excel based. This change will better prepare students for the 21st century business world. This change will increase student evaluations of teacher performance by enabling more practical, real-world content and by allowing a more hands-on, active learning pedagogy.
The
Fourth Edition adds great new investments content:
· Perform either unconstrained or constrained portfolio optimization on any number of risky assets up to maximum of 20 assets
· Immunize bond portfolios against interest rate risk by matching the durations of assets and liabilities, by matching both durations and convexities, or by matching cash flows
·
Use
simulation to price both path-independent derivatives (e.g., European options,
cash-or-nothing options, asset-or-nothing options) and path-dependent
derivatives (e.g., Asian options, lookback options, and barrier options) – both
with and without jumps
·
Analyze
trading strategies involving many options, stocks, bonds, and futures either
holding to maturity or holding to any date before maturity – it includes a
database of 50 trading strategies with bullish strategies, bearish strategies,
high volatility strategies, low volatility strategies, combined directional and
volatility strategies, and arbitrage strategies
· Price derivatives on alternative types of underlying assets, such as stocks, stock indexes, futures, and foreign currencies
· Determine margin calls and excess margin on futures contracts
· Translate bond pricing into alternative foreign currency values
· Compute the average of a N-step and a N-1-step binomial model to order to gain pricing accuracy
· See how the binomial model converges to the normal distribution
· Use current Trade and Quote (TAQ) data to compute the National Best Bid and Offer (NBBO), the quoted spread, the effective spread, and determine which exchange has the lowest cost of trading
The CD contains Ready-To-Build
spreadsheets, which are based on the Excel 2007-2010 (xlsx) file
format. However, the CD also contains a folder with Ready-To-Build spreadsheets
based on Excel 97-2003 (xls) format. By default, the explanations in the
book are based on Excel 2010. However, the book also contains “Excel 2007
Equivalent” boxes that explains how to do the equivalent step in Excel 2007
(when it is different than Excel 2010) and “Excel 97-2003 Equivalent” boxes that
explain how to do the equivalent step in Excel 97 through Excel 2003.
The instruction boxes on the Ready-To-Build spreadsheets are bitmapped images so that the formulas cannot just be copied to the spreadsheet. Both the instruction boxes and arrows are objects, so that all of them can be deleted in one step when the spreadsheet is complete and everything else will be left untouched. Click on Home | Editing | Find & Select down-arrow | Select Objects, then select all of the instruction boxes and arrows, and press the delete key. Furthermore, any blank rows can be deleted, leaving a clean spreadsheet for future use.
The book contains a significant number of comparative statics exercises (lower risk aversion, higher short-rate, etc.) and explores a variety of optional choices (alternative models to forecast expected return, alternative spreads and combinations, etc.). In each case, a picture is shown of how things change and there is a discussion of what this means in economic terms. For example, below is Figure 5.14 which explores what happens to the optimal portfolio when risk aversion is lowered?
FIGURE 5.14 Risk Aversion of 16 and 0.4
The CD provides Ready-To-Build Spreadsheets for every chapter with:
The model setup, such as input values, labels, and graph. There are step-by-step instructions for building the model on the spreadsheet itself. All instructions are explained twice: once in words and a second time as an Excel formula. Students enter the formulas and copy them as instructed to build the spreadsheet.
Many spreadsheets use real-world data.
Spin buttons, option buttons, and graphs facilitate visual interactive learning.
What Is Unique About This Book
There are many features which distinguish this book from any other:
· Plain Vanilla Excel. Other books on the market emphasize teaching students programming using Visual Basic for Applications (VBA) or using macros. By contrast, this book does nearly everything in plain vanilla Excel. Although programming is liked by a minority of students, it is seriously disliked by the majority. Plain vanilla Excel has the advantage of being a very intuitive, user-friendly environment that is accessible to all. It is fully capable of handling a wide range of applications, including quite sophisticated ones. Further, all that is assumed is that your students already know the basics of Excel, such as entering formulas in a cell and copying formulas from one cell to another. All other features of Excel (such as built-in functions, Data Tables, Solver, etc.) are explained as they are used.
· Build From Simple Examples To Practical, Real-World Applications. The general approach is to start with a simple example and build up to a practical, real-world application. In many chapters, the previous Excel model is carried forward to the next, more complex model. For example, the chapter on binomial option pricing carries forward Excel models as follows: (a.) single-period model with replicating portfolio, (b.) eight-period model with replicating portfolio, (c.) eight-period model with risk-neutral probabilities, (d.) eight-period model with risk-neutral probabilities for American or European options with discrete dividends, (e.) full-scale, fifty-period model with risk-neutral probabilities for American or European options with discrete dividends. Whenever possible, this book builds up to full-scale, practical applications using real data. Students are excited to learn practical applications that they can actually use in their future jobs. Employers are excited to hire students with Excel modeling skills, who can be more productive faster.
· Supplement For All Popular Investments Textbooks. This book is a supplement to be combined with a primary textbook. This means that you can keep using whatever textbook you like best. You don’t have to switch. It also means that you can take an incremental approach to incorporating Excel modeling. You can start modestly and build up from there.
· A Change In Content Too. Excel modeling is not merely a new medium, but an opportunity to cover some unique content items which require computer support to be feasible. For example, the Portfolio Optimization chapter uses 10 years of monthly returns for individual stocks, U.S. Fama-French portfolios, and country ETFs to estimate the (unconstrained) Risky Opportunity Set and the (unconstrained) Complete Opportunity Set. The same data is used by Solver to numerically solve for the Constrained Risky Opportunity Set and the Constrained Complete Opportunity Set. The same data is used to estimate the Static CAPM using the Fama-MacBeth method and to estimate the APT or Intertemporal CAPM using the Fama-MacBeth method. The Market Microstructure chapter uses current Trade and Quote (TAQ) data to compute the National Best Bid and Offer (NBBO), the quoted spread, the effective spread, and to determine which exchange has the lowest cost of trading The Excel model in US Yield Curve Dynamics shows 40 years of monthly US yield curve history in just a few minutes. Real call and put prices are fed into the Black-Scholes Option Pricing model and Excel’s Solver is used to back-solve for the implied volatilities. Then the “smile” pattern (or more like a “scowl” pattern) of implied volatilities is graphed. As a practical matter, all of these sophisticated applications require Excel.
This book uses a number of conventions.
· Time Goes Across The Columns And Variables Go Down The Rows. When something happens over time, I let each column represent a period of time. For example, in life-cycle financial planning, date 0 is in column B, date 1 is in column C, date 2 is in column D, etc. Each row represents a different variable, which is usually labeled in column A. This manner of organizing Excel models is so common because it is how financial statements are organized.
· Color Coding. A standard color scheme is used to clarify the structure of the Excel models. The Ready-To-Build spreadsheets on CD uses: (1) yellow shading for input values, (2) no shading (i.e. white) for throughput formulas, and (3) green shading for final results ("the bottom line"). A few Excel models include choice variables. Choice variables use blue shading. The Constrained Portfolio Optimization spreadsheet includes constraints. Constaints use pink-purple shading.
· The Time Line Technique. The most natural technique for discounting cash flows in an Excel model is the time line technique, where each column corresponds to a period of time. As an example, see the section labeled “Bond Price using a Timeline” in the figure below.
· Using As Many Different Techniques As Possible. In the figure above, the bond price is calculated using as many different techniques as possible. Specifically, it is calculated three ways: (1) discounting each cash flow on a time line, (2) using the closed-form formula, and (3) using Excel’s PV function. This approach makes the point that all three techniques are equivalent. This approach also develops skill at double-checking these calculations, which is a very important method for avoiding errors in practice.
· Symbolic Notation is Self-Contained. Every spreadsheet that contains symbolic notation in the instruction boxes is self-contained (i.e., all symbolic notation is defined on the spreadsheet).
I challenge the readers of this book to dramatically improve your finance education by personally constructing all of the Excel models in this book. This will take you about 10 – 20 hours hours depending on your current Excel modeling skills. Let me assure you that it will be an excellent investment. You will:
· gain a practical understanding of the core concepts of Investments,
· develop hands-on, Excel modeling skills, and
· build an entire suite of finance applications, which you fully understand.
When you complete this challenge, I invite you to send an e-mail to me at cholden@indiana.edu to share the good news. Please tell me your name, school, (prospective) graduation year, and which Excel modeling book you completed. I will add you to a web-based honor roll at:
http://www.excelmodeling.com/honor-roll.htm
We can celebrate together!
This book is one of two Excel Modeling books by Craig W. Holden, published by Pearson / Prentice Hall. The other book is Excel Modeling in Corporate Finance. Both books teach value-added skills in constructing financial models in Excel. Complete information about my Excel Modeling books is available at my web site:
If you have any suggestions or corrections, please e-mail them to me at cholden@indiana.edu. I will consider your suggestions and will implement any corrections in the next edition.
Suggestions for Faculty Members
There is no single best way to use Excel Modeling in Investments. There are as many different techniques as there are different styles and philosophies of teaching. You need to discover what works best for you. Let me highlight several possibilities:
1. Out-of-class individual projects with help. This is a technique that I have used and it works well. I require completion of several short Excel modeling projects of every individual student in the class. To provide help, I schedule special “help lab” sessions in a computer lab during which time myself and my graduate assistant are available to answer questions while students do each assignment in about an hour. Typically about half the questions are Excel questions and half are finance questions. I have always graded such projects, but an alternative approach would be to treat them as ungraded homework.
2. Out-of-class individual projects without help. Another technique is to assign Excel modeling projects for individual students to do on their own out of class. One instructor assigns seven Excel modeling projects at the beginning of the semester and has individual students turn in all seven completed Excel models for grading at the end of the semester. At the end of each chapter are problems that can be assigned with or without help. Faculty members can download the completed Excel models and answers to end-of-chapter problems at http://www.pearsonhighered.com/irc. See your local Pearson representative to gain access.
3. Out-of-class group projects. A technique that I have used for the last fifteen years is to require students to do big Excel modeling projects in groups. I have students write a report to a hypothetical boss, which intuitively explains their method of analysis, key assumptions, and key results.
4. In-class reinforcement of key concepts. The class session is scheduled in a computer lab or equivalently students are required to bring their (required) laptop computers to a technology classroom, which has a data jack and a power outlet at every student station. I explain a key concept in words and equations. Then I turn to a 10-15 minute segment in which students open a Ready-To-Build spreadsheet and build the Excel model in real-time in the class. This provides real-time, hands-on reinforcement of a key concept. This technique can be done often throughout the semester.
5. In-class demonstration of Excel modeling. The instructor can perform an in-class demonstration of how to build Excel models. Typically, only a small portion of the total Excel model would be demonstrated.
6. In-class demonstration of key relationships using Spin Buttons, Option Buttons, and Charts. The instructor can dynamically illustrate comparative statics or dynamic properties over time using visual, interactive elements. For example, one spreadsheet provides a “movie” of 37 years of U.S. term structure dynamics. Another spreadsheet provides an interactive graph of the sensitivity of bond prices to changes in the coupon rate, yield-to-maturity, number of payments / year, and face value.
I’m sure I haven’t exhausted the list of potential teaching techniques. Feel free to send an e-mail to cholden@indiana.edu to let me know novel ways in which you use this book.
I thank Tessa O’Brien, Mark Pfaltzgraff, David Alexander, Jackie Aaron, P.J. Boardman, Mickey Cox, Maureen Riopelle, and Paul Donnelly of Pearson for their vision, innovativeness, and encouragement of Excel Modeling in Investments. I thank Amy Foley, Nancy Fenton, Susan Abraham, Mary Kate Murray, Ana Jankowski, Lori Braumberger, Holly Brown, Debbie Clare, Cheryl Clayton, Kevin Hancock, Josh McClary, Bill Minic, Melanie Olsen, Beth Ann Romph, Erika Rusnak, Gladys Soto, and Lauren Tarino of Pearson for many useful contributions. I thank Professors Alan Bailey (University of Texas at San Antonio), Zvi Bodie (Boston University), Jack Francis (Baruch College), David Griswold (Boston University), Carl Hudson (Auburn University), Robert Kleiman (Oakland University), Mindy Nitkin (Simmons College), Steve Rich (Baylor University), Tim Smaby (Penn State University), Charles Trzcinka (Indiana University), Sorin Tuluca (Fairleigh Dickinson University), Marilyn Wiley (Florida Atlantic University), and Chad Zutter (University of Pittsburgh) for many thoughtful comments. I thank my dad, Bill Holden, and my graduate students Sam Singhania, Harry Bramson, Brent Cherry, Scott Marolf, Heath Eckert, Ryan Brewer, Ruslan Goyenko, Wendy Liu, and Wannie Park for careful error-checking. I thank Jim Finnegan and many other students for providing helpful comments. I thank my family, Kathryn, Diana, and Jimmy, for their love and support.
CRAIG W. HOLDEN
Craig W. Holden is a Professor of Finance at the Kelley School of Business at Indiana University. His M.B.A. and Ph.D. are from the Anderson School at UCLA. He is the winner of many teaching and research awards, including a Fama/DFA Prize. His research on security trading and market making (“market microstructure”) has been published in leading academic journals. He has written Excel Modeling in Investments and Excel Modeling in Corporate Finance. The Fourth Editions in English are published by Pearson / Prentice Hall and there are International, Chinese, and Italian editions. He has chaired eighteen dissertations, been a member or chair of 51 dissertations, served on the program committee of the Western Finance Association for eleven years, and served as an associate editor of the Journal of Financial Markets for thirteen years. He chaired the finance department undergraduate committee for twelve years, chaired the finance department doctoral committee for four years, and chaired three different schoolwide committees for a combination of six years. He has led several major curriculum innovations in the finance department. More information is available at Craig’s home page: www.kelley.iu.edu/cholden.
