Financial Modeling - 2nd Edition

by Simon Benninga

• ISBN13: 9780262024822
• ISBN10: 0262024829
• Edition: 2nd
• Format: Hardcover
• Copyright: 2000-10-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 Modelingbridges 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 Excelreg;. In this sense, this is a finance "cookbook," providing recipes with lists of ingredients and instructions. Areas covered include computation of corporate finance problems, standard portfolio problems, option pricing and applications, and duration and immunization. The second edition contains six new chapters covering financial calculations, cost of capital, value at risk (VaR), real options, early exercise boundaries, and term structure modeling. A new technical chapter contains a potpourri of tips for using Excelreg;. Although the reader should know enough about Exceltrade; to set up a simple spreadsheet, the author explains advanced Excelreg; techniques used in the book. The book includes chapters dealing with random number generation, data tables, matrix manipulation, and VBA programming. It also comes with a CD-ROM containing Excelreg; worksheets and solutions to end-of-chapter exercises.

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