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

The objective 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.

 

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

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

12.00 - 13.00 Lunch

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
  • 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, Factor-Immunized Portfolios

Evaluation and Termination of the Workshop