E
XCEL MODELING AND ESTIMATION
IN INVESTMENTS
Third
Edition
CRAIG W. HOLDEN
Max Barney Faculty Fellow and Associate Professor
Kelley School of Business
Indiana University
Copyright © 2008 by Prentice Hall, Inc., Upper Saddle River, New Jersey 07458
To Kathryn, Diana, and Jimmy.
Contents iii
CONTENTS
Preface ..................................................................................... vii
Third Edition Changes .................................................................................... vii
What Is Unique About This Book ..................................................................... x
Conventions Used In This Book ..................................................................... xii
Craig’s Challenge ........................................................................................... xiii
The Excel Modeling and Estimation Series .................................................. xiii
Suggestions for Faculty Members .................................................................. xiv
Acknowledgements ........................................................................................... xv
About The Author ................................................................. xvi
PART 1 BONDS / FIXED INCOME
SECURITIES ............................................. 1
Chapter 1 Bond Pricing ............................................................ 1
1.1 Annual Payments .......................................................................................... 1
1.2 EAR and APR ............................................................................................... 2
1.3 By Yield To Maturity .................................................................................. 3
1.4 Dynamic Chart............................................................................................. 4
1.5 System of Five Bond Variables ................................................................... 5
Problems .............................................................................................................. 6
Chapter 2 Bond Duration ......................................................... 8
2.1 Basics ............................................................................................................. 8
2.2 Price Sensitivity using Duration ................................................................ 10
2.3 Dynamic Chart............................................................................................ 12
Problems ............................................................................................................ 14
Chapter 3 Bond Convexity ..................................................... 15
3.1 Basics ........................................................................................................... 15
3.2 Price Sensitivity Including Convexity ....................................................... 16
3.3 Dynamic Chart............................................................................................ 19
Problems ............................................................................................................ 21
Chapter 4 The Yield Curve .................................................... 22
4.1 Obtaining It From Treasury Bills and Strips .......................................... 22
4.2 Using It To Price A Coupon Bond ............................................................ 23
4.3 Using It To Determine Forward Rates .................................................... 25
Problems ............................................................................................................ 25
Chapter 5 US Yield Curve Dynamics .................................. 27
5.1 Dynamic Chart........................................................................................... 27
Problems ............................................................................................................ 33
Chapter 6 Affine Yield Curve Models ................................. 35
6.1 The Vasicek Model .................................................................................... 35
6.2 The Cox-Ingersoll-Ross Model ................................................................. 38
Problems ............................................................................................................ 40
iv Contents
PART 2 STOCKS / SECURITY
ANALYSIS ............................................... 41
Chapter 7 Portfolio Optimization ........................................ 41
7.1 Two Risky Assets and a Riskfree Asset ................................................... 41
7.2 Descriptive Statistics ................................................................................. 44
7.3 Many Risky Assets and a Riskfree Asset ................................................. 49
Problems ............................................................................................................ 60
Chapter 8 Constrained Portfolio Optimization .................. 62
8.1 No Short Sales, No Borrowing, and Other Constraints ......................... 62
Problems ............................................................................................................ 78
Chapter 9 Asset Pricing ......................................................... 79
9.1 Static CAPM Using Fama-MacBeth Method .......................................... 79
9.2 APT or Intertemporal CAPM Using Fama-McBeth Method ................ 84
Problems ............................................................................................................ 91
Chapter 10 Trading Simulations using @RISK ................. 93
10.1 Trader Simulation ................................................................................... 93
10.2 Dealer Simulation .................................................................................. 109
Chapter 11 Portfolio Diversification Lowers Risk ...........127
11.1 Basics ...................................................................................................... 127
11.2 International .......................................................................................... 129
Problems .......................................................................................................... 131
Chapter 12 Life-Cycle Financial Planning ........................132
12.1 Basics ...................................................................................................... 132
12.2 Full-Scale Estimation ............................................................................ 134
Problems .......................................................................................................... 142
Chapter 13 Dividend Discount Models ..............................143
13.1 Dividend Discount Model ...................................................................... 143
Problems .......................................................................................................... 144
Chapter 14 Du Pont System Of Ratio Analysis .................145
14.1 Basics ...................................................................................................... 145
Problems .......................................................................................................... 146
PART 3 OPTIONS / FUTURES /
DERIVATIVES ..................................... 147
Chapter 15 Option Payoffs and Profits .............................147
15.1 Basics ...................................................................................................... 147
Problems .......................................................................................................... 149
Chapter 16 Option Trading Strategies ..............................150
16.1 Two Assets .............................................................................................. 150
16.2 Four Assets ............................................................................................. 155
Problems .......................................................................................................... 159
Chapter 17 Put-Call Parity .................................................162
17.1 Basics ...................................................................................................... 162
17.2 Payoff Diagram ...................................................................................... 162
Problems .......................................................................................................... 164
Chapte
r
18.
18.
18.
3
18.
18.
18.
Pr
o
Chapte
r
19.
19.
19.
3
19.
19.
Pr
o
Chapte
r
20.
20.
Pr
o
Chapte
r
21.
21.
Pr
o
Chapte
r
22.
22.
Pr
o
PAR
T
Chapte
r
23.
23.
23.
3
23.
23.
23.
23.
23.
23.
23.
23.
CONT
E
R
e
E
x
C
h
C
h
C
h
r
18 Bino
m
1 Estimatin
g
2 Sin
g
le Peri
o
3
Multi-Peri
o
4 Risk Neutr
a
5 American
W
6 Full-Scale .
.
o
blems ...........
.
r
19 Black
1 Basics .......
.
2 Continuou
s
3
Greeks ......
.
4 Implied V
o
5 Exotic Opt
i
o
blems ...........
.
r
20 Mert
o
1 Two Meth
o
2 Impact of
R
o
blems ...........
.
r
21 Spot-
1 Basics .......
.
2 Index Arbi
t
o
blems ...........
.
r
22 Inter
n
1 S
y
stem of
F
2 Estimatin
g
o
blems ...........
.
T
4 E
X
r
23 Usef
u
1 Quickl
y
D
e
2 Freeze Pan
3
Spin Butto
n
4 Option Bu
t
5 Scroll Bar .
.
6 Install Sol
v
7 Format Pa
i
8 Condition
a
9 Fill Handl
e
10 2-D Scatt
e
11 3-D Surfa
E
NTS O
N
e
adme.txt
x
cel Mod Est
i
h
01 Bond Pri
h
02 Bond D
u
h
03 Bond Co
n
m
ial Opti
o
Volatilit
y
.....
o
d ..................
o
d ...................
a
l ...................
W
ith Discrete
.
......................
.
......................
Scholes
O
.
......................
s
Dividend .....
.
......................
o
latilit
y
...........
i
ons ................
.
......................
o
n Corpo
r
o
ds ..................
R
is
k
................
.
......................
Futures
P
.
......................
t
ra
g
e .............
.
......................
n
ational
P
F
our Parit
y
C
o
Future Exch
a
.
......................
X
CEL
S
u
l Excel T
r
e
lete The Inst
r
es ...................
n
s and the De
v
t
tons and Gro
.
......................
v
er or the An
a
i
nter ...............
a
l Formattin
g
e
......................
e
r Chart ........
ce Chart ........
N
CD
i
n Inv 3e.pdf
cing.xlsx
u
ration.xlsx
n
vexity.xlsx
o
n Pricin
g
.......................
.......................
.......................
.......................
Dividends .....
.......................
.......................
O
ption Pr
i
.......................
.......................
.......................
.......................
.......................
.......................
r
ate Bond
.......................
.......................
.......................
P
arit
y
(Co
s
.......................
.......................
.......................
P
arit
y
.......
o
nditions .......
a
n
g
e Rates .....
.......................
S
KILL
S
r
icks ........
r
uction Boxes
.......................
v
eloper Tab ..
up Boxes .......
.......................
a
l
y
sis ToolPa
k
.......................
.......................
.......................
.......................
.......................
g
...............
.
......................
.
......................
.
......................
.
......................
.
......................
.
......................
.
......................
.
i
cin
g
........
.
......................
.
......................
.
......................
.
......................
.
......................
.
......................
.
Model ...
.
......................
.
......................
.
......................
.
s
t of Carr
y
......................
.
......................
.
......................
.
................
.
......................
.
......................
.
......................
.
S
..........
................
.
and Arrows .
.
......................
.
......................
.
......................
.
......................
.
k
......................
.
......................
.
......................
.
......................
.
......................
.
......................
.
Conten
.
................
.
......................
.
.
......................
.
.
......................
.
.
......................
.
.
......................
.
.
......................
.
.
......................
.
.
................
.
......................
.
.
......................
.
.
......................
.
.
......................
.
.
......................
.
.
......................
.
.
................
.
......................
.
.
......................
.
.
......................
.
y
) .............
.
......................
.
.
......................
.
.
......................
.
.
................
.
......................
.
.
......................
.
.
......................
.
..........
2
.
................
.
......................
.
.
......................
.
.
......................
.
.
......................
.
.
......................
.
.
......................
.
.
......................
.
.
......................
.
.
......................
.
.
......................
.
.
......................
.
ts v
.165
.
... 165
.
... 166
.
... 168
.
... 174
.
... 177
.
... 181
.
... 189
.191
.
... 191
.
... 192
.
... 195
.
... 201
.
... 203
.
... 210
.212
.
... 212
.
... 213
.
... 215
.216
.
... 216
.
... 217
.
... 218
.220
.
... 220
.
... 222
.
... 224
2
25
.225
.
... 225
.
... 225
.
... 226
.
... 227
.
... 229
.
... 230
.
... 230
.
... 231
.
... 232
.
... 232
.
... 234
