Table Of Contents:
Preface xv
Preface to the First Edition xvii
I Corporate Finance Models 1(128)

Basic Financial Calculations 3(24)

Introduction 3(1)

Present Value (PV) and Net Present Value (NPV) 3(2)

The Internal Rate of Return (IRR) and Loan Tables 5(3)

Multiple Internal Rates of Return 8(3)

Flat Payment Schedules 11(1)

Future Values and Applications 12(2)

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

Continuous Compounding 18(9)

Exercises 22(5)

Calculating the Cost of Capitol 27(30)

Introduction 27(1)

The Gordon Dividend Model 27(4)

Calculating the Cost of Equity for Abbott Laboratories Using the Gordon Model 31(2)

Capitol Asset Pricing Model 33(2)

Using the Security Market Line (SML) to Calculate Abbott's Cost of Equity 35(2)

Calculating the Cost of Debt 37(1)

Calculating Abbott's Cost of Debt 38(3)

Weighted Average Cost of Capitol (WACC) 41(1)

When the Models Don't Work 42(4)

Conclusion 46(11)

Exercises 47(2)

A Rule of Thumb for Calculating Debt Betas 49(2)

Why Is β Such a Good Measure of Risk? Portfolio β versus Individual Stock β 51(1)

Getting Data from the Internet 52(5)

Financial Statement Modeling 57(32)

Overview 57(1)

How Financial Models Work: Theory and an Initial Example 57(7)

Free Cash Flow (FCF): Measuring the Cash Produced by the Business 64(4)

Using the FCF to Value the Firm and Its Equity 68(1)

Some Notes on the Valuation Procedure 69(2)

Sensitivity Analysis 71(1)

Debt as a Plug 72(3)

Incorporating a Target Debt/Equity Ratio into a Pro Forma 75(1)

Project Finance: Debt Repayment Schedules 76(4)

Conclusion 80(9)

Exercises 81(2)

Calculating the Free Cash Flows When There Are Negative Profits 83(1)

Accelerated Depreciation in Pro Forma Models 84(5)

Using Financial Statement Models for Valuation 89(12)

Overview 89(1)

Farmers Bagels---Some Background 89(2)

Building a Financial Model 91(5)

Deriving the Free Cash Flows (FCF) for Farmers Bagels 96(1)

Calculating Farmers' Weighted Average Cost of Capitol 97(1)

Sensitivity Analysis 98(1)

Conclusion 99(2)

Exercises 100(1)

The Financial Analysis of Leasing 101(14)

Introduction 101(1)

A Simple Example 101(2)

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

The Lessor's Problem: Calculating the Highest Acceptable Lease Rental 106(3)

Asset Residual Value and Other Considerations 109(6)

Exercises 110(1)

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

The Financial Analysis of Leveraged Leases 115(14)

Introduction 115(1)

An Example 116(3)

Analyzing the Cash Flows by NPV or IRR 119(1)

What Does the IRR Mean? 120(3)

Accounting for Leveraged Leases: The ``Multiple-Phases Method'' 123(3)

Comparing the MPM Rate of Return with the IRR 126(3)

Exercises 127(2)
II Portfolio Models 129(100)

Portfolio Models---Introduction 131(20)

Overview 131(1)

A Simple Two-Asset Example 131(4)

Calculating Portfolio Means and Variances 135(2)

Portfolio Mean and Variance---The General Case 137(4)

Efficient Portfolios 141(2)

Conclusion 143(8)

Exercises 143(3)

Adjusting for Dividends 146(2)

Continuously Compounded versus Geometric Returns 148(3)

Calculating the Variance-Covariance Matrix 151(10)

Overview 151(1)

Using the Excess-Return Matrix in the Spreadsheet 152(1)

Illustration 153(1)

Other Ways of Calculating the Variance-Covariance Matrix 154(2)

The Single-Index Model 156(5)

Exercises 159(2)

Calculating Efficient Portfolios When There Are No Short-Sale Restrictions 161(24)

Overview 161(1)

Some Preliminary Definitions and Notation 161(2)

Some Theorems on Efficient Portfolios and the CAPM 163(5)

Calculating the Efficient Frontier: An Example 168(7)

