Investment Portfolio Analysis with Excel

Learn investment portfolio analysis main concepts from basic to expert level through a practical course with Excel.
3.8 (5 ratings)
Instead of using a simple lifetime average, Udemy calculates a
course's star rating by considering a number of different factors
such as the number of ratings, the age of ratings, and the
likelihood of fraudulent ratings.
90 students enrolled
Instructed by Diego Fernandez Business / Finance
$19
$40
52% off
Take This Course
  • Lectures 40
  • Length 9.5 hours
  • Skill Level All Levels
  • Languages English
  • Includes Lifetime access
    30 day money back guarantee!
    Available on iOS and Android
    Certificate of Completion
Wishlisted Wishlist

How taking a course works

Discover

Find online courses made by experts from around the world.

Learn

Take your courses with you and learn anywhere, anytime.

Master

Learn and practice real-world skills and achieve your goals.

About This Course

Published 11/2015 English

Course Description

Course video lectures, content slides and Excel file constantly updated (latest: November 2016, audio re-editing)

Learn investment portfolio analysis through a practical course with Excel using index replicating 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 take decisions as DIY 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

  • Download index replicating funds data to perform investment portfolio analysis operations directly into worksheet using Excel Add-In.
  • Compare main asset classes’ returns and risks tradeoffs.
  • Estimate portfolio expected returns, historical and market participants’ implied volatility.  
  • Approximate portfolio excess returns based on market, technical, fundamental and macroeconomic factors.
  • Evaluate returns and risks relationship between portfolio and overall market.
  • Hedge portfolio market risk with stock option strategies and measure Hedge Fund index performance.
  • Calculate maximum historical portfolio leverage and assess returns and risks amplification.
  • Build global portfolios following asset allocation strategies from well-known investment managers and compare them using risk adjusted metrics.
  • Diversify specific risks through global portfolios’ asset allocation strategies through mean and variance optimization.
  • Test market efficiency and measure investment costs impact to portfolio returns.

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 finance or business research. And it is necessary for DIY investors’ portfolio management.

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 39 lectures and 9 hours of content. It’s designed for all investment portfolio analysis knowledge levels and a basic understanding of Excel is recommended.

At first, you’ll learn how to download index replicating funds data to perform investment portfolio analysis operations directly into worksheet using Excel Add-In. Then, you’ll compare main asset classes’ returns and risks tradeoffs for cash, bonds, stocks, commodities, real estate and currencies. 

After that, you’ll estimate portfolio expected returns, historical and market participants’ implied volatility.  Then, you’ll approximate portfolio excess returns using capital asset pricing model (CAPM), Fama-French-Carhart factors model and arbitrage pricing theory model (APT).

Next, you’ll hedge portfolios’ market risks with index replicating funds using financial options and measure Hedge Funds index performance. After that, you’ll calculate maximum historical portfolio leverage and assess returns and risks amplification.

Later, you’ll build global portfolios following asset allocation strategies from well-known investment managers and compare them using risk adjusted metrics such as Jensen’s Alpha, Sharpe, Treynor, Sortino and Kelly ratios. Next, you’ll diversify specific risks through portfolios’ asset allocation strategies through mean and variance Markowitz optimization.

Finally, you’ll test market efficiency in its weak and semi-strong forms and asses investment costs impact on portfolio performance.

What are the requirements?

  • Spreadsheet software such as Microsoft Excel is required.
  • Practical example spreadsheet provided by instructor.
  • Prior basic spreadsheet software knowledge is recommended.

What am I going to get from this course?

  • Download index replicating funds data to perform investment portfolio analysis operations directly into worksheet using Excel Add-In.
  • Compare main asset classes’ 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 excess returns using capital asset pricing model (CAPM), Fama-French-Carhart factors model and arbitrage pricing theory model (APT).
  • Hedge portfolios’ market risks with index replicating funds using financial options and measure Hedge Funds index performance.
  • Calculate maximum historical portfolio leverage and assess returns and risks amplification.
  • Build global portfolios following asset allocation strategies from well-known investment managers and compare them using risk adjusted metrics such as Jensen’s Alpha, Sharpe, Treynor, Sortino and Kelly ratios.
  • Diversify specific risks through global portfolios’ asset allocation strategies through mean and variance Markowitz optimization.
  • Test market efficiency in its weak and semi-strong forms and asses investment costs impact on portfolio performance.

What is the target audience?

  • Students at any knowledge level who want to learn about investment portfolio analysis using Microsoft Excel.
  • Finance professionals or academic researchers who wish to deepen their knowledge in quantitative finance.
  • DIY investors also at any knowledge level who desire to learn about investment portfolio analysis and put it in practice.
  • This course is NOT about “get rich quick” investment strategies or magic formulas.

What you get with this course?

Not for you? No problem.
30 day money back guarantee.

Forever yours.
Lifetime access.

Learn on the go.
Desktop, iOS and Android.

