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: +44 (0)1279 623623 Fax: +44 (0)1279 431059 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