Finding the Market Portfolio: The Capital Market Line (CML) 175(2)

The SML When There Is a Risk-Free Asset 177(8)

Exercises 178(1)

Appendix 179(6)

Estimating Betas and the Security Market Line 185(14)

Overview 185(1)

Testing the CAPM 185(3)

Testing the CAPM: General Rules 188(1)

Why Are the Results so Bad? Is the Market Portfolio Efficient? 188(1)

The Nonefficiency of the ``Market Portfolio'' 189(6)

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

Does the CAPM Have Any Uses? 197(2)

Exercise 197(2)

Efficient Portfolios without Short Sales 199(10)

Introduction 199(2)

A Numerical Example 201(3)

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

The VBA Program 206(2)

Conclusion 208(1)

Exercises 208(1)

Value at Risk (VaR) 209(20)

Overview 209(1)

A Very Simple Example 209(2)

Defining Quantiles in Excel 211(3)

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

Simulating Data---Bootstrapping 216(13)

Appendix: How to Bootstrap: Making a Bingo Card in Excel 219(10)
III Option-Pricing Models 229(132)

An Introduction to Options 231(22)

Basic Option Definitions and Terminology 231(3)

Some Examples 234(3)

Option Payoff and Profit Patterns 237(4)

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

Option Arbitrage Propositions 243(10)

Exercises 250(3)

The Binomial Option-Pricing Model 253(24)

Two-Date Binomial Pricing 253(1)

State Prices 254(2)

Multiperiod Binomial Model 256(6)

Pricing American Options Using the Binomial Pricing Model 262(2)

Programming the Binomial Option-Pricing Model in VBA 264(2)

American Put Pricing 266(4)

The Convergence of the Binomial Option-Pricing Model to the Black-Scholes Price 270(1)

Using the Binomial Model to Price Nonstandard Options: An Example 271(6)

Exercises 273(4)

The Lognormal Distribution 277(20)

Introduction 277(1)

What Do Stock Prices Look Like? 278(4)

Lognormal Price Distributions and Geometric Diffusions 282(3)

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

Simulating Lognormal Price Paths 288(3)

Technical Analysis 291(2)

Calculating the Parameters of the Lognormal Distribution from Stock Prices 293(4)

Exercises 295(2)

The Black-Scholes Model 297(14)

Introduction 297(1)

The Black-Scholes Model 297(2)

Using VBA to Define a Black-Scholes Pricing Function 299(1)

Calculating the Implied Volatility 300(2)

A VBA Function to Find the Implied Variance 302(2)

Bang for the Buck with Options 304(7)

Exercises 307(4)

Portfolio Insurance 311(18)

Introduction: Insuring Stock Returns 311(1)

Portfolio Insurance on More Complicated Assets 312(2)

An Example 314(3)

Some Properties of Portfolio Insurance 317(2)

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

Insuring Total Portfolio Returns 322(4)

Implicit Puts and Asset Values 326(3)

Exercises 327(2)

Real Options 329(14)

An Introduction 329(1)

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

The Abandonment Option 333(5)

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

Conclusion 341(2)

Exercises 341(2)

Early Exercise Boundaries 343(18)

Introduction 343(1)

Why Would You Want to Exercise a Put Early? 343(2)

The Early Exercise Boundary for Puts 345(2)

A VBA Program to Find the Put Early Exercise Boundary 347(3)

A Note on Dividend-Equivalent Price Processes 350(2)

Early Exercise of American Calls: A Numerical Example 352(2)

A VBA Program for the Call Early Exercise Boundary with Dividends 354(7)

Exercises 357(1)

Appendix: Proof 358(3)
IV Bonds and Duration 361(68)

Duration 363(18)

Introduction 363(1)

Two Examples 363(3)

What Does Duration Mean? 366(3)

Duration Patterns 369(1)

The Duration of a Bond with Uneven Payments 370(6)

Nonflat Term Structures and Duration 376(5)

Exercises 378(3)

Immunization Strategies 381(12)

Introduction 381(1)

A Basic Simple Model of Immunization 381(2)

A Numerical Example 383(4)

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

Building a Better Mousetrap 389(4)

Exercises 391(2)

Modeling the Term Structure 393(8)

