Excel Modeling in Investments

Preface       vii Fifth Edition Changes      vii

Ready-To-Build Spreadsheets vii

What Is Unique About This Book        xi

Conventions Used In This Book xii

Craig’s Challenge  xiv

Excel® Modeling Books   xiv

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, APR, and Foreign Currencies        2

1.3 Duration and Convexity      7

1.4 Price Sensitivity        9

1.5 Immunization  11

1.6 System of Five Bond Variables     17

Problems     18

Chapter 2 The Yield Curve       21

2.1 Obtaining It From Treasury Bills and Strips      21

2.2 Using It To Price A Coupon Bond  22

2.3 Using It To Determine Forward Rates    23

Problems     24

Chapter 3 Affine Yield Curve Models  25

3.1 US Yield Curve Dynamics    25

3.2 The Vasicek Model   30

3.3 The Cox-Ingersoll-Ross Model    32

Problems     34

PART 2 PORTFOLIO MANAGEMENT    35

Chapter 4 Portfolio Optimization        35

4.1 Two Risky Assets and a Riskfree Asset  35

4.2 Descriptive Statistics 38

4.3 Many Risky Assets and a Riskfree Asset         42

4.4 Any Number of Risky Assets        52

Problems     57

Chapter 5 Constrained Portfolio Optimization       58

5.1 No Short Sales, No Borrowing, and Other Constraints         58

5.2 Any Number of Risky Assets        68

Problems     77

Chapter 6 Portfolio Performance        78

6.1 Evaluation Measures 78

Problems     80

Chapter 7 Portfolio Diversification Lowers Risk      81

7.1 Basics   81

7.2 International   82

Problems     84

PART 3 SECURITY ANALYSIS     85

Chapter 8 Stock Valuation        85

8.1 Dividend Discount Mode