Toadstool
Well-known Member
- Joined
- Mar 5, 2018
- Messages
- 2,549
- Office Version
- 2016
- Platform
- Windows
I have stock buy/sell transactions and want to calculate my position.
Here is some sample data:
I can calculate most things I need but I'm struggling with Average Cost.
So XXX bought 100 at $1 then sold 50 at $2, so my average cost is $1.
Then bought 100 at $2 and sold 100 at $3 so I have a Holding of 50.
I'm OK with assuming the oldest was sold first so the Average Cost of my 50 would be $2 (50 of the 7 Jan transaction).
I'm OK with assuming the newest would be sold first so the Average Cost would be $1 being 50 of the 100 bought 1 Jan.
It gets trickier with ZZZ as the 19 Jan transaction takes my holding to 0 so I really need an Average Cost of $1 being either 22 or 25 Jan buys.
It may be that I just haven't had enough coffee yet but this is hurting my head.
Any ideas for a formula/function solution? (avoiding macros or VBA).
Thanks!
Here is some sample data:
I can calculate most things I need but I'm struggling with Average Cost.
So XXX bought 100 at $1 then sold 50 at $2, so my average cost is $1.
Then bought 100 at $2 and sold 100 at $3 so I have a Holding of 50.
I'm OK with assuming the oldest was sold first so the Average Cost of my 50 would be $2 (50 of the 7 Jan transaction).
I'm OK with assuming the newest would be sold first so the Average Cost would be $1 being 50 of the 100 bought 1 Jan.
It gets trickier with ZZZ as the 19 Jan transaction takes my holding to 0 so I really need an Average Cost of $1 being either 22 or 25 Jan buys.
It may be that I just haven't had enough coffee yet but this is hurting my head.
Any ideas for a formula/function solution? (avoiding macros or VBA).
Thanks!