Excel Modeling in Investments

Description

For courses in corporate finance or financial management at the undergraduate and graduate level.

 

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.

 

For courses in corporate finance or financial management at the undergraduate and graduate level.

 

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.

The Fifth Edition adds great new investments content:

 

• NEW. Market microstructure including:

• Compute the National Best Bid and Offer (NBBO)
• Determine how a set of market orders and limit orders will execute in a limit order book market vs. a call market
• Compute five alternative transaction cost measures
• Decompose transaction costs into six components
• Estimate the Probability of Informed Trade (PIN)

• NEW. Portfolio Performance Evaluation including the Jensen, Treynor, Sharp, and M-squared measures 

• NEW.Taxes and Retirement Savings, including taxable, IRA, 401k, and Roth accounts

• UPDATED. 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 to the present.

Present a user-friendly learning environment: Plain Vanilla Excel. Other books on the market teach students how to program using Visual Basic for Applications (VBA) or macros. By contrast, this text does nearly everything in plain vanilla Excel. This approach offers students a very intuitive, user-friendly environment that is accessible to all.

 

Focus on the implementation: Ready-to-Build Spreadsheets. The accompanying CD for this text provides ready-to-build spreadsheets for every chapter with step-by-step instructions. Since the CD provides instructions within each spreadsheet, students do not have to refer back to the text for each step. This approach allows students to concentrate on how to implement financial formulas and estimation.

 

Build students’ skills: From Simple Examples to Practical, Real-World Applications. The general approach of this text is to have students start with a simple example and then move on to completing full-scale, practical applications using real data.

 

Provide effective guidance: Essential Learning Conventions. In order to aid students as they move through the material, this text offers the following learning conventions:

• Time goes across the columns and variables go down rows. When something happens over time, each column represents 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.
• Color coding. A standard color scheme is used to clarify the structure of excel models.
• The Time Line 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.
• Symbolic notation is self-contained. Every spreadsheet that contains symbolic notation in the instruction boxes is self-contained.

 Provide targeted investments content, enabling students to:

• Perform either unconstrained or constrained portfolio optimization on any number of risky assets up to a maximum of 20 assets
• Immunize bond portfolios against interest rate risk by matching the durations of assets and liabilities, by matching both durations and convexities, or by matching cash flows
• Use simulation to price path-independent derivatives (e.g., European options, cash-or-nothing options, asset-or-nothing options) and path-dependent derivatives (e.g., Asian options, lookback options, and barrier options)—both with and without jumps
• Analyze trading strategies involving many options, stocks, bonds, and futures either holding to maturity or holding to any date before maturity—including a database of 50 trading strategies with bullish strategies, bearish strategies, high volatility strategies, low volatility strategies, combined directional and volatility strategies, and arbitrage strategies
• Price derivatives on alternative types of underlying assets, such as stocks, stock indexes, futures, and foreign currencies
• Determine margin calls and excess margin on futures contracts
• Translate bond pricing into alternative foreign currency values
• Compute the average of a N-step and a N-1-step binomial model in order to gain pricing accuracy
• See how the binomial model converges to the normal distribution
• Use current Trade and Quote (TAQ) data to compute the National Best Bid and Offer (NBBO), the quoted spread, the effective spread, and determine which exchange has the lowest cost of trading.

• The Fifth Edition adds great new investments content:

   

• NEW. Market microstructure including:

• Compute the National Best Bid and Offer (NBBO)
• Determine how a set of market orders and limit orders will execute in a limit order book market vs. a call market
• Compute five alternative transaction cost measures
• Decompose transaction costs into six components
• Estimate the Probability of Informed Trade (PIN)

• NEW. Portfolio Performance Evaluation including the Jensen, Treynor, Sharp, and M-squared measures 

• NEW.Taxes and Retirement Savings, including taxable, IRA, 401k, and Roth accounts

• UPDATED. 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 to the present.

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