Order Flow Trading

Get the Book | Contents | Reviews | Articles | Contact | Login

How to Create a Portfolio in Excel

Updated: 2017-03-22 11:55:23

Investors, whether they are individuals or institutions, often choose to hold portfolios, which is a fancy name that means a collection of various assets.

Much of the innovation in investment research over the past 60 years has been in the development of a theory of portfolio management. The question it attempts to answer is, "What rate of return will you demand in order to hold a risky security in your portfolio?" To answer this question, we first must consider what type of investor you are, how to define return, and how to define risk.

Now you might be wondering; why should we be interested in portfolio investing at all? Why bother, if we can just trade pens, forex, futures, stocks and houses as usual?

The answer is diversification. By not having all your eggs in one basket you can try to generate income in another manner.

Also, with portfolios, the timing component is not as influential as it is with trading; which can give you a break from a lot of the stress that comes with high frequency activity.

Who is Harry Markowitz and what is Modern Portfolio Theory?

Harry Markowitz is an American economist - recipient of the 1989 John von Neumann Theory Prize and the 1990 Nobel Memorial Prize in Economic Sciences - and professor of finance at the Rady School of Management at the University of California, San Diego (UCSD). He is best known for his pioneering work in modern portfolio theory, studying the effects of asset risk, return, correlation, and diversification on probable investment portfolio returns.

Harry Markowitz put forward this model in 1952. It assists in the selection of the most efficient portfolio by analyzing various possible portfolios of the given securities. By choosing securities that do not "move" exactly together, the model shows investors how to reduce their risk. The Markowitz model is also called Mean-Variance Model due to the fact that it is based on expected returns (mean) and the standard deviation (variance) of the various portfolios.

Harry Markowitz made the following assumptions while developing the model:

  1. Risk of a portfolio is based on the variability of returns from the said portfolio.
  2. An investor is risk averse.
  3. An investor prefers to increase consumption.
  4. The investor's utility function is concave and increasing, due to his risk aversion and consumption preference.
  5. Analysis is based on a single period model of investment.
  6. An investor either maximizes his portfolio return for a given level of risk or maximizes his return for the minimum risk.
  7. An investor is rational in nature.

Number 7 makes me chuckle, but we can put that to the side for now...

The model outlined is effective at helping investors choose the best portfolio from a number of possible portfolios, each with different return and risk. This task requires two separate decisions: determination of a set of efficient portfolios and selection of the best portfolio out of the efficient set. A portfolio that gives maximum return for a given risk, or minimum risk for given return is an efficient portfolio.

Thus, portfolios are selected as follows:

(a) From the portfolios that have the same return, the investor will prefer the portfolio with lower risk, and

(b) From the portfolios that have the same risk level, an investor will prefer the portfolio with higher rate of return.

The fundamental concept behind Modern Portfolio Theory is that the assets in an investment portfolio should be selected considering each asset's correlation with the other assets in the portfolio.

Investing is a trade-off between risk and expected return, with the effort to minimize the former and maximize the latter. In general, assets with higher expected returns are riskier. For a given amount of risk, MPT describes how to select a portfolio with the highest possible expected return. Or, for a given expected return, MPT explains how to select a portfolio with the lowest possible risk.

Here is the typical risk-return profiles for various asset classes:


Modern Portfolio Theory is a form of diversification

What we must always keep in mind is that the mathematics behind portfolio construction are based on assumptions. Given the nature of assumptions, some of these are very strong and end up not holding up well in the real world. Markowitz himself said:

The investment process is made up of two steps. The first step is to have an expectancy of the future returns of certain assets that you would like to insert into the portfolio. This step requires experience and keen insight. The second step is combining the assets in an efficient manner.

So Markowitz himself admitted that mathematics and mean-variance optimization are not sufficient for generating profitable portfolios. The investor needs to have some way of making an educated guess of the future course of prices, which we will talk about in detail in future lessons. Right now we need to understand how to approach the portfolio building process.

Investment Objectives 101

Traders who employ modern portfolio theory can generate easy, consistent returns, but there is no return without risk. In every trade you ultimately need to decide how much loss you are personally willing to take in order to achieve a given return.

Generally speaking, there are four different objectives one seeks when assembling a portfolio:

Income: An investment objective seeking a portfolio that produces current income will focus on cash-flow producing assets that deliver spendable money at the end of each month/quarter/year,

