Financial Modelling in Microsoft® Excel®
Duration: 2 days
 Introduction to Excel as an Analysis Tool
 Working with Cash Flows
 Basic Statistical and Financial Functions
 Random Number Generation and Monte Carlo Simulation
 Using Goal Seek/Solver for Optimization Problems
 Recording and Using Visual Basic Macros
The objective of this highly practical, workshop is to give you a thorough introduction to and
handson, practical experience with Excel as a financial analysis tool.
We start with a general overview of the Excel environment and its standard and addin 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 builtin 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 “ValueatRisk” 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.
 Handson 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 lognormal distributions
 Handson 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
 Handson Workshop:
Sampling from selected distributions and pricing of different instruments
 Handson Workshop:
Sampling from an empirical distribution and calculation of ValueatRisk on
longer horizons
Day Two
09.00  09.15 Recap
09.15  12.00 Using the Goalseek 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 Nonlinear Optimization Problems
 Handson 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 addin Functions
 Eventdriven Actions
 Sharing your Programs with other Users
 Working with Forms and ActiveX objects
 Creating a Simple User Dialogue
 Handson workshop: Programming and implementing simple userdefined functions
 Running Recorded Macros
 Handson Workshop:
Recording and Running Macros
 Working with Objects
 Enhancing and Debugging the Recorded Macros
 Programming and Implementing Userdefined Functions
Summary, Evaluation and Termination of the Workshop
