Financial Modeling
CONTENTS
PREFACE
I CORPORATE FINANCE MODELS
1 Financial Statement Modeling
1.1 Overview
1.2 How Financial Models Work: Theory and an Initial Example
1.2.1 The "Plug"
1.2.2 The Starting Balance Sheet
1.2.3 An Example
1.2.4 Using the Model for Financial Predictions
1.3 A Variation on the Model: Including Short-Term Financial Assets
1.4 Incorporating a Target Debt/Equity Ratio into a Pro Forma
1.5 Credit Analysis: Debt Repayment Schedules
1.6 Free Cash Flow: Measuring the Cash Produced by the Business
1.6.1 An Illustration
Exercises
2 Using Financial Statement Models for Valuation
2.1 Overview
2.2 Farmers Bagels
2.3 Building a Financial Model
2.3.1 Negative Debt
2.4 Deriving the Free Cash Flows (FCF) for Farmers Bagels
2.5 Valuing the Firm as an Unlevered Entity
2.5.1 The Terminal Value
2.6 The Effects of Leverage on the Valuation
2.6.1 Application to Farmers Bagels
2.6.2 Implementation
2.7 Some Sensitivity Analysis
2.7.1 Sensitivity of the Value to Sales Forecasts
2.7.2 Sensitivity to Cost Parameters
2.8 Summary
Exercises
3 The Financial Analysis of Leasing
3.1 Introduction
3.2 A Simple Example
3.3 Leasing and Firm Financing: The Equivalent-Loan Method
3.3.1 Why Did We Decide against the Lease?
3.4 The Lessor\\u0027s Problem: Calculating the Highest Acceptable Lease Rental
3.5 Asset Residual Value and Other Considerations
Exercises
Appendix: The Tax and Accounting Treatment of Leases
The Accounting Treatment of Leases
Reconciling the Tax and Accounting Treatments of Leases
4 The Financial Analysis of Leveraged Leases
4.1 Introduction
4.2 An Example
4.3 Analyzing the Cash Flows by NPV or IRR
4.4 What Does the IRR Mean?
4.4.1 The Leveraged Lease Example Revisited
4.5 Accounting for Leveraged Leases: The "Multiple-Phases Method"
4.5.1 Calculating the Multiple-Phases-Method Rate of Return
4.6 Comparing the MPM Rate of Return with the IRR
Exercises
II PORTFOLIO MODELS
5 Portfolio Models-Introduction
5.1 Overview
5.2 A Simple Two-Asset Example
5.3 Calculating Portfolio Means and Variances
5.4 Portfolio Mean and Variance: The General Case
5.5 Efficient Portfolios
Exercises
Appendix: Continuously Compounded versus Discrete Returns
6 Calculating the Variance-Covariance Matrix
6.1 Overview
6.2 Using the Excess-Return Matrix in the Spreadsheet
6.3 Illustration
6.4 Using an Excel Macro
6.5 The Single-Index Model
Exercises
7 Calculating Efficient Portfolios When There Are No Short-Sale Restrictions
7.1 Overview
7.2 Some Preliminary Definitions and Notation
7.3 Some Theorems on Efficient Portfolios and the CAPM
7.4 Calculating the Efficient Frontier: An Example
7.4.1 Calculating Two Envelope Portfolios
7.4.2 Calculating the Efficient Frontier
7.5 Finding the Market Portfolio: The Capital Market Line (CML)
7.6 The SML When There Is a Risk-Free Asset
Exercises
Appendix
8 Estimating Betas and the Security Market Line
8.1 Introduction
8.2 Testing the CAPM
8.3 Testing the CAPM: General Rules
8.4 Why Are the Results So Bad? Is the "Market" Portfolio Efficient?
8.5 The Nonefficiency of the "Market Portfolio""
8.5.1 Is the SP500 Not Efficient?
8.5.2 Redoing the SML with an Efficient Portfolio
8.5.3 Rederiving the SML ...
8.6 So What\\u0027s the Real Market Portfolio? How Can We Test the CAPM?
8.7 Does the CAPM Have Any Uses?
Exercise
9 Efficient Portfolios without Short Sales
9.1 Introduction
9.2 A Numerical Example
9.3 The Efficient Frontier with Short-Sale Restrictions
9.4 The VBA Program
9.5 Conclusion
Exercises
III OPTION PRICING MODELS
10 An Introduction to Options
10.1 Basic Option Definitions and Terminology
10.1.1 Writing Options versus Purchasing Options: Cash Flows
10.2 Some Examples
10.3 Option Payoff and Profit Patterns
10.3.1 Stock Profit Patterns
10.3.1.1 Payoff Pattern from a Purchased Stock
10.3.1.2 Payoff from the Short Sale of a Stock
10.3.1.3 Graphing Stock Profit Patterns
10.3.2 Call Option Profit Patterns
10.3.2.1 Payoff Pattern from a Purchased Call
10.3.2.2 Payoff Pattern from a Written Call
10.3.3 Put Option Profit Patterns
10.3.3.1 Payoff Pattern from a Purchased Put
10.3.3.2 Payoff Pattern from a Written Put
10.4 Option Strategies: Payoffs from Portfolios of Options and Stocks
10.4.1 The Protective Put
10.4.2 Spreads
10.5 Option Arbitrage Propositions
Exercises
11 The Binomial Option-Pricing Model
11.1 Two-Date Binomial Pricing
11.2 State Prices
11.3 Multiperiod Binomial Model
11.3.1 Extending the Binomial Pricing Model to Many Periods
11.3.2 Do You Really Have to Price Everything Backward?
11.4 Pricing American Options Using the Binomial Pricing Model
11.5 Programming the Binomial Option-Pricing Model in VBA
11.5.1 American Put Pricing
Exercises
12 The Lognormal Distribution
12.1 Introduction
12.2 What Do Stock Prices Look Like?
12.2.1 Reasonable Stock Properties and Stock Price Paths
12.3 Lognormal Price Distributions and Geometric Diffusions
12.4 What Does the Lognormal Distribution Look Like?
12.5 Simulating Lognormal Price Paths
12.6 Technical Analysis
12.7 Calculating the Parameters of the Lognormal Distribution from Stock Prices
Exercises
13 The Black-Scholes Model
13.1 Introduction
13.2 The Black-Scholes Model
13.2.1 Implementing the Black-Scholes Formulas in a Spreadsheet
13.3 Using VBA to Define a Black-Scholes Pricing Function
13.3.1 Pricing Puts
13.3.2 Using These Functions in an Excel Spreadsheet
13.4 Calculating the Implied Volatility
13.5 A VBA Function to Find the Implied Variance
Exercises
14 Portfolio Insurance
14.1 Overview: Insuring Stock Returns
14.2 Portfolio Insurance on More Complicated Assets
14.3 An Example
14.4 Some Properties of Portfolio Insurance
14.5 What Do Portfolio Insurance Strategies Look Like? A Simulation Program
14.5.1 Some Examples
14.6 Insuring Total Portfolio Returns
14.6.1 What Is the Effect of Raising the Insurance Level? Can You Insure for More Than Your Initial ...
14.7 Implicit Puts and Asset Values
Exercises
IV BONDS AND DURATION
15 Duration
15.1 Introduction
15.2 Two Examples
15.2.1 Using an Excel Formula
15.3 What Does Duration Mean?
15.3.1 Duration as the Weighted Average of the Bond\\u0027s Payments
15.3.2 Duration as the Bond\\u0027s Price Elasticity with Respect to Its Discount Rate
15.3.3 Babcock\\u0027s Formula: Duration as the Convex Combination of Bond Yields
15.4 Duration Patterns
15.5 The Duration of a Bond with Uneven Payments
15.5.1 Duration of a Bond with Uneven Payments
15.5.2 Calculating the YTM for Uneven Periods
15.5.3 Using Excel\\u0027s XIRR Function to Compute the YTM for Uneven Payments
15.5.4 Calculating the YTM for Uneven Payments Using a VBA Program
15.6 Nonflat Term Structures and Duration
Exercises
16 Immunization Strategies
16.1 Introduction
16.2 A Basic Simple Model of Immunization
16.3 A Numerical Example
16.4 Convexity: A Continuation of Our Immunization Experiment
16.5 Building a Better Mousetrap
Exercises
17 Calculating Default-Adjusted Expected Bond Returns
17.1 Introduction
17.1.1 Some Preliminaries
17.2 Calculating the Expected Return in a One-Period Framework
17.3 A Multiperiod, Multistate Markov Chain Problem
17.3.1 The Multiperiod Transition Matrix
17.3.2 The Bond Payoff Vector
17.4 A Numerical Example
17.4.1 How to Calculate the Expected Bond Payoffs
17.5 Transition Matrices and Recovery Percentages: What Do We Know?
17.6 Adjusting the Expected Return for Uneven Periods
17.7 Computing Bond Betas
Exercises
18 Duration and the Cheapest-to-Deliver Problem for Treasury Bond Futures Contracts
18.1 Introduction
18.2 A General Model of the CTD
18.2.1 Nonoption Forward Contracts
18.3 The Extremal Coupon as a General Solution for the CTD
18.4 Choosing the Optimal Maturity for CTD: The Case of Flat Term Structure
18.5 Using Excel to Plot the CTD and Duration
18.5.1 The Excel Spreadsheet
18.5.2 The Numerical Simulation Results
18.6 Conclusion
V TECHNICAL CONSIDERATIONS
19 Generating Random Numbers
19.1 Introduction
19.2 Testing the Excel Random-Number Generator
19.3 Generating Normally Distributed Random Numbers
Exercises
20 Data Table Commands
20.1 Introduction
20.2 An Example
20.3 Setting Up a Data Table
20.4 Building a Two-Dimensional Data Table
20.5 An Aesthetic Note: Hiding the Formula Cells
20.6 Excel Data Tables Are Arrays
21 Matrices
21.1 Introduction
21.2 Matrix Operations
21.2.1 Multiplication by a Scalar
21.2.2 Addition
21.2.3 Transposition
21.2.4 Multiplication of Matrices
21.3 Matrix Inverses
21.4 Solving Systems of Simultaneous Linear Equations
Exercises
22 The Gauss-Seidel Method
22.1 Overview
22.2 A Simple Example
22.3 A More Concise Solution
22.4 Conclusion
Exercise
23 Excel Functions
23.1 Introduction
23.2 Financial Functions
23.2.1 NPV()
23.2.2 IRR()
23.2.3 PV()
23.2.4 PMT()
23.3 Array Functions
23.3.1 Frequency()
23.3.2 Index()
23.4 Statistical Functions
23.5 Doing Regressions with Excel
23.5.1 Using Linest()
23.5.2 Using Index() to Pick Out Individual Entries of Linest()
23.5.3 Multiple Regressions
23.6 Conditional Functions
VI INTRODUCTION TO VISUAL BASIC FOR APPLICATIONS
24 Programming in Microsoft Excel
24.1 Overview
24.2 Why Macros Are Necessary
24.3 Recording a Macro
24.4 Editing a Macro
24.5 Running a Macro (Part 1)
24.6 Making a Macro Globally Available
24.6.1 Creating the Personal Macro Workbook
24.6.2 Moving a Macro to the Personal Macro Workbook
24.7 Running a Macro (Part 2)
24.7.1 Using the Menu System
24.7.2 Using the Tools Menu
24.7.3 Using an Attached Graphical Object or a Button on the Worksheet
24.7.4 Using an Attached Button on a Toolbar
24.8 More Information on Our First Macro
25 Introduction to User-Defined Functions in Visual Basic for Application
25.1 Overview
25.2 A Simple Example
25.2.1 The Parts of a Function
25.3 Using User-Defined Functions
25.3.1 Defining a Function
25.3.2 The If, ElseIf, and End If Statement
25.3.3 The CVErr Function
25.3.4 Named Constants
25.3.5 Going Around in Circles or Loops
25.3.6 Moving Information in and out of the Worksheet
25.4 Excel Objects: A Short Introduction
25.4.1 The With Statement
25.4.2 Using the Object Browser
25.4.3 Adding a New Menu Bar
25.4.4 Changing the Default Tool Tip for a Button
25.5 The Ins and Outs of Array Manipulation in VBA17
25.5.1 Using the Excel Array Manipulation Routines
25.5.2 Returning Constants from a Matrix Operation
25.5.3 Performing Matrix Operations on Data That Originates in VBA Arrays
25.6 Conclusion
REFERENCES
General Finance
Chapters 1-2: Financial Statement Modeling
Chapters 3-4: Leasing
Chapters 5-9: Portfolio
Chapters 10-14: Options
Chapters 15-17: Duration
Chapter 18: Cheapest to Deliver
Chapter 19: Random Numbers
INDEX
A
B
C
D
E
F
G
H
I
K
L
M
N
O
P
R
S
T
U
V
W
Y
Z