Get rewarded.
Certificate of completion.

Curriculum

Section 1: Course Overview
03:42

In this lecture you will view course disclaimer and learn which are its objectives, how you will benefit from it, its previous requirements and my profile as instructor.

02:55

In this lecture you will learn that it is recommended to view course in an ascendant manner as each section builds on last one and also does its complexity. You will also study course structure and main sections (course overview, asset classes, returns and risks, portfolio theory and market efficiency).

03:37

In this lecture you will learn investment portfolio analysis definition and calculations with Microsoft Excel.

14:39

In this lecture you will learn investment portfolio analysis data sources, direct downloading to worksheet with older version of Quandl Excel Add-In, data table vertical look up definition and calculation (VLOOKUP() function). 

17:48

In this lecture you will learn investment portfolio analysis data sources, direct downloading to worksheet with newer version of Quandl Excel Add-In, data table vertical look up definition and calculation (VLOOKUP() function). 

Article

Before starting course please download .XLS Investment Portfolio Analysis with Excel file as additional resources. Investment Portfolio Analysis with Excel Old file only left for previous course version reference. 

7 pages

In this lecture you can download .PDF section slides file.

Section 2: Asset Classes
30 pages

In this lecture you can download .PDF section slides file.

15:51

In this lecture you will learn section lectures’ details and main themes to be covered related to asset classes (cash and cash equivalents, fixed-income or bonds, equities or stocks, commodities, real estate and currencies or foreign exchange).

14:20

In this lecture you will learn cash and cash equivalents definition, returns and risks tradeoff and main calculations (VLOOKUP(), PRODUCT(), COUNT() STDEV(), SQRT(), ARRAY {}  functions).

16:10

In this lecture you will learn fixed-income or bonds definition, returns and risks tradeoff and main calculations (VLOOKUP(), PRODUCT(), COUNT() STDEV(), SQRT(), ARRAY {}  functions). Asset class will first be segmented between U.S. and international indexes, then between U.S. short and long term ones.

16:55

In this lecture you will learn equities or stocks definition, returns and risks tradeoff and main calculations (VLOOKUP(), PRODUCT(), COUNT() STDEV(), SQRT(), ARRAY {}  functions). Asset class will be segmented between U.S. and international indexes, then between U.S. size and investment style indexes and finally between international developed and emerging indexes.

13:51

In this lecture you will learn commodities definition, returns and risks tradeoff and main calculations (VLOOKUP(), PRODUCT(), COUNT() STDEV(), SQRT(), ARRAY {}  functions). Oil and gold prices indexes will be used for reference.

13:21

In this lecture you will learn real estate definition, returns and risks tradeoff and main calculations (VLOOKUP(), PRODUCT(), COUNT() STDEV(), SQRT(), ARRAY {}  functions). 

16:37

In this lecture you will learn currencies or foreign exchange definition, returns and risks tradeoff and main calculations (VLOOKUP(), PRODUCT(), COUNT() STDEV(), SQRT(), ARRAY {}  functions). U.S. Dollar trade weighted indexes will be used for reference.

Section 3: Returns and Risks
27 pages

In this lecture you can download .PDF section slides file.

16:54

In this lecture you will learn section lectures’ details and main themes to be covered related to returns and risks: expected returns (arithmetic mean, median, compounded annual growth rate), risks (uncertainty, systematic risk, unsystematic risk, standard deviation, mean absolute deviation, market implied volatility, normalized rate of return, geometric expected rate of return), risks normality (skewness, kurtosis, Jarque-Bera normality test, Value at Risk), returns and risks relationship (covariance, correlation, coefficient of determination), assets and market relationship (capital asset pricing model CAPM, beta coefficient, Jensen’s alpha, residual variance, Fama-French-Carhart factors model and arbitrage pricing theory model APT expected returns), systematic risk hedge (tail hedge and Hedge Fund Index), portfolio leverage (maximum historical, returns and risks amplification).

10:08

In this lecture you will learn expected returns definition and main calculations (AVERAGE(), MEDIAN(), EXP(), LN() functions).

15:46

In this lecture you will learn risks definition and main calculations (STDEV(), AVEDEV() functions).

08:36

In this lecture you will learn risks normality definition and main calculations (SKEW(), KURT(), COUNT(), NORM.INV() functions).

07:34

In this lecture you will learn how returns and risks are related between assets together with main calculations (CORREL(), COVARIANCE.S() functions).

16:41

In this lecture you will learn how returns and risks are related between assets and the market together with main calculations (AVERAGE(), COVARIANCE.S(), VAR.S(), PRODUCT(), COUNT(), SQRT(), ARRAY {}  functions).

19:16

In this lecture you will learn how returns and risks are related between assets, market, technical and fundamental factors together with main calculations (INDEX(), LINEST(), PRODUCT(), COUNT(), SQRT(), ARRAY {}  functions).

17:50

