The break-even point helps you determine what selling price to target. Once Excel calculates the solution to this formula for a given stock, it will tell you your break-even point for this stock. You could also add in your state income tax rate as well to this number. 'Tax Rate' will either be the long-term capital gains rate for that year, if you plan to hold the stock for one year or longer, or your federal tax bracket, if you expect to sell the stock before one year is up. Perhaps your list is stored in a table (Insert > Table) and looks like Figure 1. Let’s say you have a handful of tickers and you want to view their current prices or related information. It is 'Commission*2' in the formula, because you will have to pay this commission twice for the one stock: once when you buy and once when you sell. Note: This article is presented with Excel O365 for Windows not all versions of Excel include the features discussed. 'Commission' is the amount that you must pay to your brokerage service each time you buy or sell a stock. 'Shares Purchased' is the number of shares you bought. 'Buying Price' is the price you paid per share of the stock. For each of the variables, you could either put in the exact numbers from the previously gathered data, or you could build the formula using cell references.
First, add a column labeled 'Break-Even Price.' Then, in the cells below for each stock you're tracking, input the following formula: =(((Buying Price*Shares Purchased+(Commission*2))/Shares Purchased)-Tax Rate*Buying Price)/(1-Tax Rate).
Once you have your basic information input into Excel, it's time to begin to use the calculation power of the software to make investing decisions.
