Investment Portfolio Analysis with Excel

Full Course Content Last Update 12/2018

Learn investment portfolio analysis through a practical course with Microsoft Excel® using index replicating ETFs and Mutual Funds historical data for back-testing. It explores main concepts from basic to expert level which can help you achieve better grades, develop your academic career, apply your knowledge at work or do your research as experienced investor. All of this while exploring the wisdom of Nobel Prize winners and best practitioners in the field.

Become an Investment Portfolio Analysis Expert in this Practical Course with Excel

• Compare main asset classes benchmark indexes replicating funds returns and risks tradeoffs for cash, bonds, stocks, commodities, real estate and currencies.

• Estimate portfolio expected returns, historical and market participants implied volatility. 

• Approximate portfolio expected excess returns using capital asset pricing model (CAPM), Fama-French-Carhart factors model and arbitrage pricing theory model (APT).

• Hedge portfolio systematic risk through options trading strategies benchmark indexes replicating funds.

• Evaluate hedge fund index performance and assess portfolio returns and risks amplification through leverage.

• Calculate portfolio performance metrics such as Sharpe, Treynor, Sortino, and Kelly ratios.

• Estimate benchmark global portfolios returns from periodically rebalanced equal weighted asset allocations and those from well-known investment managers.

• Optimize global portfolios asset allocation weights for mean maximization, standard deviation minimization, mean maximization and standard deviation minimization, mean maximization and value at risk minimization objectives within training range based on Markowitz portfolio theory.

• Approximate global portfolios returns from periodically rebalanced optimized asset allocations within testing range and compare them with equal weighted and well-known investment managers benchmark portfolios.

• Evaluate global portfolios performance through global risk factors model and estimate their expected return, expected excess return and expected return contribution from global risk factors exposure while assessing investment costs impact on portfolio performance.

Become an Investment Portfolio Analysis Expert and Put Your Knowledge in Practice

Learning investment portfolio analysis is indispensable for finance careers in areas such as asset management, private wealth management, and risk management within institutional investors represented by banks, insurance companies, pension funds, hedge funds, investment advisors, endowments and mutual funds. It is also essential for academic careers in quantitative finance. And it is necessary for experienced investors optimized asset allocation strategies research and development.

But as learning curve can become steep as complexity grows, this course helps by leading you step by step using index replicating funds historical data for back-testing and to achieve greater effectiveness.

Content and Overview

This practical course contains 43 lectures and 8.5 hours of content. It’s designed for all investment portfolio analysis knowledge levels and a basic understanding of Microsoft Excel® is useful but not required.

At first, you’ll learn how to perform investment portfolio analysis operations using built-in functions and array calculations. Next, you’ll learn how to do portfolio optimization parameters estimation and linear regression calculation using Microsoft Excel® Add-ins.

Then, you’ll define main asset classes by comparing their benchmark indexes replicating funds returns and risks tradeoffs. After that, you’ll segment main asset classes into traditional and alternative ones. For traditional asset classes, you’ll define cash and cash equivalents, fixed income or bonds and equities or stocks. Regarding cash and cash equivalents traditional asset class, you’ll use U.S. total money market benchmark index replicating fund. Regarding cash and cash equivalents traditional asset class, you’ll use U.S. total money market benchmark index replicating fund is used. Regarding fixed income or bonds traditional asset class, U.S. total bond market, U.S. short term bond market, U.S. long term bond market and international total bond market benchmark indexes replicating funds. Regarding equities or stocks traditional asset class, you’ll use U.S. total stock market, U.S. large cap stock market, U.S. small cap stock market, U.S. small cap growth stock market, U.S. small cap value stock market, international total stock market, international developed stock market and international emerging stock market benchmark indexes replicating funds. For alternative asset classes, you’ll define commodities, real estate and currencies or foreign exchange. Regarding commodities alternative asset class, you’ll use oil and gold prices benchmark indexes replicating funds. Regarding real estate alternative asset class, you’ll use U.S. real estate investment trust market benchmark index replicating fund. Regarding currencies or foreign exchange alternative asset class, you’ll use U.S. dollar major currencies benchmark index replicating fund.

Next, you’ll define returns and risks using U.S. large cap stocks market benchmark index replicating fund. After that, you’ll calculate expected returns through historical returns mean and median. Then, you’ll estimate risks through historical returns standard deviation, mean absolute deviation and market participants implied volatility. Later, you’ll approximate portfolio expected excess returns through capital asset pricing model (CAPM), Fama-French-Carhart factors model and arbitrage pricing theory model (APT). Next, you’ll hedge portfolio systematic risk through options trading strategies and evaluate hedge fund index performance together with the assessment of returns and risks amplification through portfolio leverage.

After that, you’ll define portfolio optimization through global assets allocation. Next, you’ll calculate Sharpe ratio, Treynor ratio, Sortino ratio and Kelly ratio portfolio performance metrics. Then, you’ll estimate benchmark global portfolios returns from periodically rebalanced equal weighted asset allocations and those from well-known investment managers. Later, you’ll optimize global asset allocation weights within training range for mean maximization, standard deviation minimization, mean maximization and standard deviation minimization, mean maximization and value at risk minimization objectives based on Markowitz portfolio theory. After that, you’ll calculate global portfolio returns within testing range using previously optimized periodically rebalance asset allocation weights and compare them with equal weighted and well-known investment managers benchmark portfolios.

Later, you’ll evaluate optimized portfolios performance through global risk factors model. After that, you’ll estimate optimized portfolios expected returns, expected excess returns and global risk factors exposure returns contribution. Finally, you’ll assess investment costs impact on portfolio performance.