Excel Modeling in Investments

Excel Modeling in Investments, Fifth Edition approaches building and estimating models with Microsoft^® Excel^®. Students are shown the steps involved in building models, rather than already-completed spreadsheets.

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 Model   85

Problems     86

Chapter 9 Du Pont System Of Ratio Analysis         87

9.1 Basics   87

Problems     88

PART 4 STOCKS    89

Chapter 10 Asset Pricing         89

10.1 Static CAPM Using Fama-MacBeth Method    89

10.2 APT or Intertemporal CAPM Using Fama-McBeth Method 93

Problems     98

Chapter 11 Market Microstructure     100

11.1 National Best Bid and Offer (NBBO)     100

11.2 Transaction Cost By Exchange Using TAQ Data      100

11.3 Limit Order Book Vs. Call Market        106

11.4 Transaction Cost Measures       108

11.5 Transaction Cost Components   110

11.6 Probability of Informed Trading (PIN)  111

Problems     113

Chapter 12 Life-Cycle Financial Planning     114

12.1 Taxable Vs. Traditional Vs. Roth Savings      114

12.2 Basic Life-Cycle Planning  115

12.3 Full-Scale Life-Cycle Planning    118

Problems     125

PART 5 INTERNATIONAL INVESTMENTS      126

Chapter 13 International Parity 126

13.1 System of Four Parity Conditions         126

13.2 Estimating Future Exchange Rates       128

Problems     129

Chapter 14 Swaps 130

14.1 Valuation of Interest Rate Swaps         130

14.2 Valuation of Currency Swaps     132

Problems     133

PART 6 OPTIONS, FUTURES, AND OTHER DERIVATIVES  135

Chapter 15 Option Payoffs and Profits        135

15.1 Basics 135

Problems     136

Chapter 16 Option Trading Strategies         137

16.1 Two Assets   137

16.2 Four Assets   140

Problems     143

Chapter 17 Put-Call Parity       146

17.1 Basics 146

17.2 Payoff Diagram      146

Problems     148

Chapter 18 Binomial Option Pricing    149

18.1 Estimating Volatility         149

18.2 Single Period 150

18.3 Multi-Period 153

18.4 Risk Neutral  157

18.5 Average of N and N-1     160

18.6 Convergence to Normal   162

18.7 American With Discrete Dividends       164

18.8 Full-Scale     168

Problems     173

Chapter 19 Black-Scholes Option Pricing     175

19.1 Basics 175

19.2 Continuous Dividend        176

19.3 Greeks         180

19.4 Daily Delta Hedging         184

19.5 Trading Strategies Over Any Horizon   187

19.6 Implied Volatility    192

19.7 Exotic Options        194

Problems     199

Chapter 20 Futures        201

20.1 Spot-Futures Parity (Cost of Carry)      201

20.2 Margin         203

Problems     204

Chapter 21 Pricing By Simulation       205

21.1 Path-Independent Derivatives   205

21.2 Path-Independent Derivatives With Jumps    206

21.3 Path-Independent Derivatives With Stratified Sampling    208

21.4 Path-Dependent Derivatives     209

21.5 Path-Dependent Derivatives With Jumps      210

Problems     211

Chapter 22 Corporate Bonds   213

22.1 Two Methods         213

22.2 Impact of Risk        215

Problems     216

PART 7 EXCEL SKILLS      217

Chapter 23 Useful Excel Tricks 217

23.1 Quickly Delete The Instruction Boxes and Arrows   217

23.2 Freeze Panes         217

23.3 Spin Buttons and the Developer Tab   218

23.4 Option Buttons and Group Boxes        219

23.5 Scroll Bar      221

23.6 Install Solver or the Analysis ToolPak   222

23.7 Format Painter       222

23.8 Conditional Formatting     223

23.9 Fill Handle    224

23.10 2-D Scatter Chart 224

23.11 3-D Surface Chart         226

DOWNLOADABLE CONTENTS

   Excel Modeling in Investments Fifth Edition.pdf

  Ready-To-Build spreadsheets available in both

     XLSX and XLS file formats:

         Ch 01 Bond Pricing - Ready-To-Build.xlsx

         Ch 02 The Yield Curve - Ready-To-Build.xlsx

         Ch 03 Affine Yield Curve Models - Ready-To-Build.xlsx

         Ch 04-05 Portfolio Optimization - Ready-To-Build.xlsm

         Ch 06 Portfolio Performance - Ready-To-Build.xlsx

         Ch 07 Portfolio Diversification Lowers Risk - Ready-To-Build.xlsx

         Ch 08 Stock Valuation - Ready-To-Build.xlsx

         Ch 09 Du Pont System of Ratio Analysis - Ready-To-Build.xlsx

         Ch 10 Asset Pricing - Ready-To-Build.xlsx

         Ch 11 Market Microstructure - Ready-To-Build.xlsx

         Ch 12 Life-Cycle Financial Planning - Ready-To-Build.xlsx

         Ch 13 International Parity - Ready-To-Build.xlsx

         Ch 14 Swaps - Ready-To-Build.xlsx

         Ch 15 Option Payoffs and Profits - Ready-To-Build.xlsx

         Ch 16 Option Trading Strategies - Ready-To-Build.xlsx

         Ch 17 Put-Call Parity - Ready-To-Build.xlsx

         Ch 18 Binomial Option Pricing - Ready-To-Build.xlsx

         Ch 19 Black-Scholes Option Pricing - Ready-To-Build.xlsx

         Ch 20 Futures - Ready-To-Build.xlsx

         Ch 21 Pricing By Simulation - Ready-To-Build.xlsx

         Ch 22 Corporate Bonds - Ready-To-Build.xlsx

The New copy of this book will include any supplemental materials advertised. Please check the title of the book to determine if it should include any access cards, study guides, lab manuals, CDs, etc.

The Used, Rental and eBook copies of this book are not guaranteed to include any supplemental materials. Typically, only the book itself is included. This is true even if the title states it includes any access cards, study guides, lab manuals, CDs, etc.