• Author / Uploaded
• PAMELA AMARO JAQUEZ

Citation preview

This page intentionally left blank

PRINCIPLES of Finance with Excel®

This page intentionally left blank

PRINCIPLES of ® Finance with Excel SECOND EDITION

Simon Benninga

New York Oxford OXFORD UNIVERSITY PRESS 2011

Oxford University Press, Inc., publishes works that further Oxford University’s objective of excellence in research, scholarship, and education. Oxford University Press Oxford New York Athens Auckland Bangkok Bogotá Buenos Aires Cape Town Chennai Dar es Salaam Delhi Florence Hong Kong Istanbul Karachi Kolkata Kuala Lumpur Madrid Melbourne Mexico City Mumbai Nairobi Paris São Paulo Shanghai Singapore Taipei Tokyo Toronto Warsaw With offices in Argentina Austria Brazil Chile Czech Republic France Greece Guatemala Hungary Italy Japan Poland Portugal Singapore South Korea Switzerland Thailand Turkey Ukraine Vietnam Copyright © 2011, 2006 For titles covered by Section 112 of the US Higher Education Opportunity Act, please visit www.oup.com/us/he for the latest information about pricing and alternate formats. Published by Oxford University Press, Inc. 198 Madison Avenue, New York, New York 10016 http://www.oup.com Oxford is a registered trademark of Oxford University Press All references to Excel in this text are understood to be “Microsoft® Excel®,” a trademark of the Microsoft Corporation. The Microsoft Corporation is in no way affiliated with or endorses this publication. The publisher and author have made every effort to make this book as complete and accurate as possible, but no warranty or fitness is implied. The information herein is provided on an “as is” basis. The author and the publisher shall have neither liability nor responsibility to any person or entity with respect to any loss or damages arising from the information contained in the book or from the use of the CD or programs accompanying it. All rights reserved. No part of this publication may be reproduced, stored in a retrieval system, or transmitted, in any form or by any means, electronic, mechanical, photocopying, recording, or otherwise, without the prior permission of Oxford University Press. Library of Congress Cataloging-in-Publication Data Benninga, Simon. Principles of Finance with Excel / Simon Benninga.—2nd ed. p. cm. ISBN 978-0-19-975547-9 (acid-free paper) 1. Finance—Data processing. 2. Microsoft Excel (Computer file) 3. Capital assets pricing model. I. Title. HG173.B463 2011 332.0285′554—dc22

Printing number: 9 8 7 6 5 4 3 2 1 Printed in the United States of America on acid-free paper

2010009514

I dedicate this book to the memory of my parents Helen Benninga (1913–2008) and Noach Benninga (1909–1993)

This page intentionally left blank

CONTENTS Preface ix Part One CAPITAL BUDGETING AND VALUATION Chapter 1

Introduction to Finance 3

Chapter 2

The Time Value of Money 16

Chapter 3

What Does It Cost? IRR and the Time Value of Money 68

Chapter 4

Introduction to Capital Budgeting 104

Chapter 5

Issues in Capital Budgeting 140

Chapter 6

Choosing a Discount Rate 184

Chapter 7

Using Financial Planning Models for Valuation 214

Part Two PORTFOLIO ANALYSIS AND THE CAPITAL ASSET PRICING MODEL Chapter 8

What Is Risk? 257

Chapter 9

Statistics for Portfolios 279

Chapter 10 Portfolio Returns and the Efficient Frontier 313 Chapter 11 The Capital Asset Pricing Model and the Security Market Line 344 Chapter 12 Using the Security Market Line to Measure Investment Performance 373 Chapter 13 The Security Market Line and the Cost of Capital 394 Part Three VALUING SECURITIES Chapter 14 Efficient Markets—Some General Principles of Security Valuation 423 Chapter 15 Bond Valuation 449 Chapter 16 Valuing Stocks 482 Part Four CAPITAL STRUCTURE AND DIVIDEND POLICY Chapter 17 Capital Structure and the Value of the Firm 511 Chapter 18 The Evidence on Capital Structure 556 Chapter 19 Dividend Policy 567

vii

viii CONTENTS

Part Five OPTIONS AND OPTION VALUATION Chapter 20 Introduction to Options 589 Chapter 21 Option Pricing Facts 623 Chapter 22 Option Pricing—The Black-Scholes Formula 640 Chapter 23 The Binomial Option-Pricing Model 662 Part Six BACKGROUND TO EXCEL Chapter 24 Introduction to Excel 683 Chapter 25 Graphs and Charts in Excel 708 Chapter 26 Excel Functions 725 Chapter 27 Data Tables 755 Chapter 28 Using Goal Seek and Solver 766 Chapter 29 Working with Dates in Excel 776 Index 789

Preface Finance is the study of financial decision making. Individuals and companies make financial decisions every day, and it’s important to make them wisely. Principles of Finance with Excel (PFE) will teach you how to make these decisions—both the theory and the implementation of wise financial decision making—and how to express your decisions using Excel. Learning to do finance with Excel serves two purposes: It teaches you an important academic and practical subject (finance), and it teaches you how to implement financial analysis using the most important tool (in most cases, the only tool) for financial analysis (Excel). Your knowledge of both finance and Excel will be enhanced by carefully working through the examples and exercises in each chapter. Finance is a very practical discipline. Most readers of this book will study finance not only to increase their understanding of the valuation process, but also to get answers to practical problems. You will find that the extensive computation required in this book will not only enable you to get numerical answers to important problems (although that alone would justify the Excelcentered focus of this book)—but also deepen your understanding of the concepts involved.

Changes from the First Edition Thanks to feedback solicited from the first-edition adopters, colleagues, and students, the second edition of Principles of Finance with Excel incorporates a number of important changes: • The structure of the book has been streamlined so that the reader goes straight into the heart of finance—time value of money and discounting. Based on reviewer comments, Chapters 1, 2, and 3 from the first edition have been removed, with the most important material integrated in other places in the text where necessary. • Nearly every example in every chapter—a centerpiece of the text—has been updated. Most now refer to the post-2008-crash financial world. • Every chapter and spreadsheet has been thoroughly reviewed for accuracy. Every page of text has been revisited and, where appropriate, refined for clarity and conciseness. • The second edition now uses Excel 2007 throughout the book.1 The disk that comes with PFE includes chapter and exercise files for the whole book, so that it can easily be used for self-teaching. As in the first edition, we include an Excel “primer” at the end of the book. Note that all the examples and spreadsheets can be used with previous versions of Excel.

Prerequisites—What Excel Background Is Required for Principles of Finance with Excel? This book will teach you—alongside finance—all of the Excel concepts needed for finance. However, you should not expect the book to be a complete Excel text. I expect that, before you 1

If you are using an earlier version of Excel, download a free compatibility pack by typing “Office 2007 compatibility pack” into the search box at http://www.microsoft.com. The Excel files are fully compatible with Excel 2010.

ix

x Preface start your finance course, you will know how to do the following in Excel (just in case, many of these topics are covered in Chapter 24): • Open and save an Excel workbook. • Use basic Excel functions, for example: Sum( ) . . . . • Format numbers: Here’s an example of something that is usually not explained in the text.

6 7 8 9

C D E ($6,144.57)