Financial Modelling in Microsoft® Excel®

The objective of this highly practical, workshop is to give you a thorough introduction to and hands-on, practical experience with Excel as a financial analysis tool.

We start with a general overview of the Excel environment and its standard and add-in analysis features.

We then show how to create and analyze cash flows of basic financial instruments such as bonds and swaps, and we calculate important key ratios such as yield, duration, convexity etc.

Further, we explain and demonstrate how built-in statistical and analytical tools can be used to calculate variance, covariance, correlation etc., and how they can be used for option pricing and other interesting applications. We also explain and demonstrate how random numbers are generated and how to build up a Monte Carlo simulation. We show how risk measures such as “Value-at-Risk” can be calculated for simple positions and correlated portfolios. The “Solver” will be used to find solutions for optimizing problems (such as the mean/variance optimization), and regression analysis will be used to establish the relationship between series of financial data, e.g. in calculating “Beta” for stocks.

Finally, we give a quick introduction to Visual Basic for Applications (VBA) and show how macros can be recorded and/or individually programmed and tested to enhance the flexibility of Excel.

During the workshop, you will be programming a number of small, practical applications that you can use later. You will also design a small “program” in the Excel/VBA environment.

 

Day One

09.00 - 09.15 Welcome and Introduction

09.15 - 12.00 Introduction to Excel as an Analysis Tool

• Overview of the Excel Environment
• The Excel Object Model
• Things you can do in Excel

Working with Cash Flows

• Tips & Tricks
• Examples
  • Generating cash flows for bonds, swaps and other financial instruments
• Using Date and Time Functions for Dynamic Cash Flow Generation
• Calculating Day Count and Accrued Interest
• Formatting Cash Flows
  • Dates, currencies, borders etc.
• Hands-on Workshop:
      Analysis of Cash Flows

12.00 - 13.00 Lunch

13.00 - 16.30 Basic Statistical and Financial Functions

• Financial Functions
  • Price, yield, internal rate of return
  • Workshop: Calculating key ratios for bonds, swaps, irregular cash flows
• Statistical Functions
  • Average, variance, standard deviation, covariance and correlation
  • Uniform, normal and log-normal distributions
• Hands-on Workshop:
      Calculating “beta” and other financial analytics using statistical functions. Organizing data using “frequency” and other functions

Random Number Generation and Monte Carlo Simulation

• Excel’s Rand() Function
• Sampling from Selected Distributions
• Hands-on Workshop:
      Sampling from selected distributions and pricing of different instruments
• Hands-on Workshop:
      Sampling from an empirical distribution and calculation of Value-at-Risk on longer horizons

Day Two

09.00 - 09.15 Recap

09.15 - 12.00 Using the Goal-seek and Solver for Optimization Problems

• Introduction to Excel’s “Solver”
• Types of Problems that can be Solved
• The Balance Between Constraints and Object Function
• Local and Global Maximum
• Speeding up Convergence
• Examples of Linear and Non-linear Optimization Problems
• Hands-on Workshops:
      Using the “Solver” to find the optimal stock portfolio
      Using the “Solver” to find optimal bond portfolio

12.00 - 13.00 Lunch

13.00 - 16.30 Recording and Using Visual Basic Macros

• Introduction to Visual Basic
• Simple Macro Recording
• Creating Custom add-in Functions
• Event-driven Actions
• Sharing your Programs with other Users
• Working with Forms and Active-X objects
• Creating a Simple User Dialogue
• Hands-on workshop: Programming and implementing simple user-defined functions
• Running Recorded Macros
• Hands-on Workshop:
      Recording and Running Macros
• Working with Objects
• Enhancing and Debugging the Recorded Macros
• Programming and Implementing User-defined Functions

Summary, Evaluation and Termination of the Workshop