Microsoft Excel offers countless capabilities when it comes to business, including project management, invoicing, presentations, budgeting, and more. Here at ExcelHelp, we pride ourselves on helping businesses small and large understand how Excel can bring them closer to achieving their goals.

Some people wrongfully assume that Excel is limited to project management, accounting, and basic data entry. While all that is true for Excel, it limits the incredible platform to something much less dynamic than its true capabilities.

If you’re in the business of stocks and investments, how do you track your portfolio’s progress? You could check your bank records or online banking, but is there a way to handle all the tracking from your personal computer?

Today, we’ll show you how you can optimize Microsoft Excel to track stocks and other financial investments. Then, we’ll offer a few tips and examples to help you visualize this great tool.

Let’s get started!

How to Use Excel for Tracking Stocks and Investments

As investors, you shouldn’t have to rely on other businesses to track your investment performance. After all, it’s your money. Why not track investment data yourself through Microsoft Excel? With the platform, you can use worksheets to track trades, gain valuable insights to guide future investments, and measure your investment success.

1. Display Basic Information for Each Stock and Investment

You might use Excel to record information about projects and tasks, like costs, dates, people involved, and more. You can do the same with your investments.

Set aside one row for each stock and categorize the information with columns indicating name, ticker symbol, buying price, selling price, commission, investment partners, and notes.

Even if you purchase two of the same stock at different times, make sure you separate them to accurately track the progress.

2. Find Your Break-Even Point

Your first step towards informing investment strategies with Excel is to find the break-even point for each investment. You can do so with a simple calculation.

Start by adding a “Break-Even Price” column and for each stock below, add this formula:

(((Buying Price*# of Shares+(Commission*2))/# of Shares)-Tax Rate*Buying Price)/(1-Tax Rate)

Note: The commission is multiplied by two because you’ll need to pay the brokerage twice, once when you purchased the stock and once again when you sell it.

Once you have your break-even point, it becomes easier to know a general idea for how to price your selling stock.

3. Track Capital Gains

For all the stocks you sell, your brokerage should tell you what you paid in capital gains. Why not calculate all that yourself with Excel? Here’s how to do it:

Create a new worksheet with the following columns: symbol, # of shares sold, selling price, sale date, purchase price, long-term capital gains (held over a year), and short-term capital gains (less than a year).

4. Find the Percent Return

The percent return tells you what you’ve made on your investment. To find it, you take the current price and subtract the entry price from that, then divide the total by the entry price.

In Excel, you can add a formula into another column by entering =(current price-entry price).

5. Find Risk by Calculating Standard Deviation

How risky are your investments? You can find out by calculating the standard deviation in Excel. This calculates the distance between returns and statistical average, helping you understand an investment’s volatility throughout the lifetime of its time in your portfolio. The standard deviation shows you when your returns are looking risky, helping you better strategize when it’s time to sell or hold onto your investment.

Tips for Tracking Investments in Excel

Excel offers you many calculations and organization capabilities to help you stay on track with your investments. Maximize its potential by following these helpful tips!

Separate different purchases from each other: Put each purchase into a new row, even if it’s the same stock. When you buy a stock at different times, the purchase and possibly your future selling price will be different. This will make all related calculations different as well, so it’s best to separate all new purchases.

Track dividends and capital gains: And any other indications of profit or loss to ensure your records are as accurate as possible.

Track prospective investments, too: If you’re skeptical about a certain stock, track its performance without taking the plunge. As you track its changes, you can make a more informed decision about whether or not you should buy it.

Final Thoughts

Brokerages track investments for you and would likely be happy to tell you any calculation or measure that you’re looking for. But you’ll improve your investment knowledge and financial literacy by learning how to track your investments yourself.

Try your best to keep up-to-date spreadsheets and compare them against the brokerage’s every quarter. If you need any support using Excel to further your business goals, ExcelHelp has skilled consultants ready to advise you on how to best use Excel to meet your needs. We’re only an email away — contact us today for a consultation.

Schedule a Consultation