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

  Mapa stránek    Ochrana osobních údajů a cookies    Sledujte nás na LinkedIn