For nearly 20 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 rely on calculators as the primary tool and have little (if any) coverage of how to build spreadsheet 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 models themselves (active learning), rather than handing students canned "templates" (passive learning). It progresses from simple examples to practical, real-world applications. It spans nearly all quantitative models in investments.

Why I Wrote This Book

My goal is simply to change finance education from being calculator based to being spreadsheet modeling based. This change will better prepare students for the 21^st century business world. This change will increase student satisfaction in the classroom by allowing more practical, real-world applications and by enabling a more hands-on, active learning approach.

There are many features which distinguish this book from anything else on the market:

• Teach By Example. I believe that the best way to learn spreadsheet modeling is by working through examples and completing a lot of problems. This book fully develops this hands-on, active learning approach. Active learning is a well-established way to increase student learning and student satisfaction with the course / instructor. When students build financial models themselves, they really "get it." As I tell my students, "If you build it, you will learn."
• 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 spreadsheet modeling. You can start modestly and build up from there. Alternative notation versions are available that match the notation of all popular investments textbooks.
• 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 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, your students already know the basics of Excel and nothing more is assumed. Students are assumed to be able to enter formulas in a cell and to copy formulas from one cell to another. All other features of Excel (graphing, built-in functions, 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 spreadsheet model is carried forward to the next more complex model. For example, the chapter on binomial option pricing carries forward spreadsheet 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.) full-scale, fifty-period model with volatilities estimated from real returns data. 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 spreadsheet modeling skills, who can be more productive faster.
• A Change In Content Too. Spreadsheet 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 full-scale, real data spreadsheet model in Portfolio Optimization has you collect historical stock prices for 20 companies from Yahoo! Finance and calculate the sample means, standard deviations, and correlations. These inputs are feed into the matrix functions of Excel to calculate the efficient frontier, the tangent line, and the weights of the tangent portfolio, and then graph everything. The spreadsheet model in Life-Cycle Financial Planning includes a detailed treatment of federal and state tax schedules, social Security taxes and benefits, etc., which permit the realistic exploration savings, retirement, and investments choices over a lifetime. The spreadsheet model in US Yield Curve Dynamics shows you 30 years of monthly US yield curve history in just a few minutes. Real call and put prices are feed into the Black Scholes Option Pricing model and Excel's Solver to 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 spreadsheet modeling.

