Investment Analysis in Microsoft Excel: Risk/Return Calculations and Portfolio Management
Location: Prague, NH Hotel Prague
Lecturer: Andreas Steiner
Attend this intensive 2-day training and learn to...
Unlocking the potential of Microsoft Excel: Get an overview of the powerful built-in functionality without external add-ins and software.
Smart analytics: Learn about smart solutions to standard problems in investment analytics
Spreadsheet templates: build customized solutions based on the many spreadsheet templates distributed to participants in this course.
Spreadsheet risk management: Use Excel in a structured way and minimize operational issues
This course gives an overview of applying Excel in a most efficient manner for typical calculations in investment analysis and portfolio management. Delegates will be introduced to some of the less widely known but highly useful built-in functions. Spreadsheet templates are used in group exercises to solve case study - like problems. The focus of the course is to deliver practical value for working professionals. The calculations discussed in the course are typical examples encountered in performance measurement, risk management and portfolio management.
The training consists of classroom-based teaching combined with group exercises (four per day).
Who should attend?
The course is not only for specialists but for a wider audience including investment managers, asset management executives of all levels, institutional investors and research analysts.
This course has been designed for the benefit of:
Quantitative investment analysts
The course assumes a general familiarity with financial markets, instruments and investment portfolios. A basic understanding of statistical and mathematical concepts is an advantage.
Delegates will receive colour printouts of all slides and electronic access to Excel spreadsheets used during the course.
The workshop will be highly practical and hands-on. Participants are required to bring a notebook with MS Excel. Course files will be distributed during and after the workshop.
09.00 - 09.15 Welcome and Introduction
09.15 - 12.30
The origins of Microsoft Excel (help to understand some of its current features)
Specifications and limitations
Basic configuration after installation
Overview Basic Functions
Solver for numerical search problems
Pivot Table for data management
Recording macros, introduction to Visual Basic for Applications
12.30 - 13.30 Lunch
13.30 - 17.30
Discrete and continuous compounded returns
Chain-Linking and annualizing returns
Money-weighted return calculations
Benchmark-relative statistics: alpha, beta and gamma
Ex post volatility, annualized volatility
Ex ante and ex post contributions to volatility and tracking error
Tail and downside dependency: conditional correlation
Historical VaR and CVaR
Maximum drawdown, underwater chart
Four group exercises will be solved during the first day
09.00 - 12.30
Normally distributed returns
Normally distributed correlated returns
Mean-variance portfolio optimization with realistic constraints
Risk parity portfolio construction
Non-optimized portfolio construction methodologies: deriving allocations from scores
Expected returns using the Black/Litterman methodology
12.30 - 13.30 Lunch
13.30 - 17.30
Advanced Risk Measurement
Confidence intervals for skewness and kurtosis
Factor analysis of a portfolio: contributions of factors to portfolio risk and return
Four group exercises will be solved during the second day