Table Of Contents:
Preface xxiii
Preface to the Second Edition xxix
Preface to the First Edition xxxi

I. Corporate Finance Models 1(236)

Basic Financial Calculations 3(36)

Overview 3(1)

Present Value and Net Present Value 4(5)

Internal Rate of Return and Loan Tables 9(6)

Multiple Internal Rates of Return 15(2)

Flat Payment Schedules 17(2)

Future Values and Applications 19(2)

A Pension Problem---Complicating the Future-Value Problem 21(4)

Continuous Compounding 25(5)

Discounting Using Dated Cash Flows 30(9)

Exercises 31(8)

Calculating the Cost of Capital 39(64)

Overview 39(1)

The Gordon Dividend Model 40(4)

Adjusting the Gordon Model to Account for All Cash Flows to Equity 44(4)

``Supernormal Growth'' and the Gordon Model 48(4)

Using the Capital Asset Pricing Model to Determine the Cost of Equity rE 52(7)

Using the Security Market Line to Calculate Intel's Cost of Equity 59(3)

Three Approaches to Computing the Expected Return on the Market E(rM) 62(4)

Calculating the Cost of Debt 66(4)

Computing the WACC: Three Cases 70(1)

Computing the WACC for Kraft Corporation 70(3)

Computing the WACC for Tyson Foods 73(4)

Computing the WACC for Cascade Corporation 77(4)

When the Models Don't Work 81(5)

Conclusion 86(17)

Exercises 87(5)

Appendix 1: Why Is β a Good Measurement of Risk? Portfolio β versus Individual Stock B 92(3)

Appendix 2: Getting Data from the Internet 95(8)

Financial Statement Modeling 103(32)

Overview 103(1)

How Financial Models Work: Theory and an Initial Example 103(8)

Free Cash Flow: Measuring the Cash Produced by the Business 111(2)

Using the Free Cash Flow to Value the Firm and Its Equity 113(2)

Some Notes on the Valuation Procedure 115(2)

Sensitivity Analysis 117(1)

Debt as a Plug 118(3)

Incorporating a Target Debt/Equity Ration into a Pro Forma 121(1)

Project Finance: Debt Repayment Schedules 122(3)

Calculating the Return on Equity 125(2)

Conclusion 127(8)

Exercises 127(3)

Appendix 1: Calculating the Free Cash Flows When There Are Negative Profits 130(1)

Appendix 2: Accelerated Depreciation in Pro Forma Models 131(4)

Building a Financial Model: The Case of PPG Corporation 135(42)

Overview 135(1)

PPG Financial Statements, 1991-2000 136(2)

Analyzing the Financial Statements 138(4)

A Model for PPG 142(4)

Back to Treasury Stock and the Dividend 146(1)

The Whole Model 147(1)

Free Cash Flows and Valuation 148(3)

What Is PPG's Dividend Policy? 151(4)

Modeling PPG's Dividend Policy 155(1)

Computing PPG's Cost of Equity rE and Its Cost of Debt rD 156(4)

What Is PPG's Weighted Average Cost of Capital? 160(1)

Back to the Valuation---Sensitivity Analyses 161(16)

Exercises 163(1)

Appendix: Some Accounting Issues 163(14)

Bank Valuation 177(26)

Overview 177(1)

Analyzing Bank Balance Sheets 177(8)

The Bank's Free Cash Flow 185(3)

Large Bank Corporation Buys Small Bank: A Valuation Example 188(5)

Calculating the Exchange Ratio 193(1)

Alternatives to FCF Valuation of Financial Institutions 194(1)

Valuing a Bank by Using Capital Adequacy Ratios 194(2)

Using P/Es to Value a Bank Acquisition: First Federal Savings Bank 196(7)

The Financial Analysis of Leasing 203(16)

Overview 203(1)

A Simple Example 203(2)

Leasing and Firm Financing: The Equivalent-Loan Method 205(3)

The Lessor's Problem: Calculating the Highest Acceptable Lease Rental 208(4)

Asset Residual Value and Other Considerations 212(2)

Summary 214(5)

Exercises 214(1)

Appendix: The Tax and Accounting Treatment of Leases 215(4)

The Financial Analysis of Leveraged Leases 219(18)

Overview 219(1)

An Example 220(4)

Analyzing the Cash Flows by NPV or IRR 224(2)

What Does the IRR Mean? 226(4)

Accounting for Leveraged Leases: The ``Multiple Phases Method'' 230(7)

Comparing the MPM Rate of Return with the IRR 234(1)

Summary 234(1)

Exercises 235(2)

II. Portfolio Models 237(182)

Portfolio Models---Introduction 239(22)

Overview 239(1)

