Mastering Financial Mathematics in Microsoft Excel: A Practical Guide for Business Calculations

383 Pages·2010·13.38 MB·English

by  Alastair Day

mastering mastering mastering
A practical guide for
financial financial
financial
business calculations
mathematics mathematics
mathematics
® ®
in microsoft excel in microsoft excel
®
in microsoft excel second edition
A practical guide for business calculations mastering A practical guide for business calculations
A practical guide for business calculations
Alastair Day has worked in the fnance industry
fuonr cmtioornes t haannd 2w5a sy efaorsm ienr ltyre aa sduirreyc atonrd o mf a rketing Mastering Financial Mathematics in Microsoft® Excel provides a comprehensive set of financial Miacsrtoesroinfgt® FEinxacnelc isa l aM partahcetmicalt igcus idine to using
vendor leasing company specializing in the IT tools, methods and formulas which apply Excel to solving mathematical problems. Excel for fnancial mathematics. This new
and technology industries. After sale to a public edition includes:
company he established Systematic Finance as This practical guide: • Excel 2007
a consultancy specializing in: • Explains basic calculations for mathematical fnance
mathematics • Addition of a glossary of key terms
• F inancial modelling – review, design, build • Shows how to use formulas using straightforward Excel templates • F unctions list in English and European
and audit languages
• Provides a CD of basic templates
• T raining in fnancial modelling, corporate ® • C ontinuity check on all formats, layouts
fnance, leasing and credit analysis on an This fully revised and updated guide is an essential companion for anyone involved in microsoft excel and charts
in-house and public basis throughout Europe, in fnance, from company accountants, through to analysts, treasury managers and • More worked examples
Middle East, Africa, Asia and America business students. Explaining basic calculations and using examples and exercises,
• A ddition of exercises at the end of each
• F inance and operating lease structuring as a the book covers:
• A n invaluable explanation of basic calculations and chapter to help build models
consultant and lessor • Cash fows
• Bonds calculations and bonds risks their underlying fnancial concepts
Alastair is author of two other modelling books
published by FT Prentice Hall: Mastering • Amortization and depreciation • I ncludes a companion CD with practical examples,
Financial Modelling and Mastering Risk Modelling • Forward interest rates and futures
both of which are in their second editions, as • Foreign exchange exercises and templates
well as other books and publications on fnancial
• Valuation
analysis and leasing.
• Leasing
Alastair has a degree in Economics and German
from London University and an MBA from the
Open University Business School. second
edition
FINANCE
second edition Visit our website at
www.pearson-books.com
Visit our website at
www.pearson-books.com
AlAstAir l. DAy
CVR_DAY0330_02_SE_CVR.indd 1 31/3/10 10:50:49
mastering financial mathematics DAY
Mastering Financial Mathematics
®
in Microsoft Excel
STRA_A01.QXP 5/11/04 12:26 pm Page ii
In an increasingly competitive world, we believe it’s quality of
thinking that gives you the edge – an idea that opens new
doors, a technique that solves a problem, or an insight that
simply makes sense of it all. The more you know, the smarter
and faster you can go.
That’s why we work with the best minds in business and finance
to bring cutting-edge thinking and best learning practice to a
global market.
Under a range of leading imprints, including Financial Times
Prentice Hall, we create world-class print publications and
electronic products bringing our readers knowledge, skills and
understanding, which can be applied whether studying or at work.
To find out more about Pearson Education publications, or tell
us about the books you’d like to find, you can visit us at
www.pearsoned.co.uk
Mastering Financial
Mathematics in
®
Microsoft Excel
A practical guide for business calculations
Second Edition
ALASTAIR L. DAY
PEARSON EDUCATION LIMITED
Edinburgh Gate
Harlow CM20 2JE
Tel: [phone]
Fax: [phone]
Website: www.pearsoned.co.uk
First published in Great Britain in 2005
Second edition 2010
© Pearson Education Limited 2005, 2010
The right of Alastair Day to be identified as author of this work has been asserted
by him in accordance with the Copyright, Designs and Patents Act 1988.
ISBN 978-0-273-73033-0
British Library Cataloguing-in-Publication Data
A catalogue record for this book is available from the British Library
Library of Congress Cataloging-in-Publication Data
Day, Alastair L.
Mastering financial mathematics in Microsoft Excel : a practical guide for
business calculations / Alastair Day. -- 2nd ed.
p. cm. -- (Market editions)
Includes index.
ISBN 978-0-273-73033-0 (pbk.)
1. Business mathematics. 2. Microsoft Excel (Computer file) I. Title.
HF5691.D39 2010
650.0285'554--dc22
2010005343
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 either the prior
written permission of the publisher or a licence permitting restricted copying
in the United Kingdom issued by the Copyright Licensing Agency Ltd,
Saffron House, 6–10 Kirby Street, London EC1N 8TS. This book may not be
lent, resold, hired out or otherwise disposed of by way of trade in any form of
binding or cover other than that in which it is published, without the prior
consent of the Publishers.
Microsoft product screen shots reprinted with permission from Microsoft
Corporation.
10 9 8 7 6 5 4 3 2 1
14 13 12 11 10
Typeset in Garamond 11.5/13.5pt by 30
Printed and bound in Great Britain by Ashford Colour Press Ltd, Gosport, Hants
The Publisher’s policy is to use paper manufactured from sustainable forests.
Contents
Acknowledgements x
About the author xi
Conventions xii
Overview xiii
Warranty and disclaimer xv
1 Introduction 1
Overview 3
Common Excel errors 4
Systematic design method 6
Auditing 10
Summary 12
2 Basic financial arithmetic 13
Simple interest 15
Compound interest 19
Nominal and effective rates 24
Continuous discounting 26
Conversions and comparisons 27
Exercise 28
Summary 28
3 Cash flows 29
Net present value 31
Internal rate of return 34
XNPV and XIRR 37
XNPV periodic example 38
Modified internal rate of return 39
Exercise 41
Summary 41
v
Contents
4 Bonds calculations 43
Description 45
Cash flows 48
Zero coupons 50
Yield 51
Yield to call 51
Price and yield relationship 53
Yield curve pricing 53
Other yield measures 55
Yield measures 57
Exercise 59
Summary 59
5 Bonds risks 61
Risks 63
Duration 66
Convexity 70
Comparison 75
Exercise 77
Summary 77
6 Floating rate securities 79
Floating rates 81
Characteristics of interest rate securities 82
Yield evaluation 84
Coupon stripping 89
Exercise 90
Summary 91
7 Amortization and depreciation 93
Amortization 95
Full amortization 97
Delayed payments 97
Sum of digits 100
Straight line and declining balance depreciation 101
UK declining balance method 103
Double declining balance depreciation 104
French depreciation 105
Exercise 107
Summary 108
vi
Contents
8 Swaps 109
Definitions 111
How swaps save money 114
Advantages of swaps 115
Terminating interest rate swaps 116
Implicit credit risk 117
Worked single currency swap 117
Valuation 119
Cross currency swap 120
Worked example 121
Swaptions 122
Exercise 124
Summary 124
9 Forward interest rates 125
Definitions 127
Example forward rates 127
Hedging principles 130
Forward rate agreement 131
Yield curves 134
Exercise 138
Summary 138
10 Futures 139
Futures market 141
Terminology 142
Benefits 143
Clearinghouse operation 144
Bond futures 145
Hedging mechanisms 145
Hedging example one 147
Hedging example two 149
Exercise 152
Summary 152
11 Foreign exchange 153
Risk 155
Spot rates 157
Longer dates 161
Equivalence 162
Comparisons and arbitrage 164
Exercise 165
Summary 165
vii
Contents
12 Options 167
Description 169
Terminology 170
Underlying asset 172
Call options 172
Put options 176
Example 178
Covered call 180
Insurance using a stock and a long put 182
Pricing models 182
Black Scholes model 184
Call put parity 187
Greeks 188
Binomial models 191
Comparison to Black Scholes 194
Exercise 198
Summary 198
13 Real options 199
Real options 201
Black Scholes model 202
Binomial model 203
Exercise 205
Summary 205
14 Valuation 207
Valuation methods 209
Assets 210
Market methods 211
Multi-period dividend discount models 213
Free cash flow valuation 215
Adjusted present value 224
Economic profit 227
Exercise 229
Summary 230
15 Leasing 231
Economics of leasing 233
Interest rates 235
Classification 236
Amortization 241
Accounting 242
Settlements 243
viii
Contents
Lessor evaluation 246
Lessee evaluation 250
Exercise 252
Summary 253
16 Basic statistics 255
Methods 257
Descriptive statistics 257
Probability distributions 270
Sampling/Central Limit Theorem 277
Hypothesis testing 282
Correlation and regression 293
LINEST function 302
Exercise 304
Summary 304
Appendices 307
1 Exercise answers, functions list, software installation and licence 309
2 An introduction to Microsoft® Office 2007 (Office 12) 345
Index 359
ix