Simon Benninga. Financial Modelling 3-rd edition

Подождите немного. Документ загружается.

xx Contents

39.3 Multidimensional Arrays 946

39.4 Dynamic Arrays and the ReDim Statement 948

39.5 Array Assignment 959

39.6 Variants Containing an Array 960

39.7 Arrays as Parameters to Functions 963

39.8 Summary 971

Exercises 971

40 Objects and Add-Ins 975

40.1 Overview 975

40.2 An Introduction to Worksheet Objects 975

40.3 The Range Object 979

40.4 The With Statement 984

40.5 Collections 985

40.6 Names 991

40.7 Using the Object Browser 995

40.8 References to External Functions in Excel 997

40.9 References to External Functions in VBA 999

40.10 Add-Ins and Integration 1008

40.11 Summary 1014

Exercises 1014

Appendix 1: The Excel Object Model 1018

Appendix 2: Extracts from the Help File for Some Methods 1020

41 Information from the Web 1029

41.1 Overview 1029

41.2 Copy and Paste as a Simple Data-Acquisition

Technique 1029

41.3 Dynamic Web Queries 1035

41.4 Web Queries: The iqy File 1041

41.5 Parametric Web Pages 1047

41.6 Web Queries: Parameters 1049

41.7 Web Queries: CSV Files and Postprocessing 1056

41.8 A VBA Application: Importing Price Data from Yahoo 1059

41.9 Summary 1089

Exercises 1089

xxi Contents

Appendix 1: Excerpts from the Help File 1090

Appendix 2: The R1C1 Reference Style 1093

References 1095

Index 1107

Preface

The two previous editions of Financial Modeling have received a gratify-

ingly positive response from readers. The “cookbook combination” that

mixes explanation and implementation using Excel fulﬁ lls a need in both

the academic and practitioner markets for readers who realize that the

implementation of the ﬁ nance basics typically studied in an introductory

ﬁ nance course requires another, more heavily computational and imple-

mentational, approach. Excel, the most widely used computational tool

in ﬁ nance, is a natural vehicle for deepening our understanding of the

materials.

Financial Modeling is organized along six different subject areas. Each

of the ﬁ rst four sections of the book relates to a speciﬁ c area of ﬁ nance.

These sections are independent of each other, though the reader should

realize that they all assume some familiarity with the ﬁ nance area—

Financial Modeling is not an introductory text. Section I (Chapters 1–7)

deals with corporate ﬁ nance topics; Section II (Chapters 8–15) with

portfolio models; Section III (Chapters 16–24) with option models; and

Section IV (Chapters 25–28) with bond-related topics.

The last two sections of Financial Modeling are technical in nature.

Section V (Chapters 29–35) relates to various Excel topics that are used

throughout the book. Chapters in this section can be read and accessed

as necessary. Section VI (Chapters 36–41) deals with Excel’s program-

ming language, Visual Basic for Applications (VBA). VBA is used

throughout Financial Modeling to create functions and routines that

make life easier, but it is never intrusive—in principle the reader can

understand the materials in all of the other chapters of Financial Model-

ing without needing the VBA chapters.

New Chapters

Finance is a very dynamic area. The new edition of Financial Modeling

contains many updates and changes that track new developments in the

area of computational ﬁ nance. In addition, almost all the chapters have

been revised to make explanations more up-to-date. The third edition of

Financial Modeling contains eight completely new chapters:

•

Chapter 5 discusses bank valuation. The basic valuation framework of

Chapter 3 is applied to the valuation of ﬁ nancial institutions.

•

Chapter 13 adds an exposition of the Black-Litterman model of port-

folio choice to the section on portfolio models. This model, widely used

in asset allocation, is not discussed in any major textbook.

•

Chapter 14 discusses event studies, the most prominent tool for judging

the effect of market events on the returns of individual stocks.

•

Chapter 20, on Greeks, has been added to Section III, on options.

•

Chapters 22 and 23 discuss the implementation of Monte Carlo methods

to option valuation.

•

Chapter 34 discusses array functions, both those included with Excel

and the construction of homemade array functions.

•

Chapter 41 shows how to use VBA to extract Web information to

Excel.

In addition to the new chapters, many of the Financial Modeling chap-

ters have been substantially rewritten. Following are a few examples:

Chapter 2 includes a number of new cases used to illustrate the estima-

tion of the cost of capital.

Chapter 4 has a new example (PPG Corporation) for the implementa-

tion of pro forma models and valuation.