Computing Returns for Walmart and Target 239(6)

Calculating Portfolio Means and Variances 245(1)

Portfolio Means and Variances---The General Case 246(4)

Efficient Portfolios 250(2)

Conclusion 252(9)

Exercises 252(3)

Appendix 1: Adjusting for Dividends 255(2)

Appendix 2: Continuously Compounded versus Geometric Returns 257(4)

Calculating Efficient Portfolios When There Are No Short-Sale Restrictions 261(30)

Overview 261(1)

Some Preliminary Definitions and Notation 261(2)

Some Theorems on Efficient Portfolios and CAPM 263(5)

Calculating the Efficient Frontier: An Example 268(4)

Three Notes on the Optimization Procedure 272(4)

Finding Efficient Portfolios in One Step 276(2)

Finding the Market Portfolio: The Capital Market Line 278(2)

Testing the SML: Implementing Propositions 3-5 280(3)

Summary 283(8)

Exercises 283(2)

Appendix 285(6)

Calculating the Variance-Covariance Matrix 291(26)

Overview 291(1)

Computing the Sample Variance-Covariance Matrix 291(4)

Should We Divide by M or by M - 1 ? Excel versus Statistics 295(2)

Alternate Methods for Computing the Sample Variance-Covariance Matrix 297(2)

Computing the Global Minimum Variance Portfolio 299(2)

Computing an Efficient Portfolio 301(3)

Alternatives to the Sample Variance-Covariance: The Single-Index Model 304(2)

Alternatives to the Sample Variance-Covariance: Constant Correlation 306(2)

Shrinkage Methods 308(2)

Alternatives to the Variance-Covariance Matrix: Impact on the Minimum-Variance Portfolio and the Optimal Portfolio 310(5)

Summary 315(2)

Exercises 315(2)

Estimating Betas and the Security Market Line 317(18)

Overview 317(3)

Testing the Security Market Line 320(4)

Did We Learn Something? 324(2)

The Inefficiency of the ``Market Portfolio'' 326(3)

So What's the Real Market Portfolio? How Can We Test the CAPM? 329(1)

Using Excess Returns 330(2)

Does the CAPM Have Any Uses? 332(3)

Exercises 333(2)

Efficient Portfolios without Short Sales 335(14)

Overview 335(1)

A Numerical Example 336(5)

The Efficient Frontier with Short-Sale Restrictions 341(2)

A VBA Program to Create the Efficient Frontier 343(2)

Other Position Restrictions 345(2)

Conclusion 347(2)

Exercises 347(2)

The Black-Litterman Approach to Portfolio Optimization 349(22)

Overview 349(2)

A Naive Problem 351(6)

Black and Litterman's Solution to the Optimization Problem 357(1)

Black-Litterman Step 1: What Does the Market Think? 357(3)

Black-Litterman Step 2: Introducing Opinions---What Does Joanna Think? 360(5)

Implementing Black-Litterman on an International Portfolio 365(3)

Summary 368(3)

Exercises 369(2)

Event Studies 371(26)

Overview 371(1)

Outline of an Event Study 371(4)

An Initial Event Study: Procter & Gamble Buys Gillette 375(7)

A Fuller Event Study: Impact of Earnings Announcements on Stock Prices 382(8)

Using a Two-Factor Model of Returns for an Event Study 390(4)

Using Excel's Offset Function to Locate a Regression in a Data Set 394(2)

Conclusion 396(1)

Value at Risk 397(22)

Overview 397(1)

A Really Simple Example 397(2)

Defining Quantiles in Excel 399(3)

A Three-Asset Problem: The Importance of the Variance-Covariance Matrix 402(2)

Simulating Data---Bootstrapping 404(15)

Appendix: How to Bootstrap: Making a Bingo Card in Excel 409(10)

III. Option-Pricing Models 419(250)

An Introduction to Options 421(22)

Overview 421(1)

Basic Option Definitions and Terminology 421(3)

Some Examples 424(2)

Option Payoff and Profit Patterns 426(4)

Option Strategies: Payoffs from Portfolios of Options and Stocks 430(2)

Option Arbitrage Propositions 432(7)

Summary 439(4)

Exercises 439(4)

The Binomial Option-Pricing Model 443(40)

Overview 443(1)

Two-Date Binomial Pricing 443(2)

State Prices 445(4)

The Multiperiod Binomial Model 449(6)

Pricing American Options Using the Binomial Pricing Model 455(3)

Programming the Binomial Option-Pricing Model in VBA 458(5)

Convergence of Binomial Pricing in the Black-Scholes Price 463(3)

Using the Binomial Model to Price Employee Stock Options 466(10)

