The average price for the shares you hold

old ged

New Member
Joined
Nov 1, 2022
Messages
3
Office Version
  1. 2007
Platform
  1. Windows
My broker has asked me to provide average price per share for the parcels of shares I own. All the shares are held in a tax free environment (pension SMSF) and no LIFO,FIFO, Income Tax or CGT is considered. This simplifies things as no tax implication sums are required. Most of my parcels of shares have several buys and sells.

Share valuation is the same regardless of which parcel they are sold from. I am trying to deduce the performance profit/loss rather than a taxable profit/loss. I see many variations on these sums. I imagine in a tax environment, the various tax implications cause the sums to be 'complicated' depending upon sales of shares from the which parcel?

Maybe this small table will help you see the picture. No Brokerage is included in the example. Maybe my excel table really is quite simple, assuming my sums are correct.

Buy Qty= 2500 on January 19th @ $12.00 = $30,000.
Buy Qty=1250 on March 7th @ $14.00 = $17,550
Sell Qty = -1,000 on June 26th @ $16.00 = -$16,000

My sums show the 3,750 shares cost $47,550, less the 1,000 share sold shares for $16,000

$47,550 - $16,000 = $31,550 for the remaining 2,750 shares. $31,550 / 2750 = $11.472

The remaining 2,750 shares have a average price of $11.472

So my broker will show on my profit/loss statement table 2750 shares available for sale
He will also show the average price as $11.472.
This will cause the profit and loss table to indicate the net cost for the holding as units held x net average price..$31,550(approx)
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Sorry if the question is vague. I am just trying to work out what I need to do...if anything...with Excel to give me the net average price for share parcels answer to my broker question. Maybe I do not need anything complicated in excel, it is just simple maths?
 
Upvote 0
It appears that your calculations as shown in Post 1 do the trick. It is a case of simple math.
 
Upvote 0
Solution
Thanks for the reply alansidman.
As you say, this is more of a straight forward math exercise, rather than an excel issue, even though, excel is used for the exercise.
I will close this enquiry. Thanks again.
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top