Conventions Used In This Book

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 a labeled in column A. This manner of organizing spreadsheets 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 spreadsheet models. The printed book uses: (1) light gray shading for input values, (2) no shading (i.e. white) for throughput formulas, (3) dark gray shading for final results ("the bottom line"). The accompanying 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 spreadsheets include choice variables. Choice variables use medium gray shading in the printed book and blue shading in the electronic version.
• The Time Line Technique. The most natural technique for discounting cash flows in a spreadsheet model is the time line technique, where each column corresponds to a period of time.
• 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 four ways: (1) discounting each cash flow on a time line, (2) using the closed-form formula, (3) using Excel's PV function, and (4) using Excel's more advanced PRICE function (found in Excel's Analysis ToolPak Add-In). This approach makes the point that all four techniques are equivalent. This approach also develops skill at double-checking these calculations, which is a very important method for avoiding errors in practice.
• Dynamic Charts. Dynamic charts allow you to see such things as a "movie" of the Term Structure of Interest Rates moves over time or an "animated graph" of how increasing the volatility of an underlying stock increases the value of an option. Dynamic charts are a combination of an up/down arrow (a "spinner") to rapidly change an input and a chart to rapidly display the changing output. I invented dynamic charts back in 1995 and I have included many examples of this useful educational tool throughout this book.

Craig's Challenge

I challenge the readers of this book to dramatically improve your finance education by personally constructing all 43 spreadsheet models in all 20 chapters of this book. This will take you about 22 to 43 hours depending on your current spreadsheet 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, spreadsheet 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 spreadsheet modeling book you completed. I will add you to a web-based honor roll at:
http://www.spreadsheetmodeling.com/honor-roll.htm

We can celebrate together!

The Spreadsheet Modeling Series

This book is part a series of book/CDs on Spreadsheet Modeling by Craig W. Holden, published by Prentice Hall. The series includes:

• Spreadsheet Modeling in Corporate Finance,
• Spreadsheet Modeling in the Fundamentals of Corporate Finance,
• Spreadsheet Modeling in Investments,
• Spreadsheet Modeling in the Fundamentals of Investments, and
• Spreadsheet Modeling in the Finance (a mixture of investments and corporate finance).

Each book teaches value-added skills in constructing financial models in Excel. Complete information about the Spreadsheet Modeling series is available at my web site:
http://www.spreadsheetmodeling.com

Most of the Spreadsheet Modeling book/CDs can be purchased any time at:
http://www.amazon.com

The Spreadsheet Modeling Community

You can access the worldwide spreadsheet modeling community by clicking on Community (Free Enhancements) at my web site http://www.spreadsheetmodeling.com. You will find free additions, extensions, and problems that professors and practitioners from around the world have made available for you. I will post annual updates of the U.S. yield curve database and occasional new spreadsheet models. If you would like to make available your own addition, extension, or problem to the worldwide finance community, just e-mail it to me at cholden@indiana.edu and I will post it on my web site. Your worldwide finance colleagues thank you.

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 future editions.

Suggestions for Faculty Members

There is no single best way to use Spreadsheet Modeling in Investments. There are as many 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 spreadsheet 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 spreadsheet 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 spreadsheet modeling projects for individual students to do on their own out of class. One instructor assigns seven spreadsheet modeling projects at the beginning of the semester and has individual students turn in all seven completed spreadsheet models for grading at the end of the semester. At the end of most chapters are numerous problems that can be assigned with or without help. Faculty members can download the completed spreadsheet models at http://www.prenhall.com/holden. See your local Prentice Hall representative for a password.
3. Out-of-class group projects. A technique that I have used for the last seven years is to require students to do big spreadsheet modeling projects in groups. I assign students to groups based on a survey of students, where they self-rate their own Excel skills on a scale from 1 to 10. This allows me to create a mix of Excel skill levels in each group. Thus, group members can help each other. 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. This is the direction I have moved in recent years. 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 I provide students with a spreadsheet that is partially complete (say, 80% complete) and have them finish the last few lines of the spreadsheet. This provides real-time, hands-on reinforcement of a key concept. This technique can be done often throughout the semester. In the appendix are numerous "Live In-class Problems" that can be implemented this way. Faculty members can download the partially complete spreadsheets at http://www.prenhall.com/holden. See your local Prentice Hall representative for a password.
5. In-class demonstration of spreadsheet modeling. The instructor can perform an in-class demonstration of how to build spreadsheet models. Typically, only a small portion of the total spreadsheet model would be demonstrated.
6. In-class demonstration of key relationships using Dynamic Charts. The instructor can dynamically illustrate comparative statics or dynamic properties over time using dynamic charts. For example, one dynamic chart illustrates 30 years of U.S. term structure dynamics. Another dynamic chart provides an "animated" illustration 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 / CD.

Alternative Notation Versions

One nice thing about spreadsheets is that you can use long descriptive labels to describe most variables and their corresponding formulas. However, some finance formulas are complex enough that they really require mathematical notation. When this happens, I provide alternative notation versions that match the notation of all popular investments textbooks.

Acknowledgements

I thank Mickey Cox, P.J. Boardman, Maureen Riopelle, and Paul Donnelly of Prentice Hall for their vision, innovativeness, and encouragement of Spreadsheet Modeling in the Fundamentals of Investments. I thank Lori Braumberger, Holly Brown, Cheryl Clayton, Josh McClary, Bill Minic, Melanie Olsen, Gladys Soto, and Lauren Tarino of Prentice Hall for many useful contributions. I thank Alan Bailey, Jim Finnegan, Jack Francis, David Griswold, Robert Kleiman, Tim Smaby, Sorin Tuluca, and Marilyn Wiley for many thoughtful comments. I thank my Graduate Assistants Ryan Brewer, Wendy Liu, and Wannie Park and many individual students for providing helpful comments. I thank my family, Kathryn, Diana, and Jimmy, for their love and support.