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.
Features
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.
- 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.
New to this Edition
The Fifth Edition adds great new investments content:
- 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.
Table of Contents
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
Your Basket