Financial Modelling in Microsoft Excel

Tuesday, September 21

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

Wednesday, September 22

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