A treasurer has the option of borrowing in dollars or euros. Construct a

model to ascertain if there is a potential saving in one of the currencies over

91 days. Also what is the breakeven exchange rate? The data is:

Spot EUR / USD 1.4000

Swap 0.0100

USD Interest 3.000%

EUR Interest 5.000%

EUR Margin 0.500%

Amount 10,000,000.00

FYI! Look at p189 pdf file attachment

a practical guide for business calculations ALASTAIR L. DAY Alastair Day has worked in the finance industry for more than 25 years in treasury and marketing functions and was formerly a director of a vendor leasing company specializing in the IT and technology industries. After rapid growth, the directors sold the enterprise to a public company and he established Systematic Finance plc as a consultancy specializing in: financial modelling - review, design, build and audit training in financial modelling, corporate finance, leasing and credit analysis on an in-house and public basis finance and operating lease structuring as a consultant and lessor Alastair is author of a number of books including three published by FT Prentice Hall: Mastering Financial Modelling, Mastering Risk Modelling and The Financial Director's Guide to Purchase Leasing. Alastair has a degree in Economics and German from London University together with an MBA and is an associate lecturer of finance with the Open University Business School. Excel a practical guide for business calculations Tools enabling managers to carry out financial calculations have evolved in the last 20 years from tables through calculators to programs on PCs and personal organisers. Today, the majority of those in finance have Excel on their desks and increasingly on their laptops or pocket computers. Mastering Financial Mathematics in Microsoft Excel provides a comprehensive set of tools and methods to apply Excel to solving mathematical problems. Alastair Day clearly explains the basic calculations for mathematical finance backed up with simple templates for further use and development, together with numerous examples and exercises. Providing an explanation of key financial formulas and subject areas, the book includes a CD which: allows you to work step-by-step through each of the chapters and examples shows the use of formulas using straightforward Excel templates introduces examples and exercises for extension work provides a menu of basic templates for further development enables you to practise, develop and improve your efficiency and competence with Excel. Mastering Financial Mathematics in Microsoft Excel will be invaluable in helping improve your Excel skills and understand the underlying financial concepts. mastering financial mathematics in mastering financial mathematics in Microsoft Excel a practical guide for business calculations Mastering Financial Mathematics in Microsoft Excel is a practical guide to using Excel for financial mathematics. The explanation of basic calculations and use of examples and exercises will improve your abilities with Excel and help find solutions to financial problems in the following areas: time value of money cash flows bonds calculations bonds risks Microsoft Excel floating rate securities amortization and depreciation swaps forward interest rates futures foreign exchange a practical guide for business calculations options real options valuation leasing ALASTAIR L. DAY Mastering Financial Mathematics in Microsoft Excel will be invaluable for: mastering financial mathematics in Microsoft mastering financial mathematics in Microsoft mastering financial mathematics in Microsoft Excel Excel CFOs and finance directors financial controllers financial analysts and executives accountants corporate finance specialists treasury managers www.pearson-books.com m a r k e t e d i t i o n s Visit our website at www.pearson-books.com business and MBA students DAY pearson-books.com - who to read, what to know and where to 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. risk managers ---------------------- FINANCE ---------------------- Visit our website at An imprint of Pearson Education market editions om collection.c g-softwarewww.tradin onch Skype: iii.g gmail.com, iii.gonch@ market editions m a r k e t e d i t i o n s FOR SALE & EXCHANGE www.trading-software-collection.com Subscribe for FREE download more stuff. Mirrors: www.forex-warez.com www.traders-software.com Contacts andreybbrv@gmail.com andreybbrv@hotmail.com andreybbrv@yandex.ru Skype: andreybbrv ICQ: 709e-co33ection.com 664 ll ar g-softw www.tradin : iii.gonch .com, Skype mail iii.gonch@g MFME_A01.QXD 29/6/05 12:24 pm Page i Mastering Financial Mathematics in Microsoft Excel llection.com software-co gch www.tradin ype: iii.gon ail.com, Sk m iii.gonch@g 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 nance 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 nd out more about Pearson Education publications, or tell us about the books you'd like to nd, you can visit us at www.pearsoned.co.uk llection.com software-co gch www.tradin ype: iii.gon ail.com, Sk m iii.gonch@g 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 llection.com software-co gch www.tradin ype: iii.gon ail.com, Sk m iii.gonch@g MFME_A01.QXD 29/6/05 12:24 pm Page iv 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 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. llection.com software-co gch www.tradin ype: iii.gon ail.com, Sk m iii.gonch@g MFME_A01.QXD 29/6/05 12:24 pm Page v Contents Acknowledgements About the author Conventions Overview Warranty and disclaimer x xi xii xiii xv 1 Introduction Overview Common Excel errors Systematic design method Auditing Summary 1 3 4 6 10 12 2 Basic financial arithmetic Simple interest Compound interest Nominal and effective rates Continuous discounting Conversions and comparisons Exercise Summary 13 15 19 25 27 27 28 28 3 Cash flows Net present value Internal rate of return XNPV and XIRR XNPV periodic example Modified internal rate of return Exercise Summary 29 31 33 37 38 39 41 41 llection.com software-co gch www.tradin ype: iii.gon ail.com, Sk m iii.gonch@g v MFME_A01.QXD 29/6/05 12:24 pm Page vi Contents 4 Bonds calculations Description Cash flows Zero coupons Yield Yield to call Price and yield relationship Yield curve pricing Other yield measures Yield measures Exercise Summary 43 45 48 50 51 51 53 54 55 57 59 60 5 Bonds risks Risks Duration Convexity Comparison Exercise Summary 61 63 66 70 75 76 77 6 Floating rate securities Floating rates Characteristics of interest rate securities Yield evaluation Coupon stripping Exercise Summary 79 81 82 84 88 89 91 7 Amortization and depreciation Amortization Full amortization Delayed payments Sum of digits Straight line and declining balance depreciation UK declining balance method Double declining balance depreciation French depreciation Exercise Summary vi llection.com software-co gch www.tradin ype: iii.gon ail.com, Sk m iii.gonch@g 93 95 97 97 100 102 103 104 104 107 108 MFME_A01.QXD 29/6/05 12:24 pm Page vii Contents 8 Swaps Definitions How swaps save money Advantages of swaps Terminating interest rate swaps Implicit credit risk Worked single currency swap Valuation Cross currency swap Worked example Swaptions Exercise Summary 109 111 114 115 116 117 117 120 120 121 123 124 125 9 Forward interest rates Definitions Example forward rates Hedging principles Forward rate agreement Yield curves Exercise Summary 127 129 129 132 133 136 140 141 10 Futures Futures market Terminology Benefits Clearinghouse operation Bond futures Hedging mechanisms Hedging example one Hedging example two Exercise Summary 143 145 146 147 148 149 149 151 153 156 156 11 Foreign exchange Risk Spot rates Longer dates Equivalence Comparisons and arbitrage Exercise Summary 157 159 161 165 166 168 168 169 llection.com software-co gch www.tradin ype: iii.gon ail.com, Sk m iii.gonch@g vii MFME_A01.QXD 29/6/05 12:24 pm Page viii Contents 12 Options Description Terminology Underlying asset Call options Put options Example Covered call Insurance using a stock and a long put Pricing models Black Scholes model Call put parity Greeks Binomial models Comparison to Black Scholes Exercise Summary 13 Real options Real options Black Scholes model Binomial model Exercise Summary 203 205 206 207 209 209 14 Valuation Valuation methods Assets Market methods Multi-period dividend discount models Free cash flow valuation Adjusted present value Economic profit Exercise Summary 211 213 214 215 217 219 228 231 233 234 15 Leasing Economics of leasing Interest rates Classification Amortization Accounting viii 171 173 173 175 176 180 183 184 186 186 188 190 191 194 198 202 202 235 237 238 240 244 245 llection.com software-co gch www.tradin ype: iii.gon ail.com, Sk m iii.gonch@g MFME_A01.QXD 29/6/05 12:24 pm Page ix Contents Settlements Lessor evaluation Lessee evaluation Exercise Summary 16 Basic statistics Methods Descriptive statistics Probability distributions Sampling/Central Limit Theorem Hypothesis testing Correlation and regression LINEST function Exercise Summary 247 250 254 256 257 259 261 261 273 281 286 297 305 307 308 Appendix 309 Index 345 llection.com software-co gch www.tradin ype: iii.gon ail.com, Sk m iii.gonch@g ix MFME_A01.QXD 29/6/05 12:24 pm Page x Acknowledgements I would like to thank Angela, Matthew and Frances, for their support and assistance with this book. In addition, Richard Stagg and Kate Salkilld of Pearson Education have provided valuable support and backing for this project. x llection.com software-co gch www.tradin ype: iii.gon ail.com, Sk m iii.gonch@g MFME_A01.QXD 29/6/05 12:24 pm Page xi About the author Alastair Day has worked in the finance industry for more than 25 years in treasury and marketing functions and was formerly a director of a vendor leasing company specializing in the IT and technology industries. After rapid growth, the directors sold the enterprise to a public company and he established Systematic Finance plc as a consultancy specializing in: Financial modelling - review, design, build and audit Training in financial modelling, corporate finance, leasing and credit analysis on an in-house and public basis Finance and operating lease structuring as a consultant and lessor Alastair is author of a number of books including three published by FT Prentice Hall: Mastering Financial Modelling, Mastering Risk Modelling and The Financial Director's Guide to Purchasing Leasing Alastair has a degree in Economics and German from London University, an MBA from the Open University Business School, and is an associate lecturer in corporate finance with the OUBS. llection.com software-co gch www.tradin ype: iii.gon ail.com, Sk m iii.gonch@g xi MFME_A01.QXD 29/6/05 12:24 pm Page xii Conventions The main part of the text is set in AGaramond, whereas entries are set in Courier. For example: Enter the Scenario Name as Base Case Items on the menu bars also shown in Courier. For example: Select Tools, Goal Seek The names of functions are in Courier capitals. This is the payment function, which requires inputs for the interest rate, number of periods, present value and future value. =PMT(INT,NPER,PV,FV,TYPE) Cell formulas are also shown in Courier. For example: =IF($C$75=1,IF($B25>C$22,$B25-C$22-C$23,C$23),IF($B25