Using the Binomial Model to Price Nonstandard Options: An Example 476(2)

Summary 478(5)

Exercises 478(5)

The Lognormal Distribution 483(26)

Overview 483(1)

What Do Stock Prices Look Like? 484(8)

Lognormal Price Distributions and Geometric Diffusions 492(3)

What Does the Lognormal Distribution Look Like? 495(3)

Simulating Lognormal Price Paths 498(4)

Technical Analysis 502(1)

Calculating the Parameters of the Lognormal Distribution from Stock Prices 503(2)

Summary 505(4)

Exercises 505(4)

The Black-Scholes Model 509(40)

Overview 509(1)

The Black-Scholes Model 509(2)

Using VBA to Define a Black-Scholes Pricing Function 511(2)

Calculating the Implied Volatility 513(4)

A VBA Function to Find the Implied Variance 517(3)

Dividend Adjustments to the Black-Scholes 520(5)

Using the Black-Scholes Formula to Price Structured Securities 525(14)

Bang for the Buck with Options 539(2)

The Black (1976) Model for Bond Option Valuation 541(3)

Summary 544(5)

Exercises 544(5)

Option Greeks 549(28)

Overview 549(1)

Defining and Computing the Greeks 550(5)

Delta Hedging a Call 555(9)

Hedging a Collar 564(10)

Summary 574(3)

Exercises 575(2)

Portfolio Insurance 577(20)

Overview 577(1)

Portfolio Insurance on More Complicated Assets 578(2)

An Example 580(4)

Some Properties of Portfolio Insurance 584(1)

What Do Portfolio Insurance Strategies Look Like? A Simulation 585(3)

Insuring Total Portfolio Returns 588(4)

Implicit Puts and Asset Values 592(1)

Summary 593(4)

Exercises 594(3)

An Introduction of Monte Carlo Methods 597(16)

Overview 597(1)

Computing π Using Monte Carlo 597(5)

Writing a VBA Program 602(2)

Another Monte Carlo Problem: Investment and Retirement 604(3)

A Monte Carlo Simulation of the Investment Problem 607(3)

Summary 610(3)

Exercises 610(3)

Using Monte Carlo Methods for Option Pricing 613(36)

Overview 613(1)

State Prices, Probabilities, and Risk Neutrality 613(2)

Pricing a Plain-Vanilla Call Using Monte Carlo Methods 615(3)

Monte Carlo Plain-Vanilla Call Pricing Converges to Black-Scholes 618(7)

Pricing Asian Options 625(8)

Pricing Asian Options with a VBA Program 633(5)

Pricing Barrier Options with Monte Carlo 638(4)

Using VBA and Monte Carlo to Price a Barrier Option 642(4)

Summary 646(3)

Exercises 646(3)

Real Options 649(20)

Overview 649(1)

A Simple Example of the Option to Expand 650(3)

The Abandonment Option 653(6)

Valuing the Abandonment Option as a Series of Puts 659(3)

Valuing a Biotechnology Project 662(5)

Conclusion 667(2)

Exercises 667(2)

IV. Bonds 669(74)

Duration 671(22)

Overview 671(1)

Two Examples 671(3)

What Does Duration Mean? 674(4)

Duration Patterns 678(1)

The Duration of a Bond with Uneven Payments 679(8)

Nonflat Term Structures and Duration 687(2)

Summary 689(4)

Exercises 689(4)

Immunization Strategies 693(12)

Overview 693(1)

A Basic Simple Immunization Model 693(2)

A Numerical Example 695(3)

Convexity: A Continuation of Our Immunization Experiment 698(2)

Building a Better Mousetrap 700(4)

Summary 704(1)

Exercises 704(1)

Modeling the Term Structure 705(14)

Overview 705(1)

An Initial Example 705(5)

Description of the Data 710(3)

The Treasury Yield Curve 713(2)

Computing Par Yields from a Zero-Coupon Yield Curve 715(1)

Summary 716(3)

Exercises 717(2)

Calculating Default-Adjusted Expected Bond Returns 719(24)

Overview 719(2)

Calculating the Expected Bond Return in a One-Period Framework 721(1)

Calculating the Expected Bond Return in a Multiperiod Framework 722(4)

A Numerical Example 726(2)

Experimenting with the Example 728(2)

Computing the Bond Expected Return for an Actual Bond 730(4)

Semiannual Transition Matrices 734(3)

Computing Bond Beta 737(2)

Summary 739(4)

Exercises 740(3)

V. Technical Considerations 743(122)

Generating Random Numbers 745(20)

Overview 745(1)

Rand() and Rnd: The Excel and VBA Random-Number Generators 746(3)

Testing Random-Number Generators 749(5)

