# Advanced Excel™ Workshop 1 - Yield Curve Estimation and Principal Components Analysis

Duration:
2 days
Location:
Prague, NH Hotel Prague
• 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 purpose of this advanced-level workshop is to give the participants hands-on 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 sub-routines 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, Excel-based Monte Carlo simulation to create return distributions for the calculation of "Value-at-Risk" and other risk measures. Finally, we explain the GARCH methodology for estimating non-stationary 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.
• Yield Curves, Par Curves and Zero Coupon Curves
• Estimation Techniques
• Nelson-Siegel
• 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
• BDT, Hull-White, BGM
• Computer Workshop

## 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

## 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 Value-at-Risk

## Simple Monte Carlo Simulation

• Sampling from Normal and Log-Normal Distributions
• Simulating a Stochastic Differential Equation
• Calculating VaR for Portfolio
• Computer Workshop: Participants Program Simple Monte Carlo Application

## 13.00 - 16.30 Principal Components Analysis

• Common Factors Affecting Bond Returns
• Overview of Multi-Factor Interest Rate Risk Models
• The Factor Model
• Eigenvalues, Eigenvectors and the Yield Curve 