Financial Modeling

by Benninga, Simon

• ISBN13: 9780262024372
• ISBN10: 0262024373
• Format: Hardcover
• Copyright: 1997-11-01
• Publisher: Mit Pr

Summary

Too often, finance courses stop short of making a connection between textbook finance and the problems of real-world business. Financial Modeling bridges this gap between theory and practice by providing a nuts-and-bolts guide to solving common financial models with spreadsheets. Simon Benninga takes the reader step by step through each model, showing how it can be solved using Microsoft Excel. In this sense, this is a finance "cookbook", providing recipes with lists of ingredients and instructions. Areas covered include the computation of corporate finance problems, standard portfolio problems, option pricing and applications, and duration and immunization. The author includes a set of chapters dealing with advanced techniqueS, including random number generation, matrix manipulation, and the Gauss-Seidel method. Although the reader should know enough about Excel to set up a simple spreadsheet, the author explains advanced Excel techniques such as functions, macros, the use of data tables, and VBA programming. The book comes with a disk containing Excel worksheets and solutions to end-of-chapter exercises.

Table of Contents

Preface	xv
I Corporate Finance Models	1	(64)
1 Financial Statement Modeling	3	(18)
1.1 Overview	3	(1)
1.2 How Financial Models Work: Theory and an Initial Example	3	(6)
1.3 A Variation on the Model: Including Short-Term Financial Assests	9	(4)
1.4 Incorporating a Target Debt Equity Ratio into a Pro Forma	13	(1)
1.5 Credit Analysis: Debt Repayment Schedules	14	(4)
1.6 Free Cash Flow: Measuring the Cash Produced by the Business	18	(2)
Exercises	20	(1)
2 Using Financial Statement Models for Valuation	21	(16)
2.1 Overview	21	(1)
2.2 Farmers Bagels	21	(2)
2.3 Building a Financial Model	23	(4)
2.4 Deriving the Free Cash Flows (FCF) for Farmers Bagels	27	(2)
2.5 Valuing the Firm as an Unlevered Entity	29	(1)
2.6 The Effects of Leverage on the Valuation	30	(4)
2.7 Some Sensitivity Analysis	34	(1)
2.8 Summary	35	(1)
Exercises	36	(1)
3 The Financial Analysis of Leasing	37	(14)
3.1 Introduction	37	(1)
3.2 A Simple Example	37	(2)
3.3 Leasing and Firm Financing: The Equivalent-Loan Method	39	(3)
3.4 The Lessor's Problem: Calculating the Highest Acceptable Lease Rental	42	(2)
3.5 Asset Residual Value and Other Considerations	44	(2)
Exercises	46	(1)
Appendix: The Tax and Accounting Treatment of Leases	46	(5)
4 The Financial Analysis of Leveraged Leases	51	(14)
4.1 Introduction	51	(2)
4.2 An Example	53	(3)
4.3 Analyzing the Cash Flows by NPV or IRR	56	(1)
4.4 What Does the IRR Mean?	57	(3)
4.5 Accounting for Leveraged Leases: The "Multiple-Phases Method"	60	(4)
4.6 Comparing the MPM Rate of Return with the IRR	64	(1)
Exercises	64	(1)
II Portfolio Models	65	(74)
5 Portfolio Models--Introduction	67	(16)
5.1 Overview	67	(1)
5.2 A Simple Two-Asset Example	67	(4)
5.3 Calculating Portfolio Means and Variances	71	(2)
5.4 Portfolio Mean and Variance: The General Case	73	(5)
5.5 Efficient Portfolios	78	(1)
Exercises	79	(1)
Appendix: Continuously Compounded versus Discrete Returns	80	(3)
6 Calculating the Variance-Covariance Matrix	83	(10)
6.1 Overview	83	(1)
6.2 Using the Excess-Return Matrix in the Spreadsheet	84	(1)
6.3 Illustration	85	(1)
6.4 Using an Excel Macro	86	(4)
6.5 The Single-Index Model	90	(2)
Exercises	92	(1)
7 Calculating Efficient Portfolios When There Are No Short Sale Restrictions	93	(20)
7.1 Overview	93	(1)
7.2 Some Preliminary Definitions and Notation	93	(2)
7.3 Some Theorems on Efficient Portfolios and the CAPM	95	(3)
7.4 Calculating the Efficient Frontier: An Example	98	(6)
7.5 Finding the Market Portfolio: The Capital Market Line (CML)	104	(2)
7.6 The SML When There Is a Risk-Free Asset	106	(1)
Exercises	106	(1)
Appendix	107	(6)
8 Estimating Betas and the Security Market Line	113	(14)
8.1 Introduction	113	(1)
8.2 Testing the CAPM	113	(3)
8.3 Testing the CAPM: General Rules	116	(1)
8.4 Why Are the Results so Bad? Is the "Market" Portfolio Efficient?	117	(1)
8.5 The Nonefficiency of the "Market Portfolio"	118	(6)
8.6 So What's the Real Market Portfolio? How Can We Test the CAPM?	124	(1)
8.7 Does the CAPM Have Any Uses?	125	(1)
Exercise	126	(1)
9 Efficient Portfolios without Short Sales	127	(12)
9.1 Introduction	127	(2)
9.2 A Numerical Example	129	(3)
9.3 The Efficient Frontier with Short-Sale Restrictions	132	(2)
9.4 The VBA Program	134	(3)
9.5 Conclusion	137	(1)
Exercises	137	(2)
III Option Pricing Models	139	(88)
10 An Introduction to Options	141	(20)
10.1 Basic Option Definitions and Terminology	141	(6)
10.2 Some Examples	147	(1)
10.3 Option Payoff and Profit Patterns	148	(4)
10.4 Option Strategies: Payoffs from Portfolios of Options and Stocks	152	(2)
10.5 Option Arbitrage Propositions	154	(5)
Exercises	159	(2)
11 The Binomial Option-Pricing Model	161	(18)
11.1 Two-Date Binomial Pricing	161	(1)
11.2 State Prices	162	(2)
11.3 Multiperiod Binomial Model	164	(5)
11.4 Pricing American Options Using the Binomial Pricing Model	169	(2)
11.5 Programming the Binomial Option-Pricing Model in VBA	171	(7)
Exercises	178	(1)
12 The Lognormal Distribution	179	(18)
12.1 Introduction	179	(1)
12.2 What Do Stock Prices Look Like?	180	(1)
12.3 Lognormal Price Distributions and Geometric Diffusions	181	(4)
12.4 What Does the Lognormal Distribution Look Like?	185	(3)
12.5 Simulating Lognormal Price Paths	188	(4)
12.6 Technical Analysis	192	(1)
12.7 Calculating the Parameters of the Lognormal Distribution from Stock Prices	193	(2)
Exercises	195	(2)
13 The Black-Scholes Model	197	(10)
13.1 Introduction	197	(1)
13.2 The Black-Scholes Model	197	(2)
13.3 Using VBA to Define a Black-Scholes Pricing Function	199	(2)
13.4 Calculating the Implied Volatility	201	(2)
13.5 A VBA Function to Find the Implied Variance	203	(2)
Exercises	205	(2)
14 Portfolio Insurance	207	(20)
14.1 Overview: Insuring Stock Returns	207	(1)
14.2 Portfolio Insurance on More Complicated Assets	208	(2)
14.3 An Example	210	(3)
14.4 Some Properties of Portfolio Insurance	213	(2)
14.5 What Do Portfolio Insurance Strategies Look Like? A Simulation Program	215	(6)
14.6 Insuring Total Portfolio Returns	221	(3)
14.7 Implicit Puts and Asset Values	224	(2)
Exercises	226	(1)
IV Bonds and Duration	227	(54)
15 Duration	229	(18)
15.1 Introduction	229	(1)
15.2 Two Examples	229	(3)
15.3 What Does Duration Mean?	232	(2)
15.4 Duration Patterns	234	(1)
15.5 The Duration of a Bond with Uneven Payments	235	(8)
15.6 Nonflat Term Structures and Duration	243	(3)
Exercises	246	(1)
16 Immunization Strategies	247	(8)
16.1 Introduction	247	(1)
16.2 A Basic Simple Model of Immunization	247	(2)
16.3 A Numerical Example	249	(2)
16.4 Convexity: A Continuation of Our Immunization Experiment	251	(2)
16.5 Building a Better Mousetrap	253	(1)
Exercises	254	(1)
17 Calculating Default-Adjusted Expected Bond Returns	255	(16)
17.1 Introduction	255	(1)
17.2 Calculating the Expected Return in a One-Period Framework	256	(2)
17.3 A Multiperiod, Multistate Markov Chain Problem	258	(4)
17.4 A Numerical Example	262	(2)
17.5 Transition Matrices and Recovery Percentages: What Do We Know?	264	(3)
17.6 Adjusting the Expected Return for Uneven Periods	267	(1)
17.7 Computing Bond Betas	268	(1)
Exercises	269	(2)
18 Duration and the Cheapest-to-Deliver Problem for Treasury Bond Futures Contracts	271	(10)
18.1 Introduction	271	(1)
18.2 A General Model of the CTD	271	(2)
18.3 The Extremal Coupon as a General Solution for the CTD	273	(1)
18.4 Choosing the Optimal Maturity for CTD: The Case of Flat Term Structure	273	(1)
18.5 Using Excel to Plot the CTD and Duration	274	(6)
18.6 Conclusion	280	(1)
V Technical Considerations	281	(46)
19 Generating Random Numbers	283	(12)
19.1 Introduction	283	(1)
19.2 Testing the Excel Random-Number Generator	284	(4)
19.3 Generating Normally Distributed Random Numbers	288	(5)
Exercises	293	(2)
20 Data Table Commands	295	(6)
20.1 Introduction	295	(1)
20.2 An Example	295	(1)
20.3 Setting Up a Data Table	296	(1)
20.4 Building a Two-Dimensional Data Table	297	(2)
20.5 An Aesthetic Note: Hiding the Formula Cells	299	(1)
20.6 Excel Data Tables Are Arrays	300	(1)
21 Matrices	301	(8)
21.1 Introduction	301	(1)
21.2 Matrix Operations	301	(3)
21.3 Matrix Inverses	304	(1)
21.4 Solving Systems of Simultaneous Linear Equations	305	(1)
Exercises	306	(3)
22 The Gauss-Seidel Method	309	(4)
22.1 Overview	309	(1)
22.2 A Simple Example	309	(1)
22.3 A More Concise Example	310	(1)
22.4 Conclusion	310	(1)
Exercise	311	(2)
23 Excel Functions	313	(14)
23.1 Introduction	313	(1)
23.2 Financial Functions	313	(4)
23.3 Array Functions	317	(4)
23.4 Statistical Functions	321	(1)
23.5 Doing Regressions with Excel	322	(3)
23.6 Conditional Functions	325	(2)
VI Introduction to Visual Basic for Applications	327	(72)
24 Programming in Microsoft Excel	329	(24)
24.1 Overview	329	(1)
24.2 Why Macros Are Necessary	329	(1)
24.3 Recording a Macro	330	(4)
24.4 Editing a Macro	334	(2)
24.5 Running a Macro (Part 1)	336	(2)
24.6 Making a Macro Globally Available	338	(3)
24.7 Running a Macro (Part 2)	341	(9)
24.8 More Information on Our First Macro	350	(3)
25 Introduction to User-Defined Functions in Visual Basic for Applications	353	(46)
25.1 Overview	353	(1)
25.2 A Simple Example	353	(2)
25.3 Using User-Defined Functions	355	(23)
25.4 Excel Objects: A Short Introduction	378	(13)
25.5 The Ins and Outs of Array Manipulation in VBA	391	(6)
25.6 Conclusion	397	(2)
References	399	(6)
Index	405