Generating Normally Distributed Random Numbers 754(8)

Summary 762(3)

Exercises 762(3)

Data Tables 765(10)

Overview 765(1)

An Example 765(1)

Setting Up a Data Table 766(2)

Building a Two-Dimensional Data Table 768(1)

An Aesthetic Note: Hiding the Formula Cells 769(1)

Excel Data Tables Are Arrays 770(5)

Exercises 771(4)

Matrices 775(10)

Overview 775(1)

Matrix Operations 776(3)

Matrix Inverses 779(2)

Solving Systems of Simultaneous Linear Equations 781(4)

Exercises 782(3)

The Gauss-Seidel Method 785(4)

Overview 785(1)

A Simple Example 785(1)

A More Concise Solution 786(1)

Conclusion 787(2)

Exercises 787(2)

Excel Functions 789(36)

Overview 789(1)

Financial Functions 789(7)

Dates and Date Functions 796(6)

The Functions XIRR and XNPV 802(3)

Statistical Functions 805(3)

Doing Regressions with Excel 808(7)

Conditional Functions 815(1)

Large and Rank, Percentile, and Percentrank 816(1)

Count, CountA, CountIF 817(2)

Boolean Functions 819(2)

Offset 821(4)

Using Array Functions and Formulas 825(16)

Overview 825(1)

Some Built-in Array Functions 825(5)

Homemade Array Functions 830(3)

Array Formulas with Matrices 833(8)

Exercises 838(3)

Some Excel Hints 841(24)

Overview 841(1)

Fast Copy: Filling in Data Next to a Filled-in Column 841(2)

Multiline Cells 843(2)

Writing on Multiple Spreadsheets 845(2)

Text Functions in Excel 847(1)

Chart Titles That Update 847(3)

Getformula: A Useful Way of Annotating Spreadsheets 850(3)

Putting Greek Symbols in Cells 853(1)

Superscripts and Subscripts 854(2)

Named Cells 856(1)

Hiding Cells 857(2)

Formula Auditing 859(2)

Formulating Millions as Thousands 861(4)

VI. Introduction to Visual Basic for Applications 865(230)

User-Defined Functions with VBA 867(28)

Overview 867(1)

Using the VBA Editor to Build a User-Defined Function 867(5)

Providing Help for the User-Defined Functions in the Function Wizard 872(3)

Fixing Mistakes in VBA 875(2)

Conditional Execution: Using If Statements in VBA Functions 877(5)

The Select Case Statement 882(2)

Using Excel Functions in VBA 884(1)

Using User-Defined Functions in User-Defined Functions 885(10)

Exercises 888(4)

Appendix: Cell Errors in Excel and VBA 892(3)

Types and Loops 895(24)

Overview 895(1)

Using Types 895(2)

Variables and Variable Types 897(4)

Boolean and Comparison Operators 901(3)

Loops 904(9)

Summary 913(6)

Exercises 913(6)

Macros and User Interaction 919(22)

Overview 919(1)

Macro Subroutines 919(7)

User Output and the MsgBox Function 926(4)

User Input and the InputBox Function 930(2)

Modules 932(3)

Summary 935(6)

Exercises 935(6)

Arrays 941(34)

Overview 941(1)

Simple Arrays 941(5)

Multidimensional Arrays 946(2)

Dynamic Arrays and the ReDim Statement 948(11)

Array Assignment 959(1)

Variants Containing an Array 960(3)

Arrays as Parameters to Functions 963(8)

Summary 971(4)

Exercises 971(4)

Objects and Add-Ins 975(54)

Overview 975(1)

An Introduction to Worksheet Objects 975(4)

The Range Object 979(5)

The With Statement 984(1)

Collections 985(6)

Names 991(4)

Using the Object Browser 995(2)

References to External Functions in Excel 997(2)

References to External Functions in VBA 999(9)

Add-Ins and Integration 1008(6)

Summary 1014(15)

Exercises 1014(4)

Appendix 1: The Excel Object Model 1018(2)

Appendix 2: Extracts from the Help File for Some Methods 1020(9)

Information from the Web 1029(66)

Overview 1029(1)

Copy and Paste as a Simple Data-Acquisition Technique 1029(6)

Dynamic Web Queries 1035(6)

Web Queries: The iqy File 1041(6)

Parametric Web Pages 1047(2)

Web Queries: Parameters 1049(7)

Web Queries: CSV Files and Postprocessing 1056(3)

A VBA Application: Importing Price Data from Yahoo 1059(30)

Summary 1089(6)

Exercises 1089(1)

Appendix 1: Excerpts from the Help File 1090(3)

Appendix 2: The R1C1 Reference Style 1093(2)
References 1095(12)
Index 1107