Advanced Excel™ Workshop 1  Yield Curve Estimation and Principal Components Analysis
Duration: 2 days
 Bootstrapping and Curve Fitting
 Implementing Term Structure Models
 Analyzing Swaps and Interest Rate Options
 Principal Components Analysis
 Simple Monte Carlo Simulation
 Optimization of Factor Portfolios
 GARCH Volatility Modelling
The objective of this advancedlevel workshop is to give the participants handson experience with
interest rate modelling in MICROSOFT™ Excel™ and Visual Basic™ for Applications. We start with an
introduction to the VBA environment and demonstrate how subroutines and user functions can be
programmed, tested and implemented. The participants will then program a pricing function in VBA that
will be used in conjunction with the Excel Solver to "Bootstrap" and smooth swap and bond curves using
the "cubic splining" technique. Further, we shall program and implement a stochastic term structure
model using the "forward induction" technique. The model is then calibrated to match the observed term
structure and observed volatilities. We then use this model to price selected instruments such as caps,
swaptions and CMS swaps. We then explain and demonstrate how "Principal components analysis" can be
used to decompose historical terms structure variations into independent factors. Participants estimate
these factors and use them in conjunction with the Excel Solver to create "factor portfolios".
Participants will also learn how to combine PCA with simple, Excelbased Monte Carlo simulation to
create return distributions for the calculation of "ValueatRisk" and other risk measures. Finally, we
explain the GARCH methodology for estimating nonstationary volatility. Participants will fit a GARCH
model to a historical series of short term interest rates and use the results to make volatility
forecasts for option pricing and other purposes.
Day One
09.00  09.15 Welcome and Introduction
09.15  12.00 Yield Curve Estimation
 Yield Curves, Par Curves and Zero Coupon Curves

Estimation Techniques
 NelsonSiegel
 Bootstrapping
 Cubic Spline
 Computer Workshop (Excel/VB): Participants Program and Test Yield Curve Estimation Routines in
Excel/VB
Term Structure Models

Programming and Implementing Term Structure Models
 Computer Workshop
12.00  13.00 Lunch
13.00  16.30 Analyzing Swaps and Interest Rate Options
 Pricing Standards Swaps
 Pricing Caps, Floors and Swaptions Using Analytical and Numerical Approaches
 Computer Workshop: Participants Analyse Selected Swap and IRO Structures Using BDT and other
Models
Pricing Complex Instruments

Pricing Complex Interest Rate Products
 Capped Floaters, Leveraged Capped Floaters, Cancellation Swaps, Constant Maturity Swaps
etc.
 "Exotic" structures (barrier, digital, lookback)
 Computer Workshop: Participants Price Selected Complex Interest Rate Products
Day Two
09.00  12.00 GARCH Volatility Modelling
 General Introduction to GARCH Modelling
 Estimating Volatility Using MA, EWMA and GARCH(1,1)
 Estimating VaR and "Tail Risk" Using GARCH
 Combining GARCH with EVT
 Computer Workshop: Fit MA, EWMA and GARCH(1,1) Models to Stock Return and Interest Rate Series
and Calculate ValueatRisk
Simple Monte Carlo Simulation
 Sampling from Normal and LogNormal Distributions
 Simulating a Stochastic Differential Equation
 Calculating VaR for Portfolio
 Computer Workshop: Participants Program Simple Monte Carlo Application
12.00  13.00 Lunch
13.00  16.30 Principal Components Analysis
 Common Factors Affecting Bond Returns
 Overview of MultiFactor Interest Rate Risk Models

The Factor Model
 Eigenvalues, Eigenvectors and the Yield Curve
 Calculating and Interpreting Factor Loadings
 Using the Factor Model to Calculate VaR
 Factor Immunization for Hedging Yield Curve Fluctuations
 Monte Carlo Simulation Using PCA
 Computer Workshop: Participants use PCA to Estimate Risk Factors and Construct Optimal,
FactorImmunized Portfolios
Evaluation and Termination of the Workshop
