August 08, 2023

The stock market may seem intimidating at first, but it doesn’t have to be. For savvy investors, a spreadsheet tool like Microsoft Excel can help track your portfolio and investments with ease.

When you use Excel to create an investment tracking spreadsheet, not only can you visualize the performance of your stocks at a quick glance, but you’ll also be able to calculate future performance and degrees of risk, profit, and loss, as well as compare different types of investments across category, futures, and types.

Plus, when paired with a Surface device, the combination of Microsoft products becomes even more intuitive. Write over an Excel cell to replace its content, scribble over cells to delete their data, and handwrite new data using your fingers or digital pen with your Surface device’s touchscreen capabilities. Learn more about creating an investment tracker that can help you assess past performance and give you insight into future returns.

Take advantage of the versatility of Surface

A Surface 2-in-1 device combines the best of both worlds: Not only does it pack a virtual, touchscreen keyboard, but it also features an intuitive PixelSense™ touch display that makes visualizing and interacting with a spreadsheet even easier. Pinch and zoom with your fingers to examine data, and key in figures through the touchscreen.

Use the Surface Slim Pen 2 with your Surface device to point and write directly on the touchscreen.¹ If you think more intuitively by writing down your calculations, you can use the Slim Pen 2 directly in a digital note-taking app like OneNote and enjoy the convenience of digitizing your thoughts.¹

Connect Excel to real-time investment tracking

Excel can connect to real-time stock tracking information so you can always see how much your stocks are worth.

To get a stock quote, first type in the name of the ticker symbol. Next, select Data > Stocks. Excel will automatically look up the ticker symbol or company, then convert your cells to the stocks data type. The cells will display live stock information from Microsoft’s online sources.

From there, you can add columns that will display pertinent information, such as numbers of shares, symbols, and closing prices.

What do you need to include in an Excel investment tracker?

No matter what your goals are in your investing journey, there are a few baselines you should consider in forming an Excel investment tracker spreadsheet:

• Investment information. This basic info includes the account name, stock ticker symbol, name of the investment, and what category it falls into. This will take up for the first four columns of your investment tracker. Categorize your investments by type, and use this information to cross-reference it against online investment trackers.
• Shares. How many shares of this investment do you have? This is vital in keeping track of your holdings and adding to or divesting from your existing holdings.
• Price and value. As explained above, you can connect Excel to real-time online stock information. You can also assign Excel functions for up-to-the-moment calculations.

Determine the break-even point

The break-even point is the price a stock needs to achieve that will ensure a net income of zero. Anything beyond that is pure profit. Here’s a formula you can use:

• = (((Buying Price*Shares Purchased+(Commission*2))/Shares Purchased)-Tax Rate*Buying Price)/(1-Tax Rate)

For an accurate prediction of profit, you’ll need to consider the following categories:

• Buying price. How much you paid for a stock, per share
• Shares purchased. The number of shares of a stock that you own
• Commission. What you’ll pay a brokerage service to buy or sell a stock
• Tax rate. Depending on the length of investment, you’ll need the figure for a long-term capital gains rate if you hold onto your stock for the long run, or a federal tax bracket if you sell before the end of the year
• State income tax rate. An optional value that varies state by state

These will be listed as additional columns in your investment tracking spreadsheet.

Excel categories for selling stocks

Once you sell a stock, it’s important to keep track of these results so you can see how much capital you have for new investments and make predictions on what to buy next. Here are recommended categories for this worksheet:

• Shares sold
• Date of sale
• Selling price
• Purchase price
• Capital gains

You can calculate capital gains and losses with a formula that adds or subtracts your sale price from your initial investment, including any commissions or brokerage fees.

An Excel investment tracker on your Surface device makes it easy to see stock trends and your own investments at a glance, so when the opportune time comes, you can take action.