You may know that Microsoft’s Excel spreadsheet program is a great tool to track your investments in an organized way, enabling you to view and sort positions, including entry prices, regular closing prices, and returns. But in fact, Excel can do more than just beautify financial statements. It can automatically calculate indicators such as the standard deviation of the asset or portfolio, the rate of return, and the overall profit and loss.
Let’s see how Excel can enhance your investment activity.
- Excel spreadsheets can not only track investments, but also calculate performance and volatility.
- Excel can calculate the difference between the current price of an asset minus its entry price.
- Excel can calculate the percentage return of assets and evaluate profit and loss.
- A particularly useful feature of Excel is its ability to calculate standard deviation, which is a complex formula for assessing risk.
Use Excel to track investments
Excel spreadsheets can be used to track investors’ holdings in a variety of ways. The first step is to decide what data to include. The figure below shows an example of a simple spreadsheet that tracks the data of an investment, including date, entry, size (how many shares), closing price on a specified date, difference between closing price and entry price, and percentage return , The profit and loss of the closing price of each period, and the standard deviation. A separate worksheet in the Excel workbook can be used for each stock.
Create a difference formula in Excel
However, some values in the spreadsheet must be calculated manually, which is time consuming. However, you can insert formulas in the cells to do this for you. For example, to calculate the difference between the current price of an asset minus its entry price, click on the cell where you want the difference to be displayed.
Next, enter the equal sign (=) and click the cell containing the current price. Add a minus sign after this and click on the cell containing the entry price. Then click Enter and the difference will appear. If you click the lower right corner of the cell until you see something that looks like a black plus sign (no small arrow above), you can drag the formula to other appropriate cells to find the difference for each data set .
Create a percentage return formula in Excel
The percentage return is the difference between the current price minus the entry price, divided by the entry price: (price-entry) ÷ entry. The percentage return calculation is performed by selecting the cell where you want the value to appear again, and then entering the equal sign. Next, type a left parenthesis and click on the cell with the current price, followed by a minus sign, entry price, and a right parenthesis.
Next, type a forward slash (for division), and then click the entry price cell again. Press Enter and the percentage return will appear. You may need to highlight the column, right-click and select “Format Cells” to select “Percents” under the “Number” tab to make these values appear as percentages. When you have a formula in a cell, you can click and drag (as above) to copy the formula to the corresponding cell.
Create profit and loss formulas in Excel
The profit and loss formula is the difference multiplied by the number of shares. To create a formula, click the cell where you want to display the value. Next, enter the equal sign and click on the cell containing the difference (see above).Then, enter the asterisk
Indicates multiplication, and then click the cell containing the number of shares. Press Enter and you will see the profit and loss of the data. You may need to highlight the column, right-click and select Format Cells, then select the currency to set the column to display in dollar amounts. Then, you can select, click and drag the formula to copy it to other corresponding cells.
Create a standard deviation formula in Excel
As the backbone of modern portfolio theory, the standard deviation of a data set can reveal important information about investment risks. The standard deviation simply measures the distance between the return and its statistical average; in other words, it allows investors to determine the above-average risk or volatility of the investment. The standard deviation of returns is more accurate than looking at periodic returns because it takes all values into account.
The lower the standard deviation of an asset or portfolio, the lower its risk.
Standard deviation calculation is a complicated and time-consuming mathematical equation. Fortunately, the same calculation can be provided in a few clicks in Excel. Even if investors do not understand the mathematical principles behind value, they can measure the risk and volatility of a particular stock or the entire portfolio with relative ease.
To find the standard deviation of a data set, click the cell where you want to display the standard deviation value. Next, under the formula heading in Excel, select the Insert function option (this looks like fx). The Insert Function box will appear, and select Statistics under Select Category. Scroll down and select STDEV, then click OK. Next, highlight the cell for which you want to find the standard deviation (in this case, the cell in the percent return column; note that only the return value is selected, not any headers). Then click OK, and the standard deviation calculation will appear in the cell.
View portfolio in Excel
You can compile data from various worksheets in Excel to get a clear overview of all collections. If you want to copy data from one worksheet in Excel to another worksheet, you can select, copy and paste the data to a new location. In this way, it is easy to import a series of stock data into a table. All formulas are the same as the previous example, and the standard deviation calculation is based on the percentage return of all stocks, not just a single instrument.
The following figure shows the data of 11 different stocks, including the date and price of the market, the number of shares, the current price, the difference between the current price and the market price, the rate of return, the profit and loss, and the overall standard deviation.
Other tips for using Excel
When the spreadsheet is formatted with the data you want to view and the necessary formulas, entering and comparing data is relatively simple. But it’s worth the time to set up the worksheet exactly the way you want it and eliminate or hide any extraneous data. To hide a column or row of data, highlight it, and then under the “Home” tab, select “Format.” A drop-down menu will appear; choose to hide or unhide, choose the option you want. Any hidden data can still be accessed for calculations, but will not be displayed in the spreadsheet. This is helpful when creating simplified, easy-to-read spreadsheets.
Of course, there are other options besides setting up the spreadsheet yourself. There are a large number of commercial products for you to choose from, from which you can choose a portfolio management software that works with Excel. Internet searches can help interested investors understand these opportunities.
Excel spreadsheets can be as simple or complex as you want. Personal preferences and needs determine the complexity of the spreadsheet. The key is to understand any data you decide to include so that you can gain insights from it. Those who are interested in learning about other ways to use the software may wish to register for one of the best online Excel courses currently available.