Chapter 10 now includes a discussion of shrinkage methods and their

use in the estimation of the variance-covariance methods.

Chapter 17 shows how to use the binomial option pricing model to price

employee stock options.

Chapter 19 adds discussions of structured securities and the Merton

model within the framework of Black-Scholes.

Chapter 27, on polynomial term structure models, is based on new mate-

rials and a new data set of zero-coupon bonds from the Federal

Reserve.

Getformula

The Excel ﬁ les with this edition include a function called Getformula

that enables the user to track cell contents. The disk that comes with

Financial Modeling has a document on how users can add Getformula

to their ﬁ les. In order to allow Getformula to work, you must set your

xxiv Preface

Excel security settings (Tools|Macro|Security) to Medium. If you have

done this, then when opening an Excel notebook, you will be confronted

by the following screen:

You can safely click Enable Macros, which enables the formulas on the

notebook. A separate ﬁ le on the CD-ROM tells you how to implant this

useful program on your own Excel notebooks.

Excel 2007

As this book went to press in late 2007, Excel 2007 was starting to be

used on many computer systems. The differences between Excel 2007

and previous versions of Excel are largely esthetic and not substantive.

Since most readers of this book are likely to have older versions of Excel

on their computers, I have chosen to continue using Excel 2003 in this

edition. A document relating to Excel 2007 is on the disk with Financial

Modeling.

The Disk

The CD-ROM included with Financial Modeling provides ﬁ les that give

all the Excel contents of each chapter as well as ﬁ les that give the answers

to each of the end-of-chapter questions. All the book’s ﬁ les have been

checked and work with Excel 2007. The disk also includes documents on

the differences between Excel 2003 and Excel 2007, on adding Getfor-

xxv Preface

mula to spreadsheets, and on some problems encountered with Excel’s

XNPV and XIRR functions.

Using Financial Modeling in a University Course

Financial Modeling has become the book of choice in many intermediate

and advanced ﬁ nance classes that stress the combination of modeling/

Excel skills and a deeper understanding of the underlying ﬁ nancial

models. The Financial Modeling–based courses are often a third- or

fourth-year undergraduate or second-year MBA course. These courses

are often very different from each other and include much instructor-

speciﬁ c input, but they seem to have a few general features:

•

A typical course starts with two or three classes that stress the Excel

skills needed for ﬁ nancial modeling. Often these classes are held in a

computer lab. Though almost all business school students know Excel,

they may not know how to ﬁ nesse data tables (Chapter 30) or some of

the basic ﬁ nancial functions (Chapters 1 and 33) and array functions

(Chapter 34). The initial classes give the instructor a chance to level the

playing ﬁ eld.

•

Most one-semester courses then cover, at most, one of the Financial

Modeling sections. If we assume that in a typical university course, cover-

ing one chapter per week is an upper limit (and many chapters will

require two weeks), then a typical course might concentrate on either

corporate ﬁ nance (Chapters 1–7), portfolio models (Chapters 8–15), or

options (Chapters 16–24). At a stretch, the instructor could perhaps

throw in the shorter bond section (Chapters 25–28).

•

I suggest that after the initial classes in a computer lab, the instructor

move to a regular classroom. This enables the classroom emphasis to be

on discussions of theory and implementation, with student homework

concentrating on actual spreadsheets.

An alternative to the preceding structure is to build an even more

advanced course around VBA. I teach a ﬁ nancial engineering course that

starts with binomial option pricing, proceeds to cover some of the VBA

chapters (36–38), and then covers Black-Scholes and Monte Carlo

methods (Chapters 18–23).

xxvi Preface

A major problem with a computer-based course is how to structure

the ﬁ nal examination. Two solutions seem to work well. One alternative

is to have students (whether alone or in teams) submit a ﬁ nal project.

Examples might be a corporate valuation if the course is based on

Section I of the book, an event study for Section II, an option-based

project for Section III, or the computation of a bond expected return if

the emphasis is on Section IV. A second alternative is to have students

submit, by e-mail, a spreadsheet-based examination with severe time

limits. One instructor using this book sends his class the ﬁ nal exam (a

compendium of spreadsheet problems) at nine o’clock in the morning

and requires an e-mail with a spreadsheet answer by noon.

Acknowledgments

I want to start by thanking a group of wonderful editors: John Covell,

Nancy Lombardi, Elizabeth Murry, Ellen Pope, and Peter Reinhart.

