Portfolio Optimization Models in EXCEL - Online Course
About the Course
The course begins by presenting the need and motivation for portfolio management including an assessment of the impact of taxes and fees on retirement savings. The two principal drivers of portfolio management, risk and return, are defined. The market portfolio is explained and its performance is compared to that of a managed portfolio.
Next, a step by step walkthrough for building a portfolio management worksheet in EXCEL is presented. This includes the calculation of security wise & portfolio wise daily return series average returns & standard deviations, betas & alphas, and setting up Solver for optimization of the portfolio allocation so as to maximize return/ minimum risk. Different portfolio strategies are tested and assessed using holding period returns evaluated over two observation periods.
This is followed by calculations of alpha and beta measures which are used to set portfolio strategies and determine optimal portfolio allocation. The stability and robustness of portfolio alphas - the excess return metric - as a tool for determining the optimal portfolio is discussed together with its implication for portfolio allocation and optimization models.
Next, a review of different index matching allocation strategies is carried out.
Finally the course concludes with a discussion of how to find an optimal allocation for the investment portfolio of a life insurance company. It presents an outline of the methodology used and provides one possible solution to the optimization challenge posed.
Learning Objectives
After taking this course you will be able to:
- Explain the need for portfolio management
- Discuss the impact of taxes and fees on retirement savings
- Calculate risk & return for a portfolio
- Calculate the combined risk/ return metric, return per unit of risk for the portfolio
- Define a market portfolio
- Assess the performance of a managed portfolio in relation to the market portfolio
- List, define & compare the three investment styles - value, growth & technical analysis
- Calculate returns and standard deviations for individual securities & portfolio
- Build a portfolio management EXCEL sheet
- Set up Solver for different portfolio optimization objective functions
- Setup & use Solver for different portfolio optimization strategies
- Calculate holding period return, aggregate return and annualized return
- Compare strategies over a given observation period and evaluate the effectiveness of selected strategy's allocation over a separate period
- Calculate Alpha and Beta for the portfolio
- Explain the difference of Alpha and Beta in terms of market dependency
- Utilize this difference to set up revised portfolio optimization strategies
- Discuss skewness preference
- Index match a portfolio
- Discuss portfolio alpha stability and its implication for portfolio allocation and optimization
- Define baseline alpha and alpha deviation
- Evaluate portfolio performance
- Determine an optimal portfolio allocation for a life insurance company's investment portfolio
Course Details | ||||
| Level | Intermediate | |||
| Prerequisites | The candidate should be comfortable with basic mathematics, statistics and EXCEL. Some finance and market knowledge would also be useful. | |||
| Target Audience | Active investors who want to take a deep dive through the investment allocation and optimization process without delving deep into mathematical equations and proofs. Auditors and accountants who need to evaluate portfolio performances against benchmarks and prepare audit disclosures for holding period returns, fund investment income. Consultants and professionals required to comment on adequacy of portfolio return and robustness of allocation models. Intermediate level finance and business school students who require a quick reference and study guide containing basic principles and real world applications of investment management and portfolio optimization. | |||
| Advance Preparation | None | |||
| Minimum Browser Requirements | This site is best viewed in Google Chrome, Firefox or Safari. Please note we do not support Internet Explorer. | |||
| Course Guide | This course consists of five lessons:
| |||
| Course Availability | Online subscription is for a 60 day period only. This content will not be available after the period ends. | |||