Home / Start Ups / Investors / Improve Your Investing with Excel

Improve Your Investing with Excel

Microsoft’s Excel spreadsheet program allows investors to keep track of investment activity in an organized manner. Using Excel, you can track positions, including entry price, periodic closing prices and returns. Another valuable feature of Excel is that it can automatically calculate an asset’s or a portfolio’s standard deviation.

The standard deviation value is synonymous with risk in regard to modern portfolio theory and can assist investors by evaluating 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

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.

Track Investments with Excel

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

Creating Formulas


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. (For more, see “The Uses and Limits of 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.

Portfolio Standard Deviation

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 and 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.

Other Tips

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.

The Bottom Line

Source link

About Amy Harvey

Amy R. Harvey writes forStartUps Sections In AmericaRichest.

Check Also

How Much Money Do You Need to Retire in Greece?

If you’ve ever vacationed in the Greek islands, you may have found it hard to …

Leave a Reply

Your email address will not be published. Required fields are marked *