Alastair Day - Mastering Financial Mathematics in Microsoft Excel: A practical guide to business calculations

I would like to thank Angela, Matthew and Frances for their support and assistance with the completion of this book. As in all the previous projects, Pearson Education have provided excellent support and backing for this project.

Alastair Day has worked in the finance industry for more than 30 years in treasury and marketing functions and was formerly a director of a vendor leasing company specialising in the IT and technology industries. After rapid growth, the directors sold the enterprise to a public company and he established Systematic Finance as a consultancy specialising in:

• financial modelling educate, design, build, audit and review;
• training in financial modelling, corporate finance, leasing and credit analysis on an in-house and public basis throughout Europe, the Middle East, Asia, Africa and the Americas;
• finance and operating lease structuring as a consultant and lessor.

Alastair is author of four modelling books published by FT Prentice Hall, namely Mastering Financial Modelling , Mastering Risk Modelling , Mastering Financial Mathematics in Microsoft Excel and Mastering Financial Mathematics in Excel , apart from a range of other books and publications on financial analysis and leasing.

Alastair has a degree in Economics and German from the University of London and an MBA from the Open University Business School.

• The main part of the text is set in Garamond, whereas entries are set in Courier. For example:
  

  

• Items on the menu bars are also shown in Courier. For example:
  

  

• 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:
  

  

• Cell formulas are also shown in Courier. For example:
  

  

• Equations are formed with the Equation Editor and are shown in normal notation. For example, net present value:
  

  

• Genders. The use of he or him refers to masculine or feminine, and this is used for simplicity to avoid repetition.

More than 25 years ago I used an early Hewlett Packard 38C to calculate interest rates and analyse cash flows and then progressed to the state of the art programmable HP 41C with a printer, magnetic strip reader and plug-in finance and mathematics packs. This was an early alphanumeric calculator, which gave me the opportunity to program lease versus purchase or lessor lease evaluation cash flows and structure more complex lease transactions. This was before IBM launched the personal computer in the early 1980s.

Since that time I have used and programmed other calculators, such as the HP 12C, HP 17BII, HP 19B and TI BAII Plus, which all provide dedicated user screens and allow you to undertake financial mathematics. While calculators are much easier to use than tables or earlier methods they can be difficult to use without making simple errors. When I run training courses on the basic financial calculations the main drawback is only too obvious: you cannot see your inputs or check the interim calculations, and therefore delegates always want to see a map of the variables to understand the answer.

While I have continued to use financial calculators, which have now been augmented with applications on mobile phones using Apple IOS or Android, and have in the past created programs in Basic, the most comprehensive medium for financial mathematics has grown to be spreadsheets such as Microsoft Excel. I first used Lotus 1-2-3 in 1988 and Excel 3.0 in 1990, and through various upgrades Excel grew into the latest Microsoft Office, which remains little changed in the core financial functions through to todays versions.

Given that almost everybody engaged in finance functions has Excel on their desktops, and increasingly on their laptops, mobile phones or tablet and notebook computers, the objective of this book is to explain the basic calculations for mathematical finance backed up by simple templates for further use and development, together with examples and exercises. If you work through each of the chapters in the book, repeating the models and trying the exercises, you will improve your Excel skills and obtain a better grasp of the underlying financial concepts.

My other modelling books published by FT Prentice Hall, Mastering Financial Modelling , Mastering Risk Modelling and Mastering Cash Flow and Valuation Modelling , provide alternative models for some of the topics in this book. These combine financial theory with model design, using ideas of best international practice coupled with tried and tested methods of auditing and testing. This book adheres to systematic spreadsheet best practice and adopts the same standards, method and layout.

The key objectives for this book are to:

• provide an explanation of key financial formulas and subject areas;
• show the use of the formulas using straightforward Excel templates;
• introduce examples and exercises for extension work;
• provide a library of basic templates for further development.

This book aims to assist two key groups:

• practitioners who want a manual of financial mathematics from which they can gain immediate use and payback;
• business students who need a textbook which is more geared to Excel solutions than some college manuals.

The areas of responsibility where the book should be of interest are:

• CFOs and finance directors;
• financial controllers;
• financial analysts and executives;
• accountants;
• corporate finance specialists;
• treasury managers;
• risk managers;
• academics, business and MBA students.

Therefore, people interested in this book range from a company accountant who wants a reference book to academics and business students who need a financial mathematics manual in Excel. The book has an international bias and provides examples which are relevant to the UK and overseas.

• Install the Excel application templates using the simple SETUP command. The files will install automatically together with a program group and icons. There is a key to the file names at the end of the book.
• Work through each of the chapters repeating the models and examples.
• Use the manual, spreadsheets and templates as a reference guide for further work.
• Practise, develop and improve your efficiency and competence with Excel.