Investment Analysis in Microsoft Excel: Risk/Return Calculations and Portfolio Management

Thursday, June 13th

09.00 - 09.15 Welcome and Introduction

09.15 - 12.30


  • The origins of Microsoft Excel (help to understand some of its current features)
  • Application versions
  • Specifications and limitations
  • Basic configuration after installation

Overview of Analytical Functions and Tools

  • Cell formulas
  • Array formulas
  • Solver for numerical search problems
  • Pivot Table for data management
  • Regression analysis
  • Conditional formatting
  • Important charts
  • Recording macros, introduction to Visual Basic for Applications

12.30 - 13.30 Lunch

13.30 - 17.30

Return Calculations

  • Discrete and continuous compounded returns
  • Chain-Linking and annualizing returns
  • Money-weighted return calculations
  • Benchmark-relative statistics: alpha, beta and gamma

Risk Calculations

  • Ex post volatility, annualized volatility
  • Covariance matrix
  • 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

Friday, June 14th

09.00 - 12.30

Simulation Methods

  • Normally distributed returns
  • Normally distributed correlated returns
  • Non-normal returns
  • Random portfolios

Portfolio Construction

  • 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

  • Resampling
  • Historical Scenarios
  • 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

Review and Conclusions

  Site Map    Privacy Policy and Cookies    Mobile Version    Follow us on LinkedIn