In this lecture you will learn how returns and risks are related between assets, market and macroeconomic factors together with main calculations (INDEX(), LINEST(), PRODUCT(), COUNT(), SQRT(), ARRAY {}  functions).

19:59

In this lecture you will learn portfolio hedge and hedge funds index definition and main calculations (PRODUCT(), COUNT() STDEV(), SQRT(), ARRAY {}  functions).

15:06

In this lecture you will learn portfolio leverage definition and main calculations (PRODUCT(), COUNT() STDEV(), SQRT(), ABS(), MIN(), MAX(), ARRAY {} functions).

Section 4: Portfolio Theory
16 pages

In this lecture you can download .PDF section slides file.

16:39

In this lecture you will learn section lectures’ details and main themes to be covered related to portfolio theory: portfolio performance (Sharpe, Treynor, Sortino and Kelly ratios), portfolio benchmarks (naïve,  Roche’s global financial assets allocation, Bogle’s 60% stocks 40% bonds, Ferri’s core four and Bernstein’s no-brainer) and asset allocation optimization (mean maximization, standard deviation minimization, Markowitz and Mean-VaR optimizations).

13:06

In this lecture you will learn portfolio performance definition and main metrics’ calculation (AVERAGE(), SQRT(), VAR.S(), IF(), COUNT() functions).

16:59

In this lecture you will learn naïve portfolio benchmark definition and well-known investment managers asset allocation strategies’ calculation (PRODUCT(), COUNT(), STDEV(), SQRT(), ARRAY {} functions).

18:10

In this lecture you will learn portfolio mean maximization asset allocation definition and main calculations (PRODUCT(), COUNT(), STDEV(), SQRT(), AVERAGE(), NORM.INV(). VAR.S(), COVARIANCE.S(), ARRAY {} functions and SOLVER Excel Add-In Optimization).

16:07

In this lecture you will learn portfolio standard deviation minimization asset allocation definition and main calculations (PRODUCT(), COUNT(), STDEV(), SQRT(), AVERAGE(), NORM.INV(). VAR.S(), COVARIANCE.S(), ARRAY {} functions and SOLVER Excel Add-In Optimization).

16:11

In this lecture you will learn portfolio Markowitz optimization asset allocation definition and main calculations (PRODUCT(), COUNT(), STDEV(), SQRT(), AVERAGE(), NORM.INV(). VAR.S(), COVARIANCE.S(), ARRAY {} functions and SOLVER Excel Add-In Optimization).

17:15

In this lecture you will learn portfolio mean-VaR optimization asset allocation definition and main calculations (PRODUCT(), COUNT(), STDEV(), SQRT(), AVERAGE(), NORM.INV(). VAR.S(), COVARIANCE.S(), ARRAY {} functions and SOLVER Excel Add-In Optimization).

Section 5: Market Efficiency
7 pages

In this lecture you can download .PDF section slides file.

13:49

In this lecture you will learn section lectures’ details and main themes to be covered related to market efficiency: market efficiency (active investing, passive investing), efficient market hypothesis EMH (weak and semi-strong forms) and investment costs (expense ratio, transaction costs, tax costs, cash drag, soft dollar costs and advisory fees).

14:31

In this lecture you will learn efficient market hypothesis (EMH) in its weak form definition and main tests’ calculation (INDEX(), LINEST(), T.DIST.2T(), ABS(), COUNT(), ARRAY {} functions).

16:06

In this lecture you will learn efficient market hypothesis (EMH) in its semi-strong form definition and main tests’ calculation (INDEX(), LINEST(), T.DIST.2T(), ABS(), COUNT(), ARRAY {} functions).

14:55

In this lecture you will learn investment costs definitions (expense ratio, transaction costs, tax costs, cash drag, soft dollar costs and advisory fees) and how they are related with investment return calculation.

Section 6: Bibliography
4 pages

In this lecture you can download slides with course bibliography.

Students Who Viewed This Course Also Viewed

  • Loading
  • Loading
  • Loading

Instructor Biography

Diego Fernandez is author of high-quality online courses and ebooks at Exfinsis for anyone who wants to become an expert in financial data analysis.

His main areas of expertise are finance and data analysis. Within finance he has focused on stock fundamental, technical and investment portfolio analysis. Within data analysis he has concentrated on applied statistics, probability, optimization methods, forecasting models and machine learning. For all of this he has become proficient in Microsoft Excel®, R statistical software and Python programming language analysis tools. 

He has important online business development experience at fast-growing startups and blue-chip companies in several European countries. He has always exceeded expected professional objectives by starting with a comprehensive analysis of business environment and then efficiently executing formulated strategy.

He also achieved outstanding performance in his undergraduate and postgraduate degrees at world-class academic institutions. This outperformance allowed him to become teacher assistant for specialized subjects and constant student leader within study groups. 

His motivation is a lifelong passion for financial data analysis which he intends to transmit in all of the courses.

Ready to start learning?
Take This Course