Building An Investment Portfolio: Excel Essentials

Building an investment portfolio in Excel is a great way to keep track of your investments and make informed decisions. It allows you to analyse your portfolio and returns and visualise your capital. You can use Excel to calculate metrics such as standard deviation, percentage return, and overall profit and loss. Additionally, Excel can help you calculate the performance and volatility of your investments.

Excel offers tools that automatically calculate stock information, such as price, price change, and market capitalization. You can also add your own formulas to compute the worth of your investments.

There are five steps to creating a stock portfolio in Excel: inserting stocks, converting data, generating relevant information, adding investment information, and adding analysis formulas. This process can be done in Excel or Google Sheets, allowing you to access it from anywhere, including your mobile device.

By building an investment portfolio in Excel, you can efficiently manage your portfolio, track the performance of your assets, and make necessary adjustments. It is a useful tool for investors to enhance their investment activities and make strategic decisions.

Inserting stocks into Excel

To insert stocks into Excel, open a new spreadsheet and list the stocks you own or plan to purchase. You can use either the company's business name, traded name, or ticker symbol.

Next, you'll need to convert this data into a format that Excel recognises as stocks. First, highlight the data and create a table by selecting Insert > Table. Then, go to the Data tab and click Stocks. Excel will now convert your data into a format it recognises as stocks, indicated by a small icon.

You can now add more data. Click on a stock name and you'll see an "Add Column" button with various options for the types of data you can add, such as price, market cap, and percentage change.

If you want to add stock prices, you can either use the STOCKHISTORY function or write formulas that reference stock data types. You can also add real-time stock prices and metrics by using the Data Types feature, which is available to Office 365 users. This feature allows you to pull data related to stocks, bonds, currencies, and cryptocurrencies.

Convert data into stocks

Now that you have inserted the stocks into Excel, you can convert the data into stocks. This will enable you to generate relevant stock information.

Firstly, select the data you are interested in. Then, from the top navigation bar, select 'Data' and click on 'Stocks'. You will see the company stocks converted into their full business name, with their traded name in the stock exchange market denoted next to it.

Next, click on any cell that contains a stock name in your portfolio. You will see an icon, and once you click on it, a drop-down list will appear. From here, you can select the information you need to generate to create your investment portfolio in stocks. This could include the stock price, market cap, and percentage of change.

Note that the information retrieved from Excel about the stocks is updated once daily, after the market closes. Therefore, you should not expect to see hourly updates.

If you are using Microsoft 365 Excel, you can also use the Excel Price Feed Add-in to help you build and maintain an investment portfolio spreadsheet with live financial data. This includes 100+ new Excel formulas for live, historical, and fundamental market data.

Generate relevant information

Once you have entered the stocks you hold or plan to purchase, and converted the data into stocks, you can then generate relevant information.

Click on any cell that contains a stock name in your portfolio. You will find an icon, and once you click on it, a drop-down list will appear. You can then select the information you need to generate to create your investment portfolio. This includes the stock price, market cap, and percentage change. You can also add your own formulas to compute how much you are trading and the worth of your investments.

You can also add your investment information, such as the number of shares purchased and the overall amount spent. This will allow you to calculate the worth of your investment in each corporation.

Additionally, you can add another column to indicate if you are gaining or losing on each stock. You can also add a column to determine your target price, and insert charts and diagrams to understand the distribution of your investment portfolio.

If you are holding a variety of international stocks, you can use the Excel Price Feed Add-in to help build and maintain your investment portfolio with live financial data. This includes current prices and PE ratios.

Add investment information

Now that you have a basic understanding of how to build an investment portfolio in Excel, it's time to dive into the specifics of adding your investment information. This is where you'll input all the relevant data about your investments, such as the number of shares, purchase prices, and current values. Here's a step-by-step guide to help you through the process:

Enter Basic Stock Data:

Start by entering the basic information about each stock in your portfolio. This includes the name or symbol of the stock, the purchase date, the purchase price, and the number of shares you own. This information should be readily available, especially if you use an online broker. If you have different purchase dates for different lots of the same stock, consider listing each lot separately or calculating a weighted-average purchase date and price.

Calculate Purchase Cost:

Calculate the purchase cost for each stock by multiplying the purchase price by the number of shares. In Excel, you can use formulas to automate these calculations. For example, if the purchase price is in cell E6 and the number of shares is in F6, the formula for calculating the purchase cost in cell G6 would be "=E6*F6". You can then copy this formula and paste it into the corresponding cells for each stock.

Input Current Price:

The current price of each stock is crucial for assessing its performance. You can manually enter the current prices, or you can use functions like GOOGLEFINANCE in Google Sheets to automatically update this information. In Google Sheets, the formula "=GOOGLEFINANCE(C6,"price")" in cell H6 will populate the current price for the stock entered in row 6.

Calculate Gross Current Value:

Calculate the gross current value for each stock by multiplying the number of shares by the current price. In cell I6, the formula would be "=F6*H6". Copy this formula for all the stocks in your portfolio.

Include Dividend Information:

Dividends are an important part of your returns, so be sure to include them in your calculations. Unfortunately, there is no easy way to automatically import dividend data. You may need to access this information through your online brokerage or research it on financial websites. Calculate the total dividends received for each stock and enter this information in your spreadsheet.

Calculate Net Current Value:

The net current value gives you an accurate picture of each stock's performance by considering both capital gains and dividends. Calculate this by adding the gross current value and the total dividends received. In cell K6, the formula would be "=I6+J6". Copy this formula for all your stocks.

Analyze Total Gain/Loss:

To understand how your stocks are performing, calculate the total gain or loss in dollars and percentage. The total gain/loss in dollars is the difference between the net current value and the purchase cost. In cell L6, the formula would be "=K6-G6". For the total gain/loss in percentage, use the formula "=L6/G6" in cell M6.

Calculate Annualized Gain/Loss:

To compare your stocks' performance over time, calculate the annualized gain or loss. This requires a more complex formula that takes into account the amount of time that has passed since the stock was purchased. In cell N6, use the formula "=((K6/G6)^(1/(YEARFRAC(D6,$C$2)))-1)". This formula also uses the portfolio's updated date to determine the time period.

Summarize Portfolio Information:

Once you have all the information for individual stocks, you can summarize it for your entire portfolio. For fields like Purchase Cost, Gross Current Value, Total Dividends Received, and Net Current Value, simply sum up the amounts for each stock. For fields like Total Gain/Loss and Annualized Gain/Loss, use the same formulas as for individual stocks but apply them to the portfolio totals.

Format and Visualize Data:

Finally, take the time to format your spreadsheet to make it more readable and visually appealing. You can change the format of dates, currency, and percentages. You can also create charts and graphs to visualize your portfolio's performance, such as a column chart for annualized gain/loss or a pie chart for the composition of your portfolio.

Remember to update your investment information regularly to keep your portfolio analysis accurate and reflect the latest market data. By following these steps, you'll have a comprehensive understanding of your investment portfolio's performance and be able to make more informed decisions.

Add analysis formulas

Once you've inserted your stocks, converted the data, generated relevant information, and added your investment information, it's time to add your analysis formulas.

Firstly, add another column that indicates if you are gaining or losing on each stock that you hold. Set conditional formatting to visually see the value of the gain/loss. Calculate this new column by subtracting the initial investment from the current value of each investment.

Next, you can add a column that determines your target price to see how close or far you are from your target price in terms of percentage.

Additionally, you can insert charts and diagrams to understand the distribution of your investment portfolio and what makes up your overall returns.

You can also add the aggregate totals of your initial investments, the current values of your investments, and the overall returns that you realize in your portfolio.

If you want to calculate the percentage return on an asset and assess profit and loss, use the following formula: (price-entry) ÷ entry.

To calculate profit and loss, use the following formula: difference multiplied by the number of shares.

One particularly helpful feature is Excel's ability to calculate standard deviation, a complex formula that assesses risk. The standard deviation for a dataset can reveal important information about an investment's risk. The lower the standard deviation value of an asset or portfolio, the lower its risk.

To find the standard deviation of a dataset, click on the cell where you want the standard deviation value to appear. Then, 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 for which you want to find the standard deviation (be careful to select only the return values and not any headers). Then click OK, and the standard deviation calculation will appear in the cell.

Frequently asked questions