Financial Modeling for Managers: With Excel Applications

288 Pages·2002·1.655 MB·English

by  Dawn E. Lorimer, Charles R. Rayhorn

with Excel® applications
second edition
Dawn E. Lorimer
Charles R. Rayhorn
Copyright 2002 by Authors Academic Press
Financial Modeling for Managers
with Excel Applications
All rights reserved. Previous edition copyright 1999 by Dunmore Press Limited. Printed in
the United States of America. Except as permitted under the United States Copyright Act of
1976, no part of this publication may be reproduced or distributed in any form or by any
means, or stored in a data base or retrieval system, without the prior written permission of
the publisher.
ISBN 0-9703333-1-5
Acquisition Editors: Trond Randoy, Jon Down, Don Herrmann
Senior Production Manager: Cynthia Leonard
Layout Support: Rebecca Herschell
Marketing Manager: Nada Down
Copyeditor: Michelle Abbott
Cover Design: Tom Fenske and Cynthia Leonard
Cover Photo: London Dealing Room. Courtesy National Australia Bank, London.
Printer: EP Imaging Concepts
Acknowledgments
Robert Miller—Northern Michigan University, Michael G. Erickson—Albertson College, and
Jacquelynne McLellan—Frostburg State University all contributed greatly to the production of
this book through their thoughtful reviews.
When ordering this title, use ISBN 0-9703333-1-5
www.AuthorsAP.com
Library of Congress Cataloging-in-Publication Data
Lorimer, Dawn E., 1950-
Financial modeling for managers : with Excel applications / Dawn E.
Lorimer, Charles R. Rayhorn.— 2nd ed.
p. cm.
Rev. ed. of: Financial maths for managers / Dawn E. Lorimer, Charles R.
Rayhorn. c1999.
Includes bibliographical references and index.
ISBN 0-9703333-1-5 (pbk.)
1. Business mathematics. 2. Microsoft Excel for Windows. 3.
Electronic spreadsheets. 4. Financial futures. I. Rayhorn, Charles R.,
1949- II. Lorimer, Dawn E., 1950- Financial maths for managers. III.
Title.
HF5691 .L58 2001
650'.01’513—dc21
2001005810
ii
Contents
CONTENTS
Contents
Preface ........................................................................................................1
Part I. Interest rates and foreign exchange
Chapter 1: Compounding and discounting 7
1.1 Notation and definitions ......................................................................... 8
1.2 Time value of money ............................................................................. 8
1.3 Simple interest, bills and other money market securities ......................10
1.4 Compound interest ................................................................................16
1.5 Linear interpolation ............................................................................... 19
1.6 Real interest rates .................................................................................20
Chapter 2: The valuation of cash flows 31
2.1 Notation and definitions ........................................................................32
2.2 Cash flow representation ......................................................................34
2.3 Valuing annuities ...................................................................................36
2.4 Quotation of interest rates ....................................................................39
2.5 Continuous time compounding and discounting .....................................44
2.6 Fixed interest securities ........................................................................47
2.7 Duration and value sensitivity ...............................................................57
2.8 Interest rate futures ..............................................................................61
Chapter 3: Zero’s, forwards, and the term structure 73
3.1 Notation and definitions ........................................................................74
3.2 A brief mystery of time .......................................................................75
3.3 Zero coupon rates .................................................................................76
3.4 Implied forward rates ...........................................................................79
3.5 Forwards, futures and no-arbitrage ......................................................81
3.6 Computing zeros and forwards .............................................................86
3.7 Algorithm: computing zeros and forwards from swap data .................89
3.8 Concluding remarks.............................................................................. 97
iii
Chapter 4: FX Spot and forwards 103
4.1 Notation and definitions ......................................................................104
4.2 Spot exchange rate quotations ............................................................105
4.3 Inversions ............................................................................................108
4.4 Cross rates ..........................................................................................109
4.5 Money market forward rates ..............................................................114
Part II. Doing it the Excel® way
Chapter 5: Learning by doing: an introduction to financial spreadsheets 127
5.1 Step-by-step bond valuation example .................................................128
5.2 Do-it-yourself fixed interest workshop ...............................................137
Part III. Statistical analysis and probability processes
Chapter 6: Statistics without becoming one 151
6.1 Notation and definitions ......................................................................152
6.2 Introduction to data .............................................................................153
6.3 Downloading data ..............................................................................154
6.4 Data exploration .................................................................................156
6.5 Summary measures ............................................................................159
6.6 Distribution function and densities ......................................................164
6.7 Sampling distributions and hypothesis testing ......................................168
Chapter 7: Regression and financial modeling 175
7.1 Notation and definitions ...................................................................... 176
7.2 Bivariate data exploration ...................................................................177
7.3 Regression statistics ..........................................................................182
7.4 More regression theory: goodness of fit ............................................187
7.5 The CAPM beta .................................................................................189
7.6 Regression extensions: multiple regression ........................................196
iv
Chapter 8: Introduction to stochastic processes 203
8.1 Notation and definitions ......................................................................204
8.2 Expectations .......................................................................................205
8.3 Hedging ...............................................................................................206
8.4 Random walks and Ito processes. ......................................................213
8.5 How Ito processes are used ...............................................................217
8.6 Volatility models ..................................................................................223
8.7 Other time series buzzwords ..............................................................226
Part IV. Many variables
Chapter 9: Many variables 235
9.1 Vectors and matrices ..........................................................................236
9.2 Matrix inverses and equation solving ..................................................240
9.3 Statistics with matrices .......................................................................243
9.4 Portfolio theory with matrices ...........................................................245
9.5 Practicum ............................................................................................248
Part V. Appendix
General Mathematical review 264
A1 Order of operations .............................................................................264
A2 Multiplication and division with signed numbers .................................265
A3 Powers and indices .............................................................................267
A4 Logarithms ..........................................................................................272
A5 Calculus ..............................................................................................273
v
vi
Preface
WHY READ THIS BOOK?
We wrote the book because we felt that financial professionals needed it; and
those who are training to be financial professionals, as students in colleges, will
need it by the time they finish. Not all of us are cut out to be Quants, or would even
want to. But pretty much all of us in the financial world will sooner or later have to
come to grips with two things. The first is basic financial math and models. The
second is spreadsheeting. So we thought that the market should have a book that
combined both.
The origins of the book lie in financial market experience, where one of us (DEL),
at the time running a swaps desk, had the problem of training staff fresh from
colleges, even good universities, who arrived in a non operational state. Rather like
a kitset that had to be assembled on the job: you know the bits are all there, but they
can’t begin to work until someone puts all the bits together. Their theoretical
knowledge might or might not have been OK, but instead of hitting the ground
running, the new graduates collapsed in a heap when faced with the “what do I do
here and now?” problem. The present book evolved out of notes and instructional
guidelines developed at the time. In later versions, the notes took on an international
flavor and in doing so, acquired a co author (CH), to become the present book,
suitable for the U.S. and other international markets.
The material you will find here has been selected for maximal relevance for the
day to day jobs that you will find in the financial world, especially those concerned
with financial markets, or running a corporate treasury. Everything that is here, so
far as topics are concerned, you can dig up from some theoretical book or journal
article in finance or financial economics. But the first problem is that busy
1
professionals simply do not have the time to go on library hunts. The second problem
is that even if you do manage to find them, the techniques and topics are not
implemented in terms of the kind of computational methods in almost universal use
these days, namely Excel or similar spreadsheets.
No spreadsheet, no solution, and that is pretty well how things stand throughout the
financial world. Students who graduate without solid spreadsheeting skills
automatically start out behind the eight ball. Of course, depending on where you
work, there can be special purpose packages; treasury systems, funds management
systems, database systems, and the like, and some of them are very good. But it is
inadvisable to become completely locked into special purpose packages, for a number
of reasons. For one thing, they are too specialized, and for another, they are subject
to “package capture”, where firms become expensively locked into service
agreements or upgrades. So in our own courses, whether at colleges or in the
markets, we stress the flexibility and relative independence offered by a multipurpose
package such as Excel or Lotus. Most practitioners continue to hedge their special
systems around with Excel spreadsheets. Others who might use econometric or
similar data crunching packages, continue to use Excel for operations like basic
data handling or graphing. So, spreadsheets are the universal data and money handling
tool.
It is also a truism that the financial world is going high tech in its methodologies,
which can become bewildering to managers faced with the latest buzzwords, usually
acronymic and often incomprehensible. This leaves the manager at a moral
disadvantage, and can become quite expensive in terms of “consultant capture”. In
writing the book we also wanted to address this credibility gap, by showing that
some, at least, of the buzzwords can be understood in relatively plain terms, and
can even be spreadsheeted in one or two cases. So without trying to belittle the
quants, or the consultants who trade in their work, we are striking a blow here for
the common manager.
It remains to thank the many people who have helped us in preparing the book. As
the project progressed, more and more people from the financial and academic
communities became involved, and some must be singled out for special mention.
Larry Grannan from the Chicago Mercantile Exchange responded quickly to our
questions on CME futures contracts. Cayne Dunnett from the National Bank of
Australia (NAB) in London gave generously of his time to advise us on financial
calculations. Together with Ken Pipe, he also organized the photo shoot for the
front cover, taken in the NAB’s new London dealing room. Joe D’Maio from the
New York office of the NAB pitched in with assistance on market conventions and
products. Andy Morris at Westpac in New York provided helpful information on
US financial products. Penny Ford from the BNZ in Wellington, New Zealand
kindly assisted with technical advice and data.
2
Among the academic community, Jacquelynne McLellan from Frostburg State
University in Maryland and Michael G. Erickson from Albertson College in Ohio
read the entire manuscript and took the trouble to make detailed comments and
recommendations. Roger Bowden at Victoria University of Wellington read through
the manuscript making many helpful suggestions, and kept us straight on stochastic
processes and econometric buzzwords. Finally, it has been great working with
Cynthia Leonard and Tom Fenske of Authors Academic Press. They have been
encouraging, patient, and responsive at all times, which has made this project a
positive experience for all.
3
4