Forum

Notifications
Clear all

Calculation for Average Cost & Cost of Holding

9 Posts
4 Users
0 Reactions
101 Views
(@kasey)
Posts: 20
Eminent Member
Topic starter
 

I was intrigued by Mynda's Stocks Dashboard so decided to replicate it for my own needs.

I was unable to calculate the Average Cost & Cost of Holding. I have attached a sample sheet with data. Including info from Investment Provider site.

Cell G8 must be £358.96, not sure how this is arrived at.

 

Thanks so much for your time.

Kasey

 
Posted : 17/07/2024 10:18 am
Anders Sehlstedt
(@sehlsan)
Posts: 971
Prominent Member
 

Hello,

I am not sure I understod this correct, I have followed the instructions but am far off from your wanted value. Do check if this is usable or not.

Br,
Anders

 
Posted : 18/07/2024 4:01 pm
(@kasey)
Posts: 20
Eminent Member
Topic starter
 

Hi

I can't get my head round it either. Very confusing

Thanks

 
Posted : 19/07/2024 9:46 am
(@keebellah)
Posts: 373
Reputable Member
 

I have no idea of all these calculations but the formula in column G you have now does not really seem to work

In G6 I did the following and COUNTIFS($B$6:B6,"<>Sold") returns the number of rows <> Sold  =SUMPRODUCT(($E$6:E6)*(COUNTIFS($B$6:B6,"<>Sold")))/F6

I cannot imagine how G8 could be = 358.96 I'll leave that to the number crackers

 
Posted : 20/07/2024 2:05 am
Riny van Eekelen
(@riny)
Posts: 1195
Member Moderator
 

I started by recreating the scenario that was given in the picture. Then I copied that and filled in the numbers from your own example and arrived at a Cost of Holding of 362.62, which is slightly off from your number but that could well be due to rounding. See attached.

 
Posted : 20/07/2024 5:35 am
Anders Sehlstedt
(@sehlsan)
Posts: 971
Prominent Member
 

Hello,

With the help by Hans’s and Riny’s comments I did some changes and now got the wanted result.
Hopefully it works with the real data too.

Br,
Anders

 
Posted : 20/07/2024 10:40 am
(@kasey)
Posts: 20
Eminent Member
Topic starter
 

Oh great @Anders, please could you share your worksheet?

I get 362.62 like @Riny

 

Thanks

 
Posted : 21/07/2024 8:33 am
Anders Sehlstedt
(@sehlsan)
Posts: 971
Prominent Member
 

Hello,

Sorry, here comes the updated copy.

Br,
Anders

 
Posted : 23/07/2024 12:52 pm
(@kasey)
Posts: 20
Eminent Member
Topic starter
 

Brilliant thanks so much Anders, I really appreciate your help!!

 
Posted : 25/07/2024 8:00 am
Share: