Financial Modeling - 2nd Edition

by Simon Benninga

Edition: 2nd

ISBN13: 9780262024822

ISBN10: 0262024829

Format: Hardcover

Pub. Date: 2000-10-01

Publisher(s): Mit Pr

Other versions by this Author

List Price: ~~$80.00~~

Rent Textbook

Select for Price

Add to Cart

There was a problem. Please try again later.

New Textbook

We're Sorry
Sold Out

Used Textbook

We're Sorry
Sold Out

eTextbook

We're Sorry
Not Available

Buy from our Marketplace starting at $3.97

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.

Preface

Preface to the First Edition

xvii

I Corporate Finance Models

(128)

Basic Financial Calculations

(24)

Introduction

(1)

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

(2)

The Internal Rate of Return (IRR) and Loan Tables

(3)

Multiple Internal Rates of Return

(3)

Flat Payment Schedules

(1)

Future Values and Applications

(2)

A Pension Problem---Complicating the Future Value Problem

(4)

Continuous Compounding

(9)

Exercises

(5)

Calculating the Cost of Capitol

(30)

Introduction

(1)

The Gordon Dividend Model

(4)

Calculating the Cost of Equity for Abbott Laboratories Using the Gordon Model

(2)

Capitol Asset Pricing Model

(2)

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

(2)

Calculating the Cost of Debt

(1)

Calculating Abbott's Cost of Debt

(3)

Weighted Average Cost of Capitol (WACC)

(1)

When the Models Don't Work

(4)

Conclusion

(11)

Exercises

(2)

A Rule of Thumb for Calculating Debt Betas

(2)

Why Is β Such a Good Measure of Risk? Portfolio β versus Individual Stock β

(1)

Getting Data from the Internet

(5)

Financial Statement Modeling

(32)

Overview

(1)

How Financial Models Work: Theory and an Initial Example

(7)

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

(4)

Using the FCF to Value the Firm and Its Equity

(1)

Some Notes on the Valuation Procedure

(2)

Sensitivity Analysis

(1)

Debt as a Plug

(3)

Incorporating a Target Debt/Equity Ratio into a Pro Forma

(1)

Project Finance: Debt Repayment Schedules

(4)

Conclusion

(9)

Exercises

(2)

Calculating the Free Cash Flows When There Are Negative Profits

(1)

Accelerated Depreciation in Pro Forma Models

(5)

Using Financial Statement Models for Valuation

(12)

Overview

(1)

Farmers Bagels---Some Background

(2)

Building a Financial Model

(5)

Deriving the Free Cash Flows (FCF) for Farmers Bagels

(1)

Calculating Farmers' Weighted Average Cost of Capitol

(1)

Sensitivity Analysis

(1)

Conclusion

(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

Financial Modeling - 2nd Edition

Rent Textbook

New Textbook

Used Textbook

eTextbook

How Marketplace Works:

Summary

Table of Contents

Digital License