Need help with formulas calculating cost basis on shares

[ashko20]

Hi All

Was hoping to get some help in creating a trading log to track my stock purchases over time. I am stuck in not being able to get the formula for calculating my weighted average cost basis for each stock given multiple purchases over time. I have tried using the sum product formula with a couple of conditions so it can be presented in a report however the formula is returning an incorrect value. 

The formula I am using to calculate cost basis is: =IF($CL2="HUT",SUMPRODUCT($AI$2:$AI2,$AJ$2:$AJ2)/CN2,IF($CL2="PLTR",SUMPRODUCT($AI$2:$AI2,$AJ$2:$AJ2)/CP2,""))

Looking at the data however the cost basis is incorrect for PLTR but correct for HUT. 

Is there an en easier way to get this information?

Have pasted the sample data below:

Symbol	Quantity	TradePrice	Buy/Sell	Net Proceeds	Identifier (helper)	HUT Share Balance	PLTR Share Balance	COST BASIS
HUT	100	7	BUY	-$650	HUT	$100	$0	$7
HUT	500	6	BUY	-$2,750	HUT	$600	$0	$6
HUT	200	3	BUY	-$600	HUT	$800	$0	$5
PLTR	100	20	BUY	-$2,000	PLTR	$800	$100	$60
PLTR	200	20	BUY	-$4,000	PLTR	$800	$300	$33
PLTR	15	8	BUY	-$115	PLTR	$800	$315	$32
PLTR	100	12	BUY	-$1,200	PLTR	$800	$415	$27

[#2]

@ashko20 

It's not clear to me (as somebody who also tracks stock and options purchases and sales) what your other formulas are doing here, before we even get to that final column. And, while we're at it, headings like "Net Proceeds" are misleading in a minor way since that tends to imply proceeds after a sale; I'd go with plain ol' "Net" to cover both outflow and inflow of funds.

Continuing, though, with points where clarification is needed:  in your first row, why are you showing $650 for a transaction where the quantity is 100 and the price 7? Why not $700? Is there a $50 fee or commission hidden in there?

Now, to get to the calculation you asked about: it would be helpful if you could post a copy of this actual spreadsheet on OneDrive or GoogleDrive with a link pasted here. Your posted data here leaves off the Column and Row headers so we can't really determine which set of cells is referenced by (for example) $CL2, which is central to the IF condition in your formula. Is that the first "SYMBOL" column, or the "Identifier (helper)" column?

All of that having been said, if I were doing this, I'd keep the transaction records as one database, and place the calculation of weighted average cost in a separate sheet (call it a "Dashboard" sheet), where it wouldn't vary row by row but would simply aggregate all of the data pertinent to HUT or PLTR into a display with one row per stock. In the attached I've put that summary data below the data table, but that's not where I'd keep it in the real workbook. Good design generally separates a transactional database from whatever summaries you're wanting to do.

The formula that calculates weighted cost is this:

=ABS(SUM(FILTER($E$5:$E$11,$A$5:$A$11=A17)))/SUM(FILTER($B$5:$B$11,$A$5:$A$11=A17))

Essentially all it's doing is adding up the numbers in column E ("Net Proceeds") for the stock in question and dividing that by the sum of the shares purchased (column B) and showing the absolute value of that result.

Assuming your larger real database is more complex, showing sales as well as purchases, multiple stocks, the formula would need to be tweaked a bit to account for the possibility of sales, etc., but the basic idea would still work.

The formulas here are very dependent on the FILTER function, which requires Excel 2021 or newer (a Microsoft 365 subscription)

[#3]

Mathetes - been a while since you posted this and I hope you are still out there... I really like your answer / explanation to this one - great job. Thank you it is very helpful.
I'm on to the more complicated version - I have some sells and need to incorporate that into my weighted cost...
Bill

[#4]

Without seeing how you’re approaching this, it’s rather difficult to give any advice. For that matter, you haven’t actually asked 😏

But if you are seeking help, you’d help us (me or one of the other frequent contributors) help you by posting a copy of your workbook. Use OneDrive or equivalent to post, and paste a link here that grants access.

[#5]

@mathetes - First I'm not the best with computers so I hope I get this right...

Data looks like this:

Dashboard (results) looks like this:

 Here is a link to the file on Onedrive: Weighted Cost w sell.xlsx

Trying figure out a good way to get the the current weighted cost of a particular stock after I sell some shares. Could be many lots that are used when a stock used dividend reinvestment. I didn't put that is in this table but can if needed.

Any suggestions for doing things better / more efficiently will be appreciated.

Thanks in advance for any help!

 

[#6]

@bmissell 

What you're doing looks fine, so far as it goes.

So much depends on your goal, how precise you want to be with lots. My main question is whether or not you're doing this in a taxable account, as opposed to an IRA of some kind. If you need to be reporting capital gains to the IRS, that could have implications for how you track sales, whether or not you're able to do FIFO on the shares that are sold, etc. I have no experience with that kind of tracking--I do all my investing in a traditional IRA, so tax consequences are not a factor. I also, frankly, do not do any of the volume of buys and sells on most of the stocks or ETFs I have. Much more long-term-buy-and-hold.

[#7]

Thanks Mathetes. Some IRA accounts and some taxable. I've only been doing FIFO so far - easier to work and not spending that much time trying to beat all the angles. I'm selling more now since I've retired, but generally a holder as well. Saving the IRA accounts for last at this point to keep the tax free compounding going. Thanks for looking at it.

[#8]

Following this. Looking for help in this very topic. I have a taxable account. I have buys, sales, dividends paid, dividends reinvested. Basically, how do I figure my cost basis? Thank you.

[#9]

@macandal 

Your question is, I would suggest, less of an Excel question than it is an accounting question. So let me refer you to this answer I got via Google.

If you need help with implementing that in Excel, feel free to come back, but come back with a sample (attached to your inquiry) of the spreadsheet you've created to track your " buys, sales, dividends paid, dividends reinvested" in a particular stock.

[#10]

@mathetes, thanks for the link. It's both. I also need help implementing this in Excel.

Thank you.

[#11]

@mathetes also, that link does not work.

[#12]

@macandal 

Let's try the link again. Here. But if that doesn't work again, you can just enter "calculating cost basis for stock" in Google and find it directly.

And you'll need to help me help you by putting on-line a copy of whatever records you've been keeping in Excel on at least one stock where you've done a fair amount of buying, selling, getting and reinvesting dividends. It shouldn't be hard to get that kind of history from your broker. But I would hope you have at least some knowledge of how to store the records of the transactions. You don't need to do any calculations. Just date, stock symbol, transaction description, quantity, price (per share).... that kind of stuff.

Put a spreadsheet on OneDrive or GoogleDrive with a link pasted here that grants access.

[#13]

@macandal 

[edit: the original spreadsheet I posted on 9/7 had some incorrect data; that's been corrected as of the morning of 9/8]

Here's a spreadsheet that should get you started. This only deals with a hypothetical history of purchases and dividends of Starbucks stock, beginning back in November of 2020. No sales, but a couple of subsequent purchases at low points in the stock's price.

If you want more help--I have no idea how familiar and comfortable you are with Excel--then feel free to return with followup questions. But please include a spreadsheet with some sample of your own real history connected with a real stock.