You probably knew that Microsoft’s Excel spreadsheet is a great tool for tracking your investments in an organized way, allowing you to view and sort positions including entry price, periodic close prices and returns. In fact, Excel can serve much more than just a glorified financial statement. It can automatically calculate parameters such as an asset or portfolio’s standard deviation, percentage return, and total profit and loss.
Let’s see how Excel can be used to improve investing activities.
Important points to remember
- Excel spreadsheets not only track investments, but also calculate performance and levels of volatility.
- Excel can calculate the difference between the current price of a commodity and the entry price.
- Excel can calculate an asset’s percentage return and assess profit and loss.
- Excel’s most useful feature is its ability to calculate standard deviation, a complex risk assessment formula.
Investment tracking with Excel
An Excel spreadsheet can be used in a number of ways to track an investor’s holdings. The first step is to decide what data you want to include. Figure 1 shows an example of a simple Excel spreadsheet that tracks investment data, including date, entry, size (how many shares), closing prices for specific dates, difference between closing price and entry price, percentage return, profit and loss for each period and closing price standard deviation. A separate sheet in an Excel workbook can be used for each action.
Figure 1: Excel spreadsheet with data from a trading instrument (McGraw Hill).
Create difference formulas in Excel
However, some values in the table need to be calculated manually, which is time-consuming. However, you can put a formula in a cell that will do the work for you. For example, to calculate the difference between the current price of a commodity and its entry price, click in the cell where you want the difference to appear.
Then type the equal sign (=) and click in the cell with the current price. Follow with a minus sign and then click in the entry price cell. Then click “Enter” and the difference will be displayed. If you click the lower-right corner of the cell until you see what looks like a darker plus sign (without the small arrows), you can drag the formula to the other appropriate cells to find the difference for each record.
Creating percentage report formulas in Excel
Percentage return is the difference between the current price minus the entry price divided by the entry price: (entry price) ÷ entry. Again, the percent return calculation is done by selecting the cell where you want the value to appear and then typing the equals sign. Next, type an opening bracket and click in the cell that contains the current price, followed by the minus sign, the entry price, and a closing bracket.
Then type a slash (to represent the split) and click in the entry price cell again. Press Enter and the return percentage will be displayed. You may need to highlight the column, right-click and select Format Cells to select Percent on the Numbers tab for these values to appear as percentages. Once you have the formula in a cell, you can click and drag (as above) to copy the formula to the appropriate cells.
Creation of profit and loss calculation formulas in Excel
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. Then type the equals sign and click in the cell containing the difference (see above). Then enter the star symbol
to show the multiplication, and 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, and then select the currency you want the column to display as a dollar amount. You can then select, click and drag the formula to copy it to the other corresponding cells.
Creating standard deviation formulas in Excel
A pillar of modern portfolio theory, the standard deviation of a data set can provide important information about an investment’s risk. The standard deviation is simply the measure of the deviation between returns and their statistical mean; In other words, it allows investors to determine an investment’s risk or volatility above average. The standard deviation of returns is a more accurate measure than looking at periodic returns because it takes all values into account.
The lower the standard deviation value of an asset or portfolio, the lower its risk.
Calculating the standard deviation is a complex and time-consuming mathematical equation. Fortunately, a few simple clicks in Excel accomplish the same calculation. Even if an investor doesn’t understand the math behind value, they can measure the risk and volatility of a specific stock or the entire portfolio. To find the standard deviation of a data set, click in the cell where you want the standard deviation value to appear. Then, under the “Formulas in Excel” heading, select “Insert Function” (it looks like “fx
“). The Insert Function dialog appears and under Select Category select Statistics. Scroll down and select “STDEV” and then click “OK”. Next, select the cells for which you want to find the standard deviation (in this case, the cells in the Percentage Yield column; make sure you only select the return values and not headings). Then click OK and the standard deviation calculation will appear in the cell.
Visualization of a portfolio in Excel
You can compile data from different spreadsheets in Excel to get an overview of all funds at a glance. If you have data on an Excel sheet that you want to display on another sheet, you can select, copy, and paste the data to a new location. This makes it easy to import a range of inventory data into one sheet. All the formulas are the same as in the previous examples, and the standard deviation calculation is based on the percentage return of all stocks rather than a single instrument.
Figure 2 shows data for 11 different stocks, including entry date and price, number of shares, current price, difference between current price and entry price, percentage return, profit and loss, and overall standard deviation.
Figure 2: Excel spreadsheet showing compilation of data from multiple stock symbols.
More tips on using Excel
Once a spreadsheet has been formatted with the data you want and the formulas you need, entering and comparing data is relatively easy. However, it’s worth taking the time to set up the sheets exactly how you want them and remove or hide any superfluous data. To hide a column or row of data, highlight the selection and choose Format from the Home tab in Excel. A drop-down menu will appear; Select “Hide/Show” and choose the option you want. Masked data remains accessible for calculations but does not appear in the table. This is useful for creating a simplified and easy-to-read table.
Of course, there are alternatives to setting up the table yourself. There is a significant number of commercial products to choose from for portfolio management software that works with Excel. Internet research can help interested investors find out about these opportunities.
The essential
An Excel spreadsheet can be as simple or as complex as you like. Personal preferences and needs determine the complexity of the table. The key is to understand the data you want to include so you can derive insights from it.