**E****XCEL**** MODELING IN INVESTMENTS,
FIFTH
EDITION
**

For more than 30 years, since the emergence of Lotus
1-2-3 and Microsoft Excel^{TM} in the 1980s, 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 very limited 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 limited treatment of the most basic Excel models to comprehensive treatment
of both simple and sophisticated Excel models*.
This change will better prepare students for their future business careers. It
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 Fifth Edition adds great new investments content:

· Market microstructure including:

o Compute the National Best Bid and Offer (NBBO),

o Determine how a set of market orders and limit orders will execute in a limit order book market vs. a call market,

o Compute five alternative transaction cost measures,

o Decompose transaction costs into four components, and

o Estimate the Probability of Informed Trade (PIN),

·
Portfolio performance evaluation including Jensen’s Alpha, the
Sharpe Ratio, the Treynor Ratio, the M^{2} measure, the T^{2}
measure, and the Information measure

· Taxable vs. traditional vs. Roth savings plans

All of the real-world data, including bond prices, the yield curve, intraday trades and quotes, asset returns, exchange rates, and options prices, have been updated.

This product includes **Ready-To-Build
spreadsheets**, which can be downloaded from the Pearson web site. The
spreadsheets are available in both “**XLSX**” and “**XLS**” file formats.
By default, the screen shots and instructions in the book are based on **Excel
2013**. For the items explained in this book, there are no significant
differences relative to Excel 2010. There are few places where there are
differences relative to Excel 2007. In those instances “Excel 2007 Equivalent”
boxes have been added in the margin to explain how to do the equivalent step in
Excel 2007.

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 they can easily be
deleted when the spreadsheet is complete. Just select the boxes and arrows and
press delete. This leaves a clean spreadsheet for future use.

The book contains a significant number of sensitivity exercises (e.g., how does a change in risk aversion affect the optimal portfolio allocation?) 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 2 and 0.4**

**Ready-To-Build Spreadsheets** for every chapter provide:

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.[1]
Although programming is liked by a minority of students, it is seriously
disliked by the majority. Excel has the advantage of being a very intuitive,
user-friendly environment that is comprehensible to all. It is fully capable of
handling a wide range of applications, including quite sophisticated ones.
Further, the only assumption 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 quickly
productive.

·
**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.

[1] I have made one exception. The Constrained Portfolio Optimization spreadsheet uses a macro to repeatedly call Solver to map out the Constrained Risky Opportunity Set and the Constrained Complete Opportunity Set.

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 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 available for download use:
(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 with blue shading. The Constrained Portfolio
Optimization spreadsheet includes
constraints with pink shading.

·
**
The
Timeline Technique.**
The most natural technique for discounting cash flows in an Excel model is the
timeline 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. 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:

**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 I 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 that 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 students are asked to bring their laptop computers to class. 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 43 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
Katie Rowland, 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 Erin McDonagh, Karen Carter,
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),
Noah Stoffman (Indiana 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 Michael Kulov,
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
Craig W.
Holden is a Professor of Finance and Boquist-Meyer Faculty Fellow 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 market microstructure has been published in leading academic
journals. He has written *Excel Modeling in
Investments*** **and**
***Excel Modeling in Corporate Finance*.
The Fifth Editions in English are published by Pearson and there are
International, Chinese, and Italian editions. He has chaired 20 dissertations,
been a member or chair of 58 dissertations, serves as the Secretary-Treasurer of
the *Society for Financial Studies*, serves as an associate editor of the
*Journal of Financial Markets*, and serves on the program committees of the
*Western Finance Association* and the *European Finance Association*.
He
chaired the department undergraduate committee for thirteen years, chaired the
department doctoral committee for four years, chaired three different schoolwide
committees for a combination of six years, and currently serves for a third year
on the campus tenure advisory committee.
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**.