Financial Analysis and Modeling Using Excel and VBA, 2nd Edition

About This Book xix

CHAPTER 1 Introduction to Financial Analysis and Modeling 1

Steps in Creating a Model 5

How This Book Is Organized 9

Part One Excel for Financial Analysis and Modeling

CHAPTER 2 Excel 2007 and the Previous Versions 15

The New Features in Excel 2007 15

The New User Interface 16

Other New Features 19

New File Formats in Excel 2007 23

CHAPTER 3 Excel Basics 25

Improving Your Excel Skills 26

The Basic Excel Features 32

Naming Workbooks and Worksheets 33

Finding the Commands You Need 33

Working with Worksheets 33

Working with Rows, Columns, and Cells 34

Using the Toolbars 34

Using Keyboard Shortcuts 34

Navigating the Worksheet 36

Formatting 36

Entering and Working with Formulas 37

Using Paste Special 37

Controlling the Worksheet View 38

Using Undo and Redo 38

Customizing the Excel Work Environment 38

Controlling When Formulas Are Calculated 39

Printing 39

Creating Charts 40

Working with More Than One Workbook 40

Using the Scroll Lock 41

CHAPTER 4 Advanced Excel Features 43

Copying Formulas Using Absolute and Relative Cell References 46

Using Names in Formulas 56

A1 and R1C1 Reference Styles 57

Using Comments in Cells 58

Data Validation 60

Controls 62

Custom Number Formats 65

Hiding and Protecting 67

Custom Views 71

Arrays and Array Formulas 72

Outline 75

CHAPTER 5 Making Decisions and Looking Up Values 77

Making Decisions 78

Looking Up Values 97

Conditional Formatting 103

CHAPTER 6 Analyzing Databases 109

Sorting 112

Filtering 120

Tables 124

PivotTable 135

CHAPTER 7 Answering What-If Questions 161

Data Tables 162

Scenario Manager 166

CHAPTER 8 Finding Iterative Solutions 171

Circular Reference 171

Goal Seek 174

Solver 177

CHAPTER 9 Doing Statistical Analysis 185

Descriptive Statistics 187

Frequency Distributions and Histograms 192

Counting, Ranking, and Calculating Quantiles 195

Normal Probability Distributions 203

Random Numbers 207

Statistical Tests 218

CHAPTER 10 Using the Financial Functions 221

Annuity Functions 221

Investment Analysis Functions 228

Bond Functions 232

Depreciation Functions 237

CHAPTER 11 Other Useful Excel Functions 243

Mathematical Functions 243

Date and Time Functions 248

Text Functions 251

Information Functions 254

Part Two Financial Modeling Using Excel

CHAPTER 12 Building Good Excel Models and Debugging Them 257

Attributes of Good Excel Models 257

Documenting Excel Models 260

Debugging Excel Models 262

Using Formula Auditing Tools for Debugging 265

Learning Modeling Using Excel 267

CHAPTER 13 Financial Statements Forecasting 271

Review of Theory and Concepts 271

Modeling Examples 281

CHAPTER 14 Time Value of Money 327

Review of Theory and Concepts 327

Modeling Examples 341

CHAPTER 15 Financial Planning and Investments 365

Review of Theory and Concepts 365

Some Basic Issues 365

Saving and Investing for Retirement 368

Structuring Portfolios 371

Modeling Examples 372

Model 1: Return with Changing Reinvestment Rates 372

Model 2: Saving for Retirement 376

Model 3: Retirement Planning in Real Dollars 379

Model 4: Retirement Planning in Nominal Dollars 380

Model 5: Portfolio Structuring 385

CHAPTER 16 Analyzing Market History 389

Review of Theory and Concepts 389

Modeling Examples 394

CHAPTER 17 Bond Pricing and Duration 409

Review of Theory and Concepts 409

Modeling Examples 416

CHAPTER 18 Simulating Stock Prices 427

Review of Theory and Concepts 427

Modeling Examples 438

CHAPTER 19 Options and Option Portfolios 449

Review of Theory and Concepts 449

Modeling Examples 460

CHAPTER 20 Binomial Option Pricing 487

Review of Theory and Concepts 487

Modeling Examples 491

Model 1: European Options on Stocks with

Known Dividend Yield 491

Model 2: American Options on Stocks Paying

No Dividend 495

Model 3: American Options on Stocks with

Known Dividend Yields 498

Model 4: American Options on Stocks with

Known Dollar Dividends 500

Part Three VBA for Financial Modeling

CHAPTER 21 Introduction to VBA 505

Example of a VBA Model 510

Recording Macros 520

Using VBA’s Help 524

The Strategy to Learn VBA Efficiently 526

CHAPTER 22 VBA Essentials 529

The Visual Basic Editor 530

Some Basics 535

Variables, Constants, and Arrays 545

Objects, Properties, and Methods 557

Branching 561

Looping 567

Using Built-In Functions 574

CHAPTER 23 Sub and Function Procedures 577

Sub Procedures 577

Function Procedures 582

CHAPTER 24 Debugging VBA Codes 589

The Four Basic Types of Bugs 590

The VBA Debugging Tools 591

Part Four Financial Modeling Using VBA

CHAPTER 25 How to Build Good VBA Models 597

Attributes of Good VBA Models 597

Documenting VBA Models 601

Learning Modeling Using VBA 603

CHAPTER 26 Time Value of Money 607

Review of Theory and Concepts 607

Modeling Examples 611

CHAPTER 27 Financial Planning and Investments 663

Modeling Examples 663

CHAPTER 28 Analyzing Market History 691

Modeling Examples 691

CHAPTER 29 Simulating Stock Prices 717

Modeling Examples 717

CHAPTER 30 Options and Option Portfolios 725

Modeling Examples 725

CHAPTER 31 Binomial Option Pricing 741

Modeling Examples 741

APPENDIX A Keyboard Shortcuts for Excel 755

APPENDIX B VBA Quick Reference 757

APPENDIX C Excel and VBA Built-In Functions 759

About the CD-ROM 777

Index 781