[PDF] financial statement analysis practice questions
[PDF] find the inverse z transform of the following
[PDF] find the laplace transform of the given function
[PDF] find the volume of the rectangular prism
[PDF] fine dining in paris near eiffel tower
[PDF] fine dining paris texas
[PDF] fine dining paris tx
[PDF] fine dining restaurants in paris ontario
[PDF] finite automata and regular expressions problems and solutions
[PDF] finite automata generator
[PDF] finite element method basis functions
[PDF] finite element method solved problems pdf
[PDF] finite fourier sine and cosine transform pdf
[PDF] finite fourier transform of partial derivatives
[PDF] fir filter coefficients calculator
Financial Modeling Using Excel and VBA
CHANDAN SENGUPTA
John Wiley & Sons, Inc.
ffirs.qxd 1/5/04 12:57 PM Page iii ffirs.qxd 1/5/04 12:57 PM Page vi
Financial Modeling Using Excel and VBA
ffirs.qxd 1/5/04 12:57 PM Page i Founded in 1807, John Wiley & Sons is the oldest independent publishing company in the United States. With offices in North America, Europe, Australia, and Asia, Wiley is glob- ally committed to developing and marketing print and electronic products and services for our customers" professional and personal knowledge and understanding. The Wiley Trading series features books by traders who have survived the market"s ever changing temperament and have prospered-some by reinventing systems, others by getting back to basics. Whether a novice trader, professional, or somewhere in-between, these books will provide the advice and strategies needed to prosper today and well into the future. For a list of available titles, visit our Web site at www.WileyFinance.com. ffirs.qxd 1/5/04 12:57 PM Page ii
Financial Modeling Using Excel and VBA
CHANDAN SENGUPTA
John Wiley & Sons, Inc.
ffirs.qxd 1/5/04 12:57 PM Page iii Copyright © 2004 by Chandan Sengupta. All rights reserved. Published by John Wiley & Sons, Inc., Hoboken, New Jersey.
Published simultaneously in Canada.
No part of this publication may be reproduced, stored in a retrieval sys tem, or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, s canning, or otherwise, except as permitted under Section 107 or 108 of the 1976 United States C opyright Act, without either the prior written permission of the Publisher, or authorization through payment of the appro- priate per-copy fee to the Copyright Clearance Center, Inc., 222 Rosewood Drive, Danvers, MA Publisher for permission should be addressed to the Permissions Departme nt, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, 201-748-6011, fax 201-748-600
8, e-mail: permcoordi-
nator@wiley.com. Limit of Liability/Disclaimer of Warranty: While the publisher and author have used their best efforts in preparing this book, they make no representations or warranti es with respect to the accu- racy or completeness of the contents of this book and specifically discl aim any implied warranties of merchantability or fitness for a particular purpose. No warranty may be created or extended by sales representatives or written sales materials. The advice and strateg ies contained herein may not be suitable for your situation. You should consult with a professional where appropriate. Neither the publisher nor author shall be liable for any loss of profit or any o ther commercial damages, including but not limited to special, incidental, consequential, or othe r damages. For general information on our other products and services, or technical support, please contact our Customer Care Department within the United States at 800-762-2974, o utside the United
States at 317-572-3993 or fax 317-572-4002.
Wiley also publishes its books in a variety of electronic formats. Some c ontent that appears in print may not be available in electronic books.
ISBN 0-471-26768-6
Printed in the United States of America.
10 9 8 7 6 5 4 3 2 1
ffirs.qxd 1/5/04 12:57 PM Page iv
01923, 978-750-8400, fax 978-750-4470, or on the web at www.copyright.com. Requests to the
For more information about Wiley products, visit our web site at www.wiley.com.
For Preety
ffirs.qxd 1/5/04 12:57 PM Page v ffirs.qxd 1/5/04 12:57 PM Page vi
Contents
viiAbout This Book xi
CHAPTER 1 Introduction to Financial Modeling 1
Part One: Excel for Financial Modeling
CHAPTER 2 Excel Basics 13
Improving Your Excel Skills 14
The Basic Excel Features 20
CHAPTER 3 Advanced Excel Features 29
CHAPTER 4 Excel"s Built-In Functions and Analysis Tools 71
Financial Functions 73
Logical Functions 86
Statistical Functions 86
Mathematical and Trigonometric Functions 97
Lookup and Reference Functions 101
Date and Time Functions 107
Text Functions 110
Information Functions 112
The Analysis ToolPak 113
Part Two: Financial Modeling Using Excel
CHAPTER 5 How to Build Good Excel Models 119
Attributes of Good Excel Models 119
Documenting Excel Models 122
Debugging Excel Models 124
Using Formula Auditing Tools for Debugging 127
Learning Modeling Using Excel 128
ftoc.qxd 1/5/04 1:00 PM Page vii
CHAPTER 6 Financial Statements Forecasting 131
Review of Theory and Concepts 131
Modeling Examples 141
CHAPTER 7 Time Value of Money 185
Review of Theory and Concepts 185
Modeling Examples 199
CHAPTER 8 Financial Planning and Investments 223
Review of Theory and Concepts 223
Modeling Examples 230
CHAPTER 9 Analyzing Market History 247
Review of Theory and Concepts 247
Modeling Examples 252
CHAPTER 10 Bond Pricing and Duration 267
Review of Theory and Concepts 267
Modeling Examples 274
CHAPTER 11 Simulating Stock Prices 285
Review of Theory and Concepts 285
Modeling Examples 295
CHAPTER 12 Options and Option Portfolios 307
Review of Theory and Concepts 307
Modeling Examples 317
CHAPTER 13 Binomial Option Pricing 345
Review of Theory and Concepts 345
Modeling Examples 349
Part Three: VBA for Financial Modeling
CHAPTER 14 Introduction to VBA 363
Example of a VBA Model 368
Recording Macros 378
Using VBA"s Help 382
The Strategy to Learn VBA Efficiently 384
CHAPTER 15 VBA Essentials 385
The Visual Basic Editor 386
Some Basics 390
Variables, Constants, and Arrays 401
Objects, Properties, and Methods 413
viiiCONTENTS ftoc.qxd 1/5/04 1:00 PM Page viii
Branching 417
Looping 423
Using Built-In Functions 430
CHAPTER 16 Sub and Function Procedures 433
Sub Procedures 433
Functions Procedures 438
CHAPTER 17 Debugging VBA Models 445
Part Four: Financial Modeling Using VBA
CHAPTER 18 How to Build Good VBA Models 453
Attributes of Good VBA Models 453
Documenting VBA Models 457
Learning Modeling Using VBA 459
CHAPTER 19 Time Value of Money 463
Review of Theory and Concepts 463
Modeling Examples 467
CHAPTER 20 Financial Planning and Investments 519
Modeling Examples 519
CHAPTER 21 Analyzing Market History 549
Modeling Examples 549
CHAPTER 22 Simulating Stock Prices 575
Modeling Examples 575
CHAPTER 23 Options and Option Portfolios 583
Modeling Examples 583
CHAPTER 24 Binomial Option Pricing 599
Modeling Examples 599
Appendix A Keyboard Shortcuts for Excel 613
Appendix B VBA Quick Reference 615
Appendix C Excel and VBA Built-In Functions 617
Index 635
About the CD-ROM 655
Contentsix
ftoc.qxd 1/5/04 1:00 PM Page ix ftoc.qxd 1/5/04 1:00 PM Page x
About This Book
xi How do you get to Carnegie Hall? You practice, practice, practice. The same is true of financial modeling. The only way you can learn to develop good financial models is by practicing a lot. Fortunately if you learn and practice modeling the right way, you will not have to practice even one-tenth as hard as a performer does to get to Carnegie Hall. The primary objectives of this book are to show you how to learn and prac- tice financial modeling the right way and to provide you with a wide range of real-world financial models-over 75 of them-to imitate and use for practice so that you can be on your way to financial modeling"s Carnegie Hall. Financial modeling is an essential skill for finance professionals and students, and Excel and its built-in programming language, Visual Basic for Applications (VBA), are the preferred tools for the job. However, modeling using Excel and VBA is rarely presented as an integrated subject in books or classrooms. The result is that both practitioners and students follow time-consuming trial and error approaches to modeling and end up with models that are not sufficiently flexible and powerful. This book, designed for self-study, classroom use, and reference, presents a comprehensive approach for developing simple to sophisticated financial models in all major areas of finance using both Excel and VBA. The approach is based on my long experience in the business world developing a wide variety of finan- cial models and in the classroom teaching an MBA course in financial modeling that students find very useful not just in their other course work but in their sub- sequent professional careers as well. Developing good financial models requires combining knowledge of finance, mathematics, and Excel and VBA using modeling skill. In each of these areas, the fol- lowing is what I assume you already know and what you will learn from this book. In finance and mathematics, I assume that you have the necessary basic knowledge. Nonetheless, in each chapter I have included a review of the theory and concepts you will find useful for working on the models within that chapter. Because I cover a wide range of topics in the book, I think some of this material will be new to you. By immediately applying the newly acquired knowledge to flast.qxd 1/8/04 11:49 AM Page xi "real world" problems, you will expand your knowledge of finance in some areas in which you may have been interested for some time. In Excel, I assume you know the basics, and I cover the advanced features of Excel that you need for modeling in detail. You may be amazed to find out how much those whiz kids from Redmond have squeezed into Excel that many of us do not even know about. VBA will be one of the most important things you learn from this book. I assume that you know nothing about it. VBA is a powerful and very useful tool that people who have Excel already have sitting inside their computers. Unfortu- nately, very few people use it because they are afraid of learning "programming." I will teach you VBA and modeling with VBA using a simple class-tested approach. The key is to learn VBA as a language the same way you learned your mother tongue-by imitating how to say things you want to say, without worry- ing about learning all the rules of grammar or trying to acquire a large vocabu- lary that you do not need. You will be surprised to find out how little you have to learn to be able to develop models with VBA that are often more useful, pow- erful, and flexible than Excel models. Finally, I assume that you are new to modeling. Even if you have some expe- rience, you will quickly find yourself challenged as you build on your skills. You will learn by imitating and practicing on numerous models from all areas of finance, and you will be able to challenge yourself further by developing exten- sions to these models. I have not tried to cover every type of financial model that you may need to develop over the years, nor have I tried to cover modeling in depth for one or two particular areas of finance (such as derivatives). The reality-and my assump- tion-is that once you develop your financial modeling skill and learn to use Excel and VBA well, you will be able to develop models for any problem as long as you know the financial theory and mathematics needed to solve it conceptu- ally. I have therefore focused on helping you develop the skill of financial mod- eling, and the best way to develop that skill is to work on a broad range of models instead of narrowly focusing on any one area of finance. The CD that accompanies this book includes complete working versions of all the models in the book. In the text I provide the modeling strategy for each problem, detailed instructions on how to build each model, and thorough analy- sis of all the VBA codes for the models. I also explain how you can cover the material following different learning tracks depending on your background, how much time you have, and how good you want to become in financial modeling. The book and the CD also include several special tools (for example, a VBA Quick Reference and a selected list of the most useful Excel and VBA built-in functions) that you can personalize, add to over time, and keep easily accessible on your computer"s hard disk. Financial modeling is finance in action. It is challenging and it is a lot of fun. I hope this book will show you how to have fun with it and benefit from it at the same time. xiiABOUT THIS BOOK flast.qxd 1/8/04 11:49 AM Page xii
Financial Modeling Using Excel and VBA
flast.qxd 1/8/04 11:49AM Pagexiii flast.qxd 1/8/04 11:49 AM Page xiv
CHAPTER
1
Introduction to Financial Modeling
1 W hat is a financial model? What is the difference between a financial model and the spreadsheet solutions you create or VBA programs you write all the time to answer financial questions or solve financial problems? A simple, practical answer is that a financial model is designed to represent in mathematical terms the relationships among the variables of a financial prob- lem so that it can be used to answer "what if" questions or make projections. Some of the spreadsheet solutions that people create capture some of these rela- tionships as well and, therefore, can answer "what if" questions to some extent. But because they are not primarily designed with these objectives in mind, they do not try to capture as many of these interdependencies as possible, and their structures often make it cumbersome to answer "what if" questions or make pro- jections with them. This may sound a little abstract. So let us look at a simple, concrete exam- ple. Suppose you are using a spreadsheet to calculate, based on your taxable income, what your after-tax income was last year. Income tax rates vary in steps (brackets) for different income levels. So you cannot simply calculate your taxes by multiplying your taxable income by one tax rate (30%, for example) and sub- tracting it from your taxable income to get the after-tax income. Consider two approaches to setting up a spreadsheet to calculate the after- tax income. In the first approach, you can enter your taxable income in a cell, calculate the tax on the income (using a hand calculator and the tax rates for the different tax brackets), and enter it in the cell below. Then you can write an equa- tion in another cell to calculate your after-tax income by subtracting the tax in the second cell from the taxable income in the first cell. This spreadsheet solution will give you the answer to your immediate ques- tion, but it is not a useful financial model. Why? Because it does not capture the key mathematical relationship between taxable income and taxes. The result is that if you now try to answer the "what if" question, What would my after-tax income have been if my taxable income were $10,000 higher?, you will have to go back to doing the calculations by hand.
01_chap_sengupta.qxd 12/31/03 12:07 PM Page 1
However, you can set up your spreadsheet to calculate the taxes on any taxable income (using the different tax brackets and tax rates) and use the computed tax number to calculate your after-tax income. You will then have a financial model, because it will capture the relationship between taxable income and taxes. You also will be able to use this model to answer the "what if" question I posed before. In creating financial models, you always have to keep in mind that you want to capture as many of the interdependencies among the variables of the model as possible. In addition, you want to structure your models in such a way that it is easy to ask "what if" questions, that is, change the values of the independent variables and observe how they affect the values of the key dependent variables. You also should recognize that some of the relationships, as in the case of taxes, are easy to establish and exact; but many others will be approximate or even unknown. You will have to come up with them based on financial theory, analy- sis of data, and so on, and coming up with these relationships is one of the majorchallenges of financial modeling. Generally, the more of these relationships you can come up with and incorporate into your model, the more useful your model will be. My Assumptions about You and the Users of Your Models In this book I assume that you know the basics of finance and can solve by hand most of the problems for which you will be creating models. I also assume that you are familiar with the basics of Excel and have experience creating spread- sheet solutions to at least simple problems. You do not need to have knowledge of Excel"s advanced features or of modeling; I will cover both in detail. You also do not need to have any knowledge of VBA. A key objective of the book is to teach you VBA and modeling using VBA from scratch by way of an easy and effective method. Another important assumption I am making is that you will be developing the models primarily for your own use or for use by people who have some expe- rience with Excel, but not necessarily with VBA. When you create models for use by people who have little or no familiarity with Excel, it requires adding special user interfaces to make the models easy to use. One must build into them spe- cial features to make them "bulletproof"-that is, to make sure that the models willnot crash or produce wrong results if someone enters inappropriate inputs. I will discuss some design methods and Excel features that make models easier to use and more "bullet-resistant." Most everyday modelers do not need to go beyond this.
Excel and VBA as Modeling Tools
Even in the mid- to late 1990s, Excel was not considered a powerful enough tool for serious financial modeling, in part because the PCs available at the time had
2INTRODUCTION TO FINANCIAL MODELING
01_chap_sengupta.qxd 12/31/03 12:07 PM Page 2
speed and memory limitations. With advances in PCs and improvements in Excel itself, the table has now turned completely: Excel has become the preferred tool for creating all but the largest and most computationally intensive financial mod- els. The advantages of Excel for financial modeling are so obvious that it is not necessary to go into them. However, for those who have not worked with other programs or programming languages for modeling, it is worthwhile to point out that one of the important advantages of Excel is that with Excel you can create excellent output with very little work. You should learn to take full advantage of
Excel"s power in this respect.
If Excel is so good, then, why bother with VBA? VBA is a programming lan- guage, and if you do not know anything about programming languages, it will be difficult for you to appreciate the advantages of VBA at this point. Let me touch on only a few key reasons here, and I will answer the question in greater detail when we discuss modeling with VBA. Despite its power, Excel has many limitations, and there are many financial models-some even relatively simple ones-that either cannot be created in Excel or will be overly complex or cumbersome to create in Excel. What"s more, when you create a highly complex model in Excel, it can be difficult to understand, debug, and maintain. VBA generally offers a significant edge in all these respects. The problem that most people have with VBA is that it is one more thing to learn, and they are somehow afraid of trying to learn a programming language. The reality is that if you follow the right method, learning a programming lan- guage is not particularly difficult-especially if you selectively learn what you will really use (as we will do in this book) and not let yourself get lost in all the other things you can do with VBA but probably never will. The truth is that you do not need to learn all that much to be able to create very useful and powerful finan- cial models with VBA. What you will need is a lot of practice, which you will get as you go through this book. VBA offers you the best of both worlds: you can take advantage of all the powers of Excel including its ability to easily create ex- cellent outputs, and supplement them with VBA"s additional tools and flexibility.
Independent and Dependent Variables
We can say that the purpose of a model is to calculate the values of certain dependent variables for the values provided for its independent variables. It is therefore important to understand the difference between independent and depen- dent variables. Independent variables are also called the input or external variables. The model"s user or creator inputs the values of these variables-they are not calcu- lated by the model. These are the variables you change to ask "what if" questions. For example, in our simple model the taxable income is an independent variable. A model may also include a special type of input variable called a parameter. Parameters are independent variables in that their values are also provided by the
Introduction to Financial Modeling3
01_chap_sengupta.qxd 12/31/03 12:07 PM Page 3
creator or user of the model. The difference is that their values are expected to remain constant or change infrequently within the context of the model. For ex- ample, the tax rates and the tax brackets in our simple model can be considered parameters of the model because their values have to be provided for the model to work, but these values are not expected to change frequently. As you create aquotesdbs_dbs8.pdfusesText_14
[PDF] Financial Modeling Using Excel and VBA