My next thanks go to a dedicated group of colleagues who read the

typescripts for Financial Modeling: Arindam Bandopadhyaya, Michael

Chau, Jaksa Cvitanic, Richard Harris, Aurele Houngbedji, Iordanis

Karagiannidis, Yvan Lengwiler, Nejat Seyhun, Gökçe Soydemir, and

David Y. Suk.

Many of the changes in this edition of Financial Modeling are due to

the comments of readers, who have been assiduous in offering sugges-

tions and improvements for the book. I follow a tradition started with

the ﬁ rst two editions of Financial Modeling by acknowledging those

readers whose comments have been incorporated into this edition: Meni

Abudy, Zvika Aﬁ k, Gordon Alexander, Naomi Belfer, David Biere,

Vitaliy Bilyk, Oded Braverman, Roeland Brinkers, Salvio Cardozo, Israel

Dac, Jeremy Darhansoff, Toon de Bakker, Govindvyas Dharwada, Davey

Disatnik, Kevin Dowd, Brice Dupoyet, Orit Eshel, Yaara Geyra, Rana P.

Ghosh, Bjarne Jensen, Marek Jochec, Milton Joseph, Erez Kamer, Saggi

Katz, Emir Kiamilev, Paul Legerer, David Martin, Tom McCurdy, Tsahi

Melamed, Tal Mofkadi, Geoffrey Morrisett, Sandip Mukherji, David

Pedersen, Georgio Questo, Alex Riahi, Arad Rostampour, Joseph Rubin,

Oﬁ r Shatz, Mel Tukman, Guy Vishnia, Torben Voetmann, James Ward,

Roberto Wessels, Geva Yaniv, and Werner Zitzman.

xxvii Preface

Finally, I want to thank my very patient wife Terry, who has maintained

her own and my equilibrium through two books and a business school

deanship in the past ﬁ ve years.

As always, I welcome comments and corrections!

Simon Benninga

benninga@wharton.upenn.edu

xxviii Preface

Preface to the Second Edition

The purpose of this book remains to provide a “cookbook” for imple-

menting common ﬁ nancial models in Excel. This edition has been

expanded by six additional chapters, covering ﬁ nancial calculations, cost

of capital, value at risk (VaR), real options, early exercise boundaries,

and term-structure modeling. There is also an additional technical chapter

containing a potpourri of Excel hints.

I am indebted to a number of people (in addition to those mentioned

in the previous preface) for help and suggestions: Andrew A. Adamovich,

Alejandro Sanchez Arevalo, Yoni Aziz, Thierry Berger-Helmchen,

Roman Weissman Bermann, Michael Giacomo Bertolino, John Bol-

linger, Enrico Camerini, Manuel Carrera, John Carson, Roy Carson,

Lydia Cassorla, Philippe Charlier, Michael J. Clarke, Alvaro Cobo, Beni

Daniel, Ismail Dawood, Ian Dickson, Moacyr Dutra, Hector Tassinari

Eldridge, Shlomy Elias, Peng Eng, Jon Fantell, Erik Ferning, Raz Gilad,

Nir Gluzman, Michael Gofman, Doron Greenberg, Phil Hamilton,

Morten Helbak, Hitoshi Hibino, Foo Siat Hong, Marek Jochec, Russell

W. Judson, Tiffani Kaliko, Boris Karasik, Rick Labs, Allen Lee, Paul

Legerer, Guoli Li, Richard Liu, Moti Marcus, Gershon Mensher, Tal

Mofkadi, Glenn Morley, Stephen O’Neil, Steven Ong, Oren Ossad, Jackie

Rosner, Steve Rubin, Dvir Sabah, Ori Salinger, Meir Shahar, Roger

Shelor, David Siu, Maja Sliwinski, Bob Taggart, Maurry Tamarkin, Mun

Hon Tham, Efrat Tolkowsky, Mel Tukman, Sandra van Balen, Michael

Verhofen, Lia Wang, Roberto Wessels, Ethan Weyand, Ubbo Wiersema,

Weiqin Xie, Ke Yang, Ken Yook, George Yuan, Khurshid Zaynutdinov,

Ehud Ziegelman, and Eric Zivot. I also want to thank my editors, who

again have been a great help: Nancy Lombardi, Peter Reinhart, Victoria

Richardson, and Terry Vaughn.

As always I welcome suggestions and comments.

Simon Benninga

http://ﬁ nance.wharton.upenn.edu/~benninga

benninga@post.tau.ac.il