Mastering Financial Mathematics in Microsoft Excel: A practical guide to business calculations (The Mastering Series)

391 Pages·2015·15.31 MB·English

by  Alastair Day

Mastering Financial Mathematics
®
in Microsoft Excel
A01_DAY7506_03_SE_FM.indd 1 07/13/15 9:57 AM
A01_DAY7506_03_SE_FM.indd 2 07/13/15 9:57 AM
Mastering Financial
Mathematics in
®
Microsoft Excel
A practical guide for business calculations
Third Edition
AlAsTAir l. DAy
A01_DAY7506_03_SE_FM.indd 3 07/13/15 9:57 AM
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
Third edition 2015 (print and electronic)
© Pearson education Limited 2005
© systematic Finance Limited 2010, 2015 (print and electronic)
isBn: 978-1-292-06750-6 (print)
978-1-292-06752-0 (PdF)
978-1-292-06751-3 (etext)
978-1-292-06753-7 (ePub)
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
the print publication is protected by copyright. Prior to any prohibited reproduction, storage in a
retrieval system, distribution or transmission in any form or by any means, electronic, mechanical,
recording or otherwise, permission should be obtained from the publisher or, where applicable, a
licence permitting restricted copying in the united Kingdom should be obtained from the copyright
Licensing agency Ltd, saffron House, 6–10 Kirby street, London ec1n 8ts.
the ePublication is protected by copyright and must not be copied, reproduced, transferred, distributed,
leased, licensed or publicly performed or used in any way except as specifically permitted in writing by the
publishers, as allowed under the terms and conditions under which it was purchased, or as strictly permit-
ted by applicable copyright law. any unauthorised distribution or use of this text may be a direct infringe-
ment of the author’s and the publisher’s rights and those responsible may be liable in law accordingly.
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 endorsement of this book by such owners.
microsoft product screen shots reprinted with permission from microsoft corporation.
Pearson education is not responsible for the content of third-party internet sites.
10 9 8 7 6 5 4 3 2 1
19 18 17 16 15
Print edition typeset in Garamond 11.5/13.5pt by 76
Printed in malaysia
note tHat anY PaGe cross reFerences reFer to tHe Print edition
A01_DAY7506_03_SE_FM.indd 4 07/13/15 9:57 AM
Contents
Acknowledgements x
About the author xi
Conventions xii
Overview xiii
How to use this book xv
Warranty and disclaimer xvi
1 Introduction 1
overview 3
common excel errors 4
systematic design method 6
auditing 10
summary 14
2 Basic financial arithmetic 15
simple interest 17
compound interest 21
multiple payments 27
differing interest rates 29
nominal and effective rates 32
continuous discounting 33
conversions and comparisons 35
exercise 36
summary 36
3 Cash flows 37
net present value 39
Varying interest rates 42
internal rate of return 43
XnPV and Xirr 46
XnPV periodic example 47
v
A01_DAY7506_03_SE_FM.indd 5 07/13/15 9:57 AM
Contents
modified internal rate of return 48
exercise 50
summary 50
4 Bonds calculations 51
description 53
cash flows 56
Zero coupons 58
Yield 59
Yield to call 59
Price and yield relationship 61
Yield curve pricing 61
other yield measures 63
Yield measures 65
exercise 67
summary 68
5 Bond risks 69
risks 71
duration 74
convexity 78
comparison 83
exercise 85
summary 86
6 Floating rate securities 87
Floating rates 89
characteristics of interest rate securities 90
Yield evaluation 92
coupon stripping 97
exercise 98
summary 99
7 Amortisation and depreciation 101
amortisation 103
Full amortisation 105
delayed payments 105
sum of digits 108
straight line and declining balance depreciation 109
uK declining balance method 111
double declining balance depreciation 112
vi
A01_DAY7506_03_SE_FM.indd 6 07/13/15 9:57 AM
Contents
French depreciation 113
exercise 115
summary 116
8 Swaps 117
definitions 119
How swaps save money 122
advantages of swaps 123
terminating interest rate swaps 124
implicit credit risk 125
Worked single currency swap 125
Valuation 126
cross currency swap 128
Worked example 129
swaptions 130
exercise 132
summary 132
9 Forward interest rates 133
definitions 135
example forward rates 135
Hedging principles 138
Forward rate agreement 139
Yield curves 142
exercise 146
summary 146
10 Futures 147
Futures market 149
terminology 150
Benefits 151
clearinghouse operation 152
Bond futures 153
Hedging mechanisms 153
Hedging example 1 155
Hedging example 2 157
exercise 160
summary 160
vii
A01_DAY7506_03_SE_FM.indd 7 07/13/15 9:57 AM
Contents
11 Foreign exchange 161
risk 163
spot rates 165
Longer dates 169
equivalence 170
comparisons and arbitrage 172
exercise 173
summary 173
12 Options 175
description 177
terminology 178
underlying asset 180
call options 180
Put options 184
example 186
covered call 188
insurance using a stock and a long put 190
Pricing models 190
Black–scholes model 192
call put parity 195
Greeks 196
Binomial models 199
comparison with Black–scholes 202
exercise 206
summary 206
13 Real options 207
real options 209
Black–scholes model 210
Binomial model 211
exercise 213
summary 214
14 Valuation 215
Valuation methods 217
assets 218
market methods 219
multi-period dividend discount models 221
Free cash flow valuation 223
adjusted present value 232
viii
A01_DAY7506_03_SE_FM.indd 8 07/13/15 9:57 AM
Contents
economic profit 235
exercise 238
summary 238
15 Leasing 239
economics of leasing 241
interest rates 243
classification 244
amortisation 249
accounting 250
settlements 251
Lessor evaluation 254
Lessee evaluation 258
exercise 260
summary 261
16 Basic statistics 263
methods 265
descriptive statistics 265
Probability distributions 278
sampling/central Limit theorem 285
Hypothesis testing 290
correlation and regression 301
Linest function 310
exercise 312
summary 312
Appendices 313
1 Exercise answers, functions list, software installation and licence 315
2 An introduction to Microsoft® Office 351
index 367
ix
A01_DAY7506_03_SE_FM.indd 9 07/13/15 9:57 AM