Growth: Growth investments typically generate little or no current income, but have the potential for capital appreciation and may perform differently from the market as a whole or similar investments.

Growth and Income: A blended objective seeking both higher returns from capital appreciation via growth equities and current income from cash-producing investments of all grades.

Speculation with active trading: An investment objective for a trader seeking higher possible capital appreciation while recognizing and accepting a high degree of risk associated with such investments and strategies, including the total loss of principal.

Asset Allocation 101

Even if you are new to investing, you may already know some of the most fundamental principles. How did you learn them? Through real-life experiences and common sense!


Asset allocation involves investing in multiple assets at once, such as stocks, bonds, cash, and funds. The process of determining which mix of assets to hold in your portfolio is a very personal one and depends on your objectives, your time horizon, and your risk tolerance.

We have already covered your objectives, so let us explore the concept of time horizon and risk tolerance:

Time Horizon: Your time horizon is the expected number of months or years you will be investing to achieve a particular financial goal. An investor with a longer time horizon like a retirement 25-40 years away may feel more comfortable taking on a riskier, or more volatile investments because he or she can wait out slow economic cycles and the inevitable downs of markets. By contrast, an investor saving up for a child's education would likely take on less risk because he or she has a shorter time horizon.

Risk Tolerance: Risk tolerance is your ability and willingness to lose some or all of your original investment in exchange for greater potential returns. An aggressive investor, or one with a high-risk tolerance, is more likely to risk losing money in order to get better results. A conservative investor, or one with a low-risk tolerance, tends to favor investments that will preserve his or her original investment, even if that means accepting a smaller return.

When it comes to investing, risk and reward are inextricably entwined. All investments involve some degree of risk. The reward for taking on risk is the potential (but not the certainty) for a greater investment return.

Asset Classes

For many financial goals, investing in a mix of stocks, bonds, and cash can be a good strategy.

Stocks have historically had the greatest risk and highest returns among the three major asset categories. As an asset category, stocks are a heavy hitter in many portfolios because they offer the greatest potential for growth. In other words stocks hit home runs, but also strike out. This volatility of stocks makes them a risky investment in the short term, but investors that have been willing to ride out the volatile returns of stocks over long periods generally have been rewarded with strong positive returns.

Bonds are generally less volatile than stocks but offer more modest returns. As a result, an investor approaching a financial goal might increase his or her bond holdings relative to his or her stock holdings because the reduced risk of holding more bonds would be attractive to the investor despite their lower potential for growth. You should keep in mind that certain categories of bonds offer high returns similar to stocks, but these bonds, known as high-yield or junk bonds, also carry higher risk.


Remember this relationship between stock and bond prices!

Cash and cash equivalents like certificates of deposit, treasury bills and other money market instruments are the safest investments, but offer the lowest return of the three major asset categories. The chances of losing money on an investment in this asset category are generally extremely low. Governments guarantee many investments in cash equivalents, but investment losses in non-guaranteed cash equivalents do occur. The principal concern for investors investing in cash equivalents is inflation risk. This is the risk that inflation will out pace and erode investment returns over time.

By including asset categories with investment returns that move up and down under different market conditions within a portfolio, an investor can protect against significant losses. Historically, the returns of the three major asset categories have not moved up and down at the same time, which is part of the magic of diversification. By picking the right group of investments, you may be able to limit your losses and reduce the fluctuations of investment returns without sacrificing too much potential gain.

In other words, each of these asset classes has its own benefits, risks, and by diversifying the composition of the portfolio you hold, you can exploit a concept known as the efficient frontier.

Efficient Frontier

It should be clear that all the videos referenced in this program are high quality, but I'd like to point out how extraspecially well done this one is.

It also has the benefit of being short. :)

Link to Video

Knowing what the efficient frontier is will help, but to really get the value from this lesson you need to learn how to calculate the efficient frontier on your own.

I suggest the following two videos:

Link to Video 1

Link to Video 2

You'll notice that the level of assumed sophistication just jumped up a few notches and this is by design. Markets are bifurcated into two distinct groups. On one side you have the retail consumers of products. On the other you have the professionals. There IS a gap between the two and crossing that gap will require a meaningful investment in yourself.

Want better trading results?

Get: Order Flow Trading for Fun and Profit