Introduction 393(1)

Polynomial Regressions 393(3)

What Happens to the Coefficients over Time? 396(2)

Academic Term-Structure Models 398(3)

Calculating Default-Adjusted Expected Bond Returns 401(16)

Introduction 401(2)

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

A Multiperiod, Multistate Markov Chain Problem 404(4)

A Numerical Example 408(2)

Transition Matrices and Recovery Percentages: What Do We Know? 410(3)

Adjusting the Expected Return for Uneven Periods 413(1)

Computing Bond Betas 414(3)

Exercises 415(2)

Duration and the Cheapest-to-Deliver Problem for Treasury Bond Futures Contracts 417(12)

Introduction 417(1)

A General Model of the CTD 417(2)

The Extremal Coupon as a General Solution for the CTD 419(1)

Choosing the Optimal Maturity for CTD: The Case of Flat Term Structure 419(2)

Using Excel to Plot the CTD and Duration 421(6)

Conclusion 427(2)
V Technical Considerations 429(62)

Random Numbers 431(12)

Introduction 431(1)

Testing the Excel Random-Number Generator 432(4)

Generating Normally Distributed Random Numbers 436(7)

Exercises 441(2)

Data Tables 443(6)

Introduction 443(1)

An Example 443(1)

Setting Up a Data Table 444(1)

Building a Two-Dimensional Data Table 445(2)

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

Excel Data Tables Are Arrays 448(1)

Exercises 448(1)

Matrices 449(8)

Introduction 449(1)

Matrix Operations 450(3)

Matrix Inverses 453(1)

Solving Systems of Simultaneous Linear Equations 454(3)

Exercises 456(1)

The Gauss-Seidel Method 457(4)

Overview 457(1)

A Simple Example 457(1)

A More Concise Solution 458(1)

Conclusion 459(2)

Exercise 459(2)

Excel Functions 461(18)

Introduction 461(1)

Financial Functions 461(4)

Array Functions 465(4)

Statistical Functions 469(2)

Doing Regressions with Excel 471(4)

Conditional Functions 475(1)

Large( ) and Rank( ), Percentile( ), and Percentrank( ) 476(3)

Some Excel Hints 479(12)

Introduction 479(1)

Fast Copy: Filling in Data Next to Filled-In Column 479(1)

Multiline Cells 480(1)

Test Functions in Excel 481(1)

Graph Titles That Update 481(3)

Putting Greek Symbols in Cells 484(1)

Superscripts and Subscripts 485(1)

Named Cells 486(1)

Hiding Cells 487(4)
VI Introduction to Visual Basic for Applications 491(112)

User-Defined Functions with Visual Basic for Applications 493(26)

Overview 493(1)

Using the VBA Editor to Build a User-Defined Function 493(3)

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

Fixing Mistakes in VBA 499(3)

Conditional Execution: Using If Statements in VBA Functions 502(4)

The Select Case Statement 506(3)

Using Excel Functions in VBA 509(1)

Using User-Defined Functions in User-Defined Functions 510(9)

Exercises 512(4)

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

Types and Loops 519(20)

Introduction 519(1)

Using Types 519(2)

Variables and Variable Types 521(4)

The Boolean and Comparison Operators 525(2)

Loops 527(12)

Exercises 535(4)

Macros and User Interaction 539(18)

Introduction 539(1)

Macro Subroutines 539(5)

User Output and the MsgBox Function 544(3)

User Input and the InputBox Function 547(2)

Modules 549(8)

Exercises 551(6)

Arrays 557(24)

Introduction 557(1)

Simple Arrays 557(4)

Multidimensional Arrays 561(2)

Dynamic Arrays and the ReDim Statement 563(6)

Array Assignment 569(2)

Variants Containing an Array 571(2)

Arrays as Parameters to Functions 573(8)

Exercises 579(2)

Objects 581(22)

Introduction 581(1)

Worksheet Objects: An Introduction 581(2)

The Range Object 583(4)

The With Statement 587(1)

Collections 588(5)

Names 593(2)

Using the Object Browser 595(8)

Exercises 597(4)

Appendix: Excel Object Hierarchy 601(2)
References 603(8)
Index 611