When folks look for a stock profit calculator, typically they are looking to quickly determine cumulative return on an investment. This simply tells you your overall return on an investment regardless of the time period in which you held the investment.
An annualized return is also useful in that you can compare investments a bit easier. For instance, if you compare the cumulative return of an investment you held for two days with one you held for 20 years, it doesn’t really give you anything overly insightful. Comparing the annualized return helps normalize the results a bit and provide a more meaningful comparison.
Moreover, when using a stock profit calculator, we will want to factor in all elements of the return in order to get a total return calculation. This should factor in dividends received (or interest in the event you’re holding bonds), and the stock profit calculator should also factor in any fees associated with the investment such as commissions or trading fees.
Quick Stock Profit Calculator
This stock profit calculator will give a quick calculation of your profit and cumulative return simply by filling out the basic data of your purchase and sell events. You can also fill out a few more fields in order to get a more full report that also includes annualized return.
How do you calculate stock profit?
If you want to opt out of using a stock profit calculator online and just do some on-paper manual calculations, here are some formulas that are likely pretty useful for you:
- Costs = (Number of Shares x Share Purchase Price) + Commissions
- Proceeds = (Number of Shares x Share Sell Price) + Dividends Received – Commissions
- Profit = Proceeds – Costs
- Cumulative Return = (Profit / Costs) x 100%
- Annualized Return using Days = ( (Proceeds / Costs) ^ (365 / Days) – 1 ) x 100%
What about multiple stocks?
What if you own multiple stocks and you want to determine your total gain? Well, if you simply want to total up the costs, proceeds and profits, you can determine the cumulative return using the formula above. If the holding periods are the same across the stock positions, then you can do annualized return as well. In most cases the holding periods of all of your positions won’t be exactly the same, so annualized return in that type of use case has some issues.
If you’re attempting to determine the profits of a portfolio, then you also have to factor in position size. Also, portfolio return usually assumes the positions are simply held (rather than bought and sold). If you want to calculate the return of a portfolio of stocks and you know the return of the individual positions, you can use this formula:
- Portfolio Return = (Position size of position 1 x return of position 1) + …. + (Position size of position n x return of position n )
Let’s show a brief example to ensure you understand the concept. Let’s say you have three positions as follows:
- Position 1: $25,000 with 10% returns
- Position 2: $25,000 with 15% returns
- Position 3: $50,000 with 5% returns
You can now use our Portfolio Return formula to determine the overall return of the portfolio as follows:
- Portfolio Return = (.25 * 10%) + (.25 * 15%) + (.5 * 5%)
- Portfolio Return = (.25 * .1) + (.25 * .15) + (.5 * .05)
- Portfolio Return = .025 + .0375 + .025
- Portfolio Return = .0875
- Portfolio Return = 8.75%
Build your own stock profit calculator using Google Sheets
I love using Google Sheets for various investment tracking tools, and building a stock profit calculator in Google Sheets is very easy. Even better, with the live quote functions that come with Google Sheets, you can build your profit calculator to live update on the fly. Let’s look at how we can build this.
We will first build a simple version of this, then we’ll build a modified version using live stock data.
Simple stock profit calculator using Google Sheets
For our initial version, let’s just build some simple input fields for the following: Number of shares, buy price, sell price, commissions paid, dividends received. Then we’ll set up simple formulas to total up the costs and proceeds, and of course a formula to get profit from the costs and proceeds. It will look something like this:
We can then add in cumulative return to complete the simple stock profit calculator.
Ok, let’s get a bit more sophisticated on our next version.
More advanced stock profit calculator using Google Sheets
In this more advanced version, let’s add in live stock data and also calculate holding period which will give us an annualized return in addition to the cumulative return.
We’re going to change the set up a bit to where we remove sell price and simply update it with the live price. This means we’re basically calculating the profit for the position on the fly and we are assuming we haven’t sold this yet. Just to simplify the examples a bit, let’s remove commissions as well. Here’s our initial setup:
What are the notable differences? First, I added a row for the stock ticker. In my example I used Apple (AAPL). I manually set a purchase price of $250 and manually set the dividends received at $75. Then on cell B5, I used the GOOGLEFINANCE function to pull the current price for Apple shares. Costs, proceeds, profit and cumulative return are all calculated based on this information similar to our more simple example we just did. Note: You can read more about using GOOGLEFINANCE function on Google Sheets here.
Now, what we want to do is add in the date of the purchase, add a cell that automatically retrieves the current date, then add a formula at the bottom for annualized return. Let’s check it out:
I manually entered the purchase date in B4. I then used the google function =TODAY() for cell B7 so that the date updates each day along with the current stock price. Then I subtracted the two dates in cell B19 in order to get the number of days held.
Then, I’ve got all the information I need in order to implement the annualized return formula based on the formula listed earlier in this article.
Now you can take the information you’ve learned and build your spreadsheet out even further as needed. You can add calculations for multiple stocks or even begin calculating profitability of an overall portfolio. The Google Sheets really are an excellent resource for building various tracking type tools for investors.