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