Excel for Investment Professionals

Course details

Do you manage a portfolio of stocks or securities? Microsoft Excel offers professionals in the investment industry with a bevy of tools that they can use to make sound investments. In this course, learn how to leverage these tools and perform key investment-related activities in Excel, including investment evaluation and analysis on a stock or portfolio. Professor Michael McDonald provides an overview of the major tasks in the investment industry, and then digs into how to do single stock investment analysis. He also goes over how to compute expected returns on a stock and a portfolio, value a bond in Excel, compute performance evaluation metrics, and more.

Learning objectives
Determine the variable required to compute the P/E ratio of a stock.
Identify two factors that drive expected returns on a stock.
Apply the appropriate formula to determine portfolio returns.
Recall the type of mean that should be used to determine future returns based on buying an investment and holding it for an extended period of time.
Define the formula for information ratios.
Recognize the measure that describes worst case expected loss under normal circumstances.

Concepts

0. Introduction

01 - Using Excel to make sound investments
02 - What you should know

1. Major Tasks in the Investments Industry

03 - Reviewing basic principles
04 - Reviewing asset allocation
05 - Reviewing beta
06 - Reviewing multiples and stock valuation
07 - Reviewing smart beta

2. Single Stock Investment Analysis

08 - Setting up time series data on a stock
09 - Computing holding period returns
10 - Computing time series momentum - Market timing
11 - Compute rolling P E and P B multiples for a stock
12 - Building a discounted cash flow model
13 - Building a dividend discount model

3. Investment Evaluation in Excel

14 - Computing expected returns on a stock
15 - Using probability to calculate stock returns
16 - Computing arithmetic and geometric returns in a portfolio
17 - Computing standard deviation and variance of an asset
18 - Finding covariances and correlations
19 - Computing standard deviation and variance of a portfolio
20 - Computing beta of an asset
21 - Computing risk for a portfolio with many stocks

4. Investment Evaluation in Excel

22 - Computing asset allocation
23 - Computing cross-sectional momentum
24 - Computing correlations between stocks
25 - Evaluating hedge funds and mutual funds with portfolio attribution
26 - Valuing a bond in Excel
27 - Performing scenario analysis

5. Portfolio Performance Evaluation

28 - Setting up allocations
29 - Scenario analysis in a portfolio
30 - Computing expected risk on a portfolio
31 - Computing portfolio Sharpe ratios
32 - Computing information ratios
33 - Computing Sortino ratios
34 - Calculating Treynor measures
35 - Calculating VaR

Conclusion

36 - Next steps