Average weighted price - checking broker data

[madeye89]

Good morning everyone,

I am seeking your assistance as I have been struggling for days to practice calculating the average cost basis displayed by my broker for an ETF stock quote. The challenge I'm facing is that there have been various sales over time, and as a result, I am unable to revert to the original value using the simple weighted average method.

Specifically, I have attempted both the FIFO and LIFO methodologies, but I am uncertain of the value obtained as the result provided by my broker is 30.82411. As a precaution, I would also like to highlight the commissions charged by the broker, which, to my understanding, should not impact the average cost basis.

Should this not be feasible, I will consider investigating directly with the broker.

This is the input data:

Date	Type	Quantity	Price	Gross Value	Net value	Commission
19/01/2024	B	76,00​	33,27500​	2.547,90	2.528,90	19,00
15/12/2023	B	76,00​	32,95500​	2.507,53	2.504,58	2,95
15/11/2023	B	80,00​	31,34000​	2.510,15	2.507,20	2,95
23/10/2023	S	350,00​	30,01000​	10.484,50	10.503,50	(19,00)
15/09/2023	B	78,00​	32,12660​	2.508,82	2.505,87	2,95
16/08/2023	B	79,00​	31,29500​	2.475,26	2.472,31	2,95
15/06/2023	B	79,00​	31,38500​	2.482,37	2.479,42	2,95
15/05/2023	B	81,00​	30,79630​	2.497,45	2.494,50	2,95
17/04/2023	B	82,00​	30,31500​	2.488,78	2.485,83	2,95
15/03/2023	B	86,00​	29,11000​	2.506,41	2.503,46	2,95
15/02/2023	B	82,00​	30,27110​	2.485,18	2.482,23	2,95
17/01/2023	B	84,00​	29,80780​	2.506,81	2.503,86	2,95
20/12/2022	S	319,00​	28,81500​	9.191,99	9.191,99	0,00
20/12/2022	S	1,00​	28,85000​	18,90	28,85	(9,95)
16/11/2022	B	82,00​	29,69500​	2.453,99	2.434,99	19,00
17/10/2022	B	88,00​	28,44770​	2.506,35	2.503,40	2,95
15/09/2022	B	83,00​	29,53000​	2.453,94	2.450,99	2,95
15/07/2022	B	63,00​	29,07000​	1.833,55	1.831,41	2,14
15/06/2022	B	64,00​	28,26900​	1.811,36	1.809,22	2,14
16/05/2022	B	85,00​	29,42500​	2.520,13	2.501,13	19,01
19/04/2022	B	26,00​	31,05360​	809,13	807,39	1,74
15/03/2022	B	28,00​	29,18740​	818,99	817,25	1,74
15/02/2022	B	27,00​	30,56900​	827,10	825,36	1,74
18/01/2022	B	27,00​	30,68440​	830,22	828,48	1,74
15/12/2021	B	26,00​	30,98390​	807,32	805,58	1,74
15/11/2021	B	27,00​	30,70000​	830,64	828,90	1,74
15/10/2021	B	29,00​	28,86000​	838,68	836,94	1,74

Additionally, it would be a bonus to acquire a simple logic in Excel, without scripts or macros, to replicate this calculation for other securities.

Thank you all for your support!

 

[SanjayGMusafir]

I am seeking your assistance as I have been struggling for days to practice calculating the average cost basis displayed by my broker for an ETF stock quote. The challenge I'm facing is that there have been various sales over time, and as a result, I am unable to revert to the original value using the simple weighted average method.

There can be two discrepancies

1. One is broker may or may not be including commission(s) paid by you
2. Other is generally Broker's system calculate various factors for different decimal points, varying between 2 to 4, but show you a rounded figure. They may seems small but effect calculations significantly.

It's best to coordinate with your broker and understand the difference.

 

[madeye89]

Thank you for your insights.

I will indeed reach out to my broker for further clarification. However, given that the results are already at hand, I am interested in exploring with the forum's assistance, the possible methodology my broker might be applying. Any insights or similar experiences shared by any forum members would be greatly appreciated in better understanding this situation!

 

[madeye89]

Hello, I'm wondering if anyone is available to offer assistance? It seems my case has not yet attracted any attention.

Thank you so much!

 

[SanjayGMusafir]

the possible methodology my broker might be applying

You need to ask what methodology your broker is applying... We can't guess it...

It's almost impossible to believe that your post has not gone through the eyes of experts in the forum...

 

[madeye89]

Thank you for your prompt response. Indeed, I have already forwarded the issue to my broker, who is not known for its swift replies.

My primary aim was to seek assistance on the Excel help forum to address a problem I believe possesses all the necessary and sufficient information for resolution, including dependent and independent variables. Thus, to also verify the potential calculations made by the broker, I asked for support from Excel experts. Please note, my intention is not to create controversy, but I am concerned that my query has not garnered the necessary interest here.