Microsoft Excel mastering a practical guide for business

372 Pages·2008·7.94 MB·English

®
mastering financial mathematics in Microsoft® Excel mastering financial mathematics in Microsoft Excel mastering financial mathematics in Microsoft® Excel
a practical guide for business calculations a practical guide for business calculations
a practical guide for business calculations mastering
ALASTAIR L. DAY Tools enabling managers to carry out financial calculations have evolved in the last 20 years from tables M
prascteicrainl gg uFidnea ntoci auls Minagt hEexmceal tficosr finin aMniciraol smofatt®heEmxcaetlicis .a The
Alastair Day has worked in the finance industry for more than 25 years through calculators to programs on PCs and personal organisers. Today, the majority of those in finance financial explanation of basic calculations and use of examples and
in treasury and marketing functions and was formerly a director of a have Excel on their desks and increasingly on their laptops or pocket computers. exercises will improve your abilities with Excel and help find
vendor leasing company specializing in the IT and technology industries. solutions to financial problems in the following areas:
After rapid growth, the directors sold the enterprise to a public company Mastering Financial Mathematics in Microsoft ® Excel provides a comprehensive set of tools and
iann:d he established Systematic Finance plc as a consultancy specializing methods to apply Excel to solving mathematical problems. Alastair Day clearly explains the basic • time value of money
• financial modelling – review, design, build and audit calculations for mathematical finance backed up with simple templates for further use and development, mathematics in • cash flows
• training in financial modelling, corporate finance, leasing and together with numerous examples and exercises. • bonds calculations
credit analysis on an in-house and public basis • bonds risks
• finance and operating lease structuring as a consultant and lessor Providing an explanation of key financial formulas and subject areas, the book includes a CD which: • floating rate securities
Alastair is author of a number of books including three published by FT • allows you to work step-by-step through each of the chapters and examples ® • amortization and depreciation
Prentice Hall: Mastering Financial Modelling, Mastering Risk Modelling • shows the use of formulas using straightforward Excel templates Microsoft Excel • swaps
and The Financial Director’s Guide to Purchase Leasing. • introduces examples and exercises for extension work • forward interest rates
Alastair has a degree in Economics and German from London University • provides a menu of basic templates for further development • futures
t
Oopgenth Uenr iwveitrhs itayn B MusBinAe sasn dS cish oaonl .associate lecturer of finance with the • enables you to practise, develop and improve your efficiency and competence with Excel. • foreign exchange
• options
Mastering Financial Mathematics in Microsoft ® Excel will be invaluable in helping improve your Excel a practical guide for business calculations • real options
skills and understand the underlying financial concepts. • valuation
• leasing
Mastering Financial Mathematics in Microsoft ® Excel will be
ALASTAIR L. DAY invaluable for:
• CFOs and finance directors
• financial controllers
• financial analysts and executives
• accountants
• corporate finance specialists
• treasury managers
• risk managers
Visit our website at ––––––––––––––––––––––
FINANCE • business and MBA students
––––––––––––––––––––––
www.pearson-books.com
Visit our website at
pearson-books.com – who to read, what to know and where to www.pearson-books.com
go in the world of finance. Find out more about the people and
ideas that can make you, your business and your investments
more effective. An imprint of Pearson Education
m a r k e t e d i t i o n s m a r k e t e d i t i o n s m a r k e t e d i t i o n s m a r k e t e d i t i o n s
®
mastering financial mathematics in Microsoft Excel DAY
FOR SALE & EXCHANGE

www.trading-software-collection.com

Subscribe for FREE download more stuff.

Mirrors:

www.forex-warez.com
www.traders-software.com

Contacts
MFME_A01.QXD 29/6/05 12:24 pm Page i
Mastering Financial Mathematics
®
in Microsoft Excel
MFME_A01.QXD 29/6/05 12:24 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
MFME_A01.QXD 29/6/05 12:24 pm Page iii
market editions
Mastering Financial
Mathematics in
®
Microsoft Excel
A practical guide for business calculations
ALASTAIR L. DAY
MFME_A01.QXD 29/6/05 12:24 pm Page iv
PEARSON EDUCATION LIMITED
Edinburgh Gate
Harlow CM20 2JE
Tel: [phone]
Fax: [phone]
Website: www.pearsoned.co.uk
First published in Great Britain in 2005
© Pearson Education Limited 2005
ISBN 0 273 68866 9
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
A catalog record for this book is available from the Library of Congress
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,
90 Tottenham Court Road, London W1T 4LP. 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.
All trademarks used herein are the property of their respective owners. The use of
any trademark in this text does not vest in the author or publisher any trademark
ownership rights in such trademarks, nor does the use of such trademarks imply
any affiliation with or endoresement of this book by such owners.
10 9 8 7 6 5 4 3 2 1
09 08 07 06 05
Typeset in Garamond 11.5/13.5pt by 30
Printed and bound by Bell and Bain Ltd, Glasgow
The Publishers’ policy is to use paper manufactured from sustainable forests.
MFME_A01.QXD 29/6/05 12:24 pm Page v
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 25
Continuous discounting 27
Conversions and comparisons 27
Exercise 28
Summary 28
3 Cash flows 29
Net present value 31
Internal rate of return 33
XNPV and XIRR 37
XNPV periodic example 38
Modified internal rate of return 39
Exercise 41
Summary 41
v
MFME_A01.QXD 29/6/05 12:24 pm Page vi
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 54
Other yield measures 55
Yield measures 57
Exercise 59
Summary 60
5 Bonds risks 61
Risks 63
Duration 66
Convexity 70
Comparison 75
Exercise 76
Summary 77
6 Floating rate securities 79
Floating rates 81
Characteristics of interest rate securities 82
Yield evaluation 84
Coupon stripping 88
Exercise 89
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 102
UK declining balance method 103
Double declining balance depreciation 104
French depreciation 104
Exercise 107
Summary 108
vi
MFME_A01.QXD 29/6/05 12:24 pm Page vii
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 120
Cross currency swap 120
Worked example 121
Swaptions 123
Exercise 124
Summary 125
9 Forward interest rates 127
Definitions 129
Example forward rates 129
Hedging principles 132
Forward rate agreement 133
Yield curves 136
Exercise 140
Summary 141
10 Futures 143
Futures market 145
Terminology 146
Benefits 147
Clearinghouse operation 148
Bond futures 149
Hedging mechanisms 149
Hedging example one 151
Hedging example two 153
Exercise 156
Summary 156
11 Foreign exchange 157
Risk 159
Spot rates 161
Longer dates 165
Equivalence 166
Comparisons and arbitrage 168
Exercise 168
Summary 169
vii
MFME_A01.QXD 29/6/05 12:24 pm Page viii
Contents
12 Options 171
Description 173
Terminology 173
Underlying asset 175
Call options 176
Put options 180
Example 183
Covered call 184
Insurance using a stock and a long put 186
Pricing models 186
Black Scholes model 188
Call put parity 190
Greeks 191
Binomial models 194
Comparison to Black Scholes 198
Exercise 202
Summary 202
13 Real options 203
Real options 205
Black Scholes model 206
Binomial model 207
Exercise 209
Summary 209
14 Valuation 211
Valuation methods 213
Assets 214
Market methods 215
Multi-period dividend discount models 217
Free cash flow valuation 219
Adjusted present value 228
Economic profit 231
Exercise 233
Summary 234
15 Leasing 235
Economics of leasing 237
Interest rates 238
Classification 240
Amortization 244
Accounting 245
viii