Microsoft's Excel spreadsheet program allows investors to keep track of investment activity in an organized manner. Using Excel, investors can track positions, including entry price, periodic closing prices and returns. Another valuable feature of Excel is that it can automatically calculate an investment's or portfolio's standard deviation. The standard deviation value is synonymous with risk in regard to modern portfolio theory, and can assist investors by evaluating an investment's or portfolio's volatility. This article will briefly explain modern portfolio theory, standard deviation and how Excel can be used to enhance one's investment activities.

Modern Portfolio Theory
Modern portfolio theory (MPT) was developed by Harry Markowitz, and introduced in the 1952 Journal of Finance. The central focus of MPT is that investors should not limit themselves to the expected risk and return of one particular stock; rather, an investor should reduce risks through diversification. MPT states that the risk in a portfolio of diverse stocks (or other investment instruments) will be less than the risk of any one of the stocks. A key point here is diversification: it is not adequate to simply own 20 different stocks. Instead, a diversified portfolio will be comprised of instruments that are relatively unrelated in order to mitigate the risks in the event that one sector or type of investment performs poorly.

According to MPT, risk involves two elements: systematic and unsystematic risk. Systematic risk includes risk factors that cannot be reduced through diversification, the risk that's inherent in the overall market. Unsystematic risk is tied to individual investment products and can be diminished through the use of diversification. A goal of modern portfolio theorists is to limit risk, or the deviation from the mean, by holding a well-diversified portfolio. (To learn more, see Modern Portfolio Theory: Why It's Still Hip.)

Standard Deviation
Calculating the standard deviation for a data set can reveal important information regarding an investment's risk. The standard deviation is simply the measure of how far returns are from their statistical average; in other words, standard deviation allows investors to determine the above-average risk or volatility of an investment. The standard deviation calculation is a complex, time-consuming mathematical equation. Fortunately, a few simple clicks in Excel can provide the same calculation, even if an investor does not understand the math behind the value.

Since the standard deviation measures the degree to which a stock's or portfolio's returns vary, or deviate, from average returns, it can be a useful metric in assessing volatility and risk. The standard deviation of returns is a more accurate measure of volatility than looking at periodic returns because it takes all values into account. The lower the standard deviation value, the lower the risk.

Using Excel to Track Investments
An Excel spreadsheet can be used in a number of ways to keep track of investment activity. The first step is to decide what data you would like to include. Figure 1 shows an example of a simple Excel spreadsheet that tracks one investment's data, including date, entry, size (how many shares), closing prices for the dates specified, the difference between the closing price and the entry price, the percentage return, profit and loss for each periodic closing price, and the standard deviation. A separate sheet in an Excel workbook can be used for each stock.

Figure 1: Excel spreadsheet showing data from one trading
instrument (McGraw Hill).

Creating Formulas
Some values in the spreadsheet, however, must be manually calculated, which is time consuming, or you can insert a formula into a cell to do the work for you. To calculate the difference (the difference of the current price minus the entry price), for instance, click in the cell where you would like the difference to appear. Next, type the equals sign and then click in the cell containing the current price. Follow this with a minus sign and then click in the cell that contains the entry price. Then click enter and the difference will appear. If you click on the lower right corner of the cell until you see what looks like a dark plus sign (without little arrows on it), you can drag the formula to the other appropriate cells to find the difference for each data set.

Percent Return
The percent return is the difference of the current price minus the entry price, divided by the entry price: (price-entry)/entry. The percent return calculation is made by, once again, selecting the cell where you would like the value to appear, then typing the equal sign. Next, type an open parenthesis and click in the cell that has the current price, followed by a minus sign, the entry price and a close parenthesis. Next type a forward slash (to represent division) and then click in the entry price cell again. Press enter and the percent return will appear. You may need to highlight the column, right click, and select Format Cells to select "Percentage" under the number tab to have these values appear as percentages. Once you have the formula in one cell, you can click and drag (as above) to copy the formula into the corresponding cells.

Profit and Loss
The profit and loss is the difference multiplied by the number of shares. To create the formula, click in the cell where you want the value to appear. Next, type the equals sign and then click in the cell that contains the difference (see above). Then, type the asterisk symbol (*; to represent multiplication) and then click in the cell that contains the number of shares. Press enter and you will see the profit and loss for that data. You may need to highlight the column, right click and select Format Cells, then select currency to set the column to display as a dollar amount. You can then select, click and drag the formula to copy it in the other corresponding cells.

Standard Deviation
As we've discussed, standard deviation is a measure of a stock's or portfolio's volatility. Excel can find the standard deviation for a data set, or population, in a few keystrokes. Since the standard deviation calculation is complex, this is an extremely helpful feature in Excel. To find the standard deviation of a data set, click on the cell where you want the standard deviation value to appear. Next, under the Formulas heading in Excel, select the "insert function" option (this looks like "fx"). The Insert Function box will appear, and under "select a category" choose "Statistical". Scroll down and select "STDEV", then click OK. Next, highlight the cells that you want to find the standard deviation for (in this case, the cells in the percent return column, careful to select only the return values and not any headers). Then click OK and the standard deviation calculation will appear in the cell. (For more, see The Uses And Limits Of Volatility.)

Finding the Standard Deviation for a Portfolio
You can compile data from the individual sheets in Excel to determine metrics such as overall profit and loss, and overall standard deviation. Figure 2 shows data from 11 different stocks, including entry date and price, the number of shares, the current price, the difference between the current price and the entry price, the percent return, the profit & loss, and the overall standard deviation. If you have data on one sheet in Excel that you would like to appear on a different sheet, you can select, copy and paste the date into a new location. In this way, it is easy to import a series of stock's data into one sheet. All of the formulas are the same as in the previous examples, and the standard deviation calculation is based on the percent return of all of the stocks, rather than just a single instrument.

Figure 2 Excel spreadsheet showing compilation of multiple
trading symbol\'s data.

Using Excel is a useful tool to assist with investment organization and evaluation. Once a spreadsheet has been formatted with the data that you would like to see, and the necessary formulas, entering and comparing data is relatively simple. It pays to take the time to set up the sheets exactly how you want them and to eliminate or hide any extraneous data. To hide a column or row of data, highlight the selection, and under the Home tab in Excel, select Format. A dropdown menu appears; select "hide/unhide" and choose the option you want. Any data that is hidden can still be accessed for calculations but will not show up in the spreadsheet. This is helpful when creating a streamlined, easy-to-read spreadsheet.

Of course there are alternatives to setting up the spreadsheet by yourself. A considerable number of commercial products are available from which to choose portfolio management software that works in concert with Excel. An internet search can help interested investors learn about these opportunities.

An Excel spreadsheet can be as easy or complex as you want it to be. Personal preference and needs dictate the complexity of the spreadsheet. The key is to understand whatever data you do decide to include so that you are able to gain insight from it.

Please note: the spreadsheet and investment data included in this article are for illustrative purposes only and do not represent any actual positions held by the author. The intention of the article is to demonstrate techniques in Excel, not to make any inferences or recommendations regarding investments. <(For more advanced tips, check out Microsoft Excel Features For The Financially Literate.)

Related Articles
  1. Retirement

    Two Heads Are Better Than One With Your Finances

    We discuss the advantages of seeking professional help when it comes to managing our retirement account.
  2. Professionals

    A Day in the Life of a Hedge Fund Manager

    Learn what a typical early morning to late evening workday for a hedge fund manager consists of and looks like from beginning to end.
  3. Investing Basics

    5 Tips For Diversifying Your Portfolio

    A diversified portfolio will protect you in a tough market. Get some solid tips here!
  4. Entrepreneurship

    Identifying And Managing Business Risks

    There are a lot of risks associated with running a business, but there are an equal number of ways to prepare for and manage them.
  5. Active Trading

    10 Steps To Building A Winning Trading Plan

    It's impossible to avoid disaster without trading rules - make sure you know how to devise them for yourself.
  6. Mutual Funds & ETFs

    Best 3 Vanguard Mutual Funds for Retirement

    Discover the top Vanguard target-date retirement funds with target dates in 2020, 2030 and 2050, and learn about the characteristics of these funds.
  7. Investing

    What’s the Difference Between Duration & Maturity?

    We look at the meaning of two terms that often get confused, duration and maturity, to set the record straight.
  8. Fundamental Analysis

    Top Private Equity Bargains for Your Portfolio

    Investing in private equity firms can lead to long-term profits.
  9. Fundamental Analysis

    Boost Your Portfolio by Adding 3 Turnaround Stocks

    Peter Lynch loves turnarounds. The stocks of these battered companies can offer incredible rewards if bought at the right time.
  10. Markets

    Are EM Stocks Finally Emerging?

    Many investors are looking at emerging market (EM) stocks and wonder if it’s time to step back in, while others wonder if we’ll see further declines.
  1. Does mutual fund manager tenure matter?

    Mutual fund investors have numerous items to consider when selecting a fund, including investment style, sector focus, operating ... Read Full Answer >>
  2. Why do financial advisors dislike target-date funds?

    Financial advisors dislike target-date funds because these funds tend to charge high fees and have limited histories. It ... Read Full Answer >>
  3. What licenses does a hedge fund manager need to have?

    A hedge fund manager does not necessarily need any specific license to operate a fund, but depending on the type of investments ... Read Full Answer >>
  4. Can mutual funds invest in hedge funds?

    Mutual funds are legally allowed to invest in hedge funds. However, hedge funds and mutual funds have striking differences ... Read Full Answer >>
  5. When are mutual funds considered a bad investment?

    Mutual funds are considered a bad investment when investors consider certain negative factors to be important, such as high ... Read Full Answer >>
  6. What fees do financial advisors charge?

    Financial advisors who operate as fee-only planners charge a percentage, usually 1 to 2%, of a client's net assets. For a ... Read Full Answer >>

You May Also Like

Hot Definitions
  1. Black Friday

    1. A day of stock market catastrophe. Originally, September 24, 1869, was deemed Black Friday. The crash was sparked by gold ...
  2. Turkey

    Slang for an investment that yields disappointing results or turns out worse than expected. Failed business deals, securities ...
  3. Barefoot Pilgrim

    A slang term for an unsophisticated investor who loses all of his or her wealth by trading equities in the stock market. ...
  4. Quick Ratio

    The quick ratio is an indicator of a company’s short-term liquidity. The quick ratio measures a company’s ability to meet ...
  5. Black Tuesday

    October 29, 1929, when the DJIA fell 12% - one of the largest one-day drops in stock market history. More than 16 million ...
  6. Black Monday

    October 19, 1987, when the Dow Jones Industrial Average (DJIA) lost almost 22% in a single day. That event marked the beginning ...
Trading Center