Excel Spreadsheet & VBA Modelling for Quantitative Finance
Certificate Course in Financial Engineering
(Special inaugural version for India)
- Objective:
- A FOUR (4) weekend course (35 man hours)
begining April, 2008 (all Saturdays and Sundays).
- Methodology:
- The course will be carried out totally in Excel/VBA spreadsheet environment with each participant using a laptop computer. Special arrangements can be made for those who cannot bring along their notebooks/laptops provided advance notice is given.
- Course Fee:
- The fee for the entire course is: US$800 (USD Eight Hundred) per person for persons from Risk Latte's client organizations (banks) in India and US$3,500 (USD Three Thousand Five Hundred) per person (the "rack rate") for others. A 50% discount will be offered to persons who are full time University students pursuing Engineering and Physical Sciences degrees in India.
Module 1
Excel Spreadsheet Advanced User Defined Functions and Problem Solving
- Overview and use of Mathematical & Statistical Functions;
- Factorial functions and Probability Distribution functions;
- Financial, logical and date functions;
- Handling arrays in Excel and array manipulation;
- Matrix operations and solution of linear equations;
- Data Analysis and advanced tools V Regressions, analysis of variance, random number generations, goal seek; solving optimization schedules using solver;
- Logical and mathematical operations and control structures in Excel;
- Writing code in Excel for solving simple financial modelling problems;
Module 2
VBA Essentials and Code Writing
- Visual Basic Editor and VBE window;
- Using Project Explorer window and setting VBE options;
- Entering code, running procedures and organizing procedures in Modules;
- Simple Code writing and debugging code, debugging VBA code and errors;
- Assignment, logical and mathematical operators;
- Working with Text, message box and input box functions;
- Working with arrays and array manipulation; writing code to compute matrices and matrix operations;
- Variables and Data types and Data manipulation;
- Branching, control structures, Subroutines and the structure of Sub procedures;
- Functions and structure of Function procedures and VBA debugging tools;
Module 3
Quantitative Modelling Foundations
(Using Excel spreadsheet and VBA code)
- Matrix Power, Arrays and Matrix Operations V Variance Covariance Matrix, Correlation Matrix and Dollar VCV Matrix;
- Cholesky Factorization and Principal Components Analysis - estimation of eigensystem matrix;
- Closed form vanilla option valuation functions, Implied Volatility function and numerical integrals;
- Bond payoff functions and bond pricing using Transitions matrices;
- Single asset Monte Carlo simulation in Excel and VBA;
- Multi-asset Monte Carlo simulation in Excel and VBA;
- Cox-Ross-Rubenstein (CRR) tree for equities and FX in Excel and VBA;
Module 4
Quant Modelling for Derivatives
(Using Excel spreadsheet and VBA code)
- Generating Short rate trees with optimization parameters V Black-Derman-Toy (BDT) tree, Hull-White tree, CIR tree and other lognormal trees;
- Asset path Simulation using Poisson and Gamma distributions;
- Vanilla option pricing using Binomial Distribution, Monte Carlo simulation and Closed form models;
- Exotic option pricing (asian, barrier, digital, lookback, range and chooser) using numerical techniques;
- Exotic multi-asset option pricing (rainbow, basket, correlation, best of and worst of, etc.) using numerical techniques;
- Pricing of vanilla and exotic caps and floors using numerical techniques;
Module 5
Product and Term Sheet Pricing
(Using Excel spreadsheet and VBA code)
Around 30 to 40 live term sheets will be priced in Excel + VBA
- Pricing FRNs and inverse FRNs and Accumulators;
- Vanilla and exotic range accrual notes and swaps, CMS spread notes and swaps, and other structured swaps;
- Snowball, snowblade, ratchet and other exotic interest rate notes;
- Variance and volatility swaps, asset swaps and Libor in arrears swap;
- Index linked bull-bear, barrier, digital, Asian, lookback and chooser equity notes;
- Power reverse dual currency note, cliquet notes, KIKO notes
Module 6
Excel Models for Portfolio Optimization
- Solution of a system of linear equations using Matrix algebra;
- Generating efficient frontier for portfolios using constrained optimization and Lagrangian multiplier;
- Sharpe's algorithm to estimate the efficient frontier and mean-variance analysis;
- Portfolio allocation models using Solver and user defined optimization function;
- Algorithmic Trading models and optimization of marginal contribution of risk;
- Optimization of VaR and risk capital allocation for trading and investment.
Risk Latte Company is a financial engineering and a training and education solutions services company based in Hong Kong. The company works with front and middle office professionals (traders, structurers, quants and derivatives sales) in top tier banks, investment banks and hedge funds to build and validate quantitative models in the areas exotic derivatives, structured products and risk management. The Company is also actively engaged in carrying out extensive training programmes for top tier banks, investment banks, hedge funds and other financial institutions all over Asia (including Japan) in the areas of Exotic Derivatives and Structured Products Modelling and Pricing, Asset Management & Portfolio Engineering, Trading and Managing Options Risks, Credit Derivatives Modelling & Pricing, Developing front office Derivatives IT, Algorithmic and Quantitative Trading, Equity Analysis and Corporate Finance.
Risk Latte Company Limited
|
a d v e r t i s e m e n t
