Microsoft Excel Q&A

[Gringotts Bank]

Hi all. New question.

I have column A with a whole range of different numbers.

I'd like each of the rows in column B to be = A*50%. I'd also like each of the rows in column C to = A+B.

I'm using Gnumeric but it's compatible with Excel. Does anyone know the formula please?

 

[skyQuake]

Hi all. New question.

I have column A with a whole range of different numbers.

I'd like each of the rows in column B to be = A*50%. I'd also like each of the rows in column C to = A+B.

I'm using Gnumeric but it's compatible with Excel. Does anyone know the formula please?

in cell b2 you'll have

=a2*0.5

in cell c2 you'll have

=a2+b2

and drag down

 

[Gringotts Bank]

Hi sky.

I'm very new to excel. Could you explain in more detail please?

I have B2 and C2, but how do i drag it across all data?

 

[skyQuake]

Hi sky.

I'm very new to excel. Could you explain in more detail please?

I have B2 and C2, but how do i drag it across all data?

Put you mouse in bottom right corner of b2
it should look like a black plus sign, then u drag down, and it'll copy the formula

 

[Gringotts Bank]

Thank you!

 

[skc]

For applying formula across cells, your best friend is Ctrl-D (fill down) or Ctrl-R (fill right).

Say your formula is typed in cell C2, and you want C3 to C100 to have the same formula. You highlight from C2 down to C100, then Ctrl-D. Done.

 

[Gringotts Bank]

Good tip thanks skc.

 

[ls111]

Hi all,

Im currently working on my spreadsheet and am hoping someone can help me out with a calculation issue im having. Here's the situation:

PARCEL 1. I buy 1000 shares in company AAA at $1. The brokerage is $20

PARCEL 2. I buy a further 2000 shares in AAA at $2. The brokerage is $30

PARCEL 3. I sell 2600 in AAA at $3. The brokerage is $40.

Because my parcels are all different sizes and different prices, I am wondering how to calculate the brokerage.

Is there more than one way to do it? ie: I sell 1000 from parcel 1 and 1600 from parcel 2

Or choose to sell 2000 from parcel 2 and 600 from parcel 1.

Or Use the average purchase price?

Could someone please clarify if these are the only 3 ways to do it...and if possible, the mathematical calculations as to how to show the brokerage fee on the transaction.

Thanks Much

 

[skyQuake]

Hi all,

Im currently working on my spreadsheet and am hoping someone can help me out with a calculation issue im having. Here's the situation:

PARCEL 1. I buy 1000 shares in company AAA at $1. The brokerage is $20

PARCEL 2. I buy a further 2000 shares in AAA at $2. The brokerage is $30

PARCEL 3. I sell 2600 in AAA at $3. The brokerage is $40.

Because my parcels are all different sizes and different prices, I am wondering how to calculate the brokerage.

Is there more than one way to do it? ie: I sell 1000 from parcel 1 and 1600 from parcel 2

Or choose to sell 2000 from parcel 2 and 600 from parcel 1.

Or Use the average purchase price?

Could someone please clarify if these are the only 3 ways to do it...and if possible, the mathematical calculations as to how to show the brokerage fee on the transaction.

Thanks Much

Im a bit confused to what you're asking, you've already provided the brokerage? (20+30+40)

 

[YELNATS]

Hi all,

Im currently working on my spreadsheet and am hoping someone can help me out with a calculation issue im having. Here's the situation:

PARCEL 1. I buy 1000 shares in company AAA at $1. The brokerage is $20

PARCEL 2. I buy a further 2000 shares in AAA at $2. The brokerage is $30

PARCEL 3. I sell 2600 in AAA at $3. The brokerage is $40.

Because my parcels are all different sizes and different prices, I am wondering how to calculate the brokerage.

Is there more than one way to do it? ie: I sell 1000 from parcel 1 and 1600 from parcel 2

Or choose to sell 2000 from parcel 2 and 600 from parcel 1.

Or Use the average purchase price?

Could someone please clarify if these are the only 3 ways to do it...and if possible, the mathematical calculations as to how to show the brokerage fee on the transaction.

Thanks Much

Why wouldn't you calculate this on the basis of reporting the lowest net capital gain to the ATO?

ie. Your net sales value is $7760.00 and the cost base for this is $4642.00 (= 2000x$2 + $30 + 600x$1 + 0.6x$20) = net capital gain of $3118.00.

 

[xyzedarteerf]

Hi all,

Im currently working on my spreadsheet and am hoping someone can help me out with a calculation issue im having. Here's the situation:

PARCEL 1. I buy 1000 shares in company AAA at $1. The brokerage is $20

PARCEL 2. I buy a further 2000 shares in AAA at $2. The brokerage is $30

PARCEL 3. I sell 2600 in AAA at $3. The brokerage is $40.

Because my parcels are all different sizes and different prices, I am wondering how to calculate the brokerage.

Is there more than one way to do it? ie: I sell 1000 from parcel 1 and 1600 from parcel 2

Or choose to sell 2000 from parcel 2 and 600 from parcel 1.

Or Use the average purchase price?

Could someone please clarify if these are the only 3 ways to do it...and if possible, the mathematical calculations as to how to show the brokerage fee on the transaction.

Thanks Much

Brokerage fee is deducted once you sell the parcel.
I assume your selling the same stock code.
so if you bought AAA @ $1 x 1000 parcels brk fee $19.95 charged when you sell this parcel.
bought another 2000 parcels brk fee $19.95 charged when you sell this.

so if yo sell 2600 parcel you will be charged brk fee of 19.95 x 2. I assume you will be keeping 400 parcels since you bought 3000 units.

Some brokers don't let you choose which parcel you can sell.

I am a bit confused why your brokerage fees are increasing?

 

[ls111]

Thanks for the replies, however i'll clarify what i'm actually after.

Below is an example of a straight forward buy and sell:

Bought AAA. 1000 Units. Purchased at $1. Brokerage is $20
Sold AAA. 1000 Units. Sold at $2. Brokerage is $30

(The brokerage figures I am using is only an example)

When I record this transaction I have the following:

Company Shares Buy Price Sell Price Brokerage Net Profit

AAA 1000 $1 $2 $50 $950

But what i'm basically asking is how do you calculate the brokerage when you sell a parcel of shares that is incomplete or spans across 2 separate buy parcels.

So going back to my example...

PARCEL 1. I buy 1000 shares in company AAA at $1. The brokerage is $20

PARCEL 2. I buy a further 2000 shares in AAA at $2. The brokerage is $30

PARCEL 3. I sell 2600 in AAA at $3. The brokerage is $40.

The brokerage cannot be 40+30+20 because I have not sold my entire holding like in the first example.

Yelnats, yes that is an option however if I was to record the transaction like in the table format in the first example, what would the brokerage cost actually be?

I hope this makes a bit more sense...

 

[YELNATS]

Some brokers don't let you choose which parcel you can sell.

I am a bit confused why your brokerage fees are increasing?

Brokerage fees typically increase with the values of buy/sell transactions, as is the case in the example of the three transactions provided.

Interesting comment about "some brokers not allowing you to choose which parcel you can sell". I haven't come across this. I have always believed that your holding in one stock code, for example in code AAA, was considered by brokers as just one homogeneous pool of stock, and they didn't know or particularly care which parcel/s you were selling from.

Can you please elaborate.

Thanks

 

[skc]

Thanks for the replies, however i'll clarify what i'm actually after.

Below is an example of a straight forward buy and sell:

Bought AAA. 1000 Units. Purchased at $1. Brokerage is $20
Sold AAA. 1000 Units. Sold at $2. Brokerage is $30

(The brokerage figures I am using is only an example)

When I record this transaction I have the following:

Company Shares Buy Price Sell Price Brokerage Net Profit

AAA 1000 $1 $2 $50 $950

But what i'm basically asking is how do you calculate the brokerage when you sell a parcel of shares that is incomplete or spans across 2 separate buy parcels.

So going back to my example...

PARCEL 1. I buy 1000 shares in company AAA at $1. The brokerage is $20

PARCEL 2. I buy a further 2000 shares in AAA at $2. The brokerage is $30

PARCEL 3. I sell 2600 in AAA at $3. The brokerage is $40.

The brokerage cannot be 40+30+20 because I have not sold my entire holding like in the first example.

Yelnats, yes that is an option however if I was to record the transaction like in the table format in the first example, what would the brokerage cost actually be?

I hope this makes a bit more sense...

If it's for tax accounting purpose I would use the highest brokerage to keep my CGT down as suggested earlier.

If it's for your own performance tracking purpose I would just use the average buy brokerage.

But for trading performance, it may matter a bit psychologically whether you take the higher profits or the lower profits first.

E.g. You bought 2 x 1000 share parcels at $1 and $2. The share price is $2.50 and you want to sell half your holdings. Recording the $1 parcel sold means you have banked a $1.5 profit and keep a 50c open profit at risk, whereas recording the $2 as sold means the exact opposite.

If you have problems letting profits run, then having the cheaper parcel recorded as sold could help, because with the $2 parcel your percent of open profit is much smaller, and you may be psychologically more patient to let it rise further.

Hope that makes sense.

 

[pixel]

Thanks for the replies, however i'll clarify what i'm actually after.

Below is an example of a straight forward buy and sell:

Bought AAA. 1000 Units. Purchased at $1. Brokerage is $20
Sold AAA. 1000 Units. Sold at $2. Brokerage is $30

(The brokerage figures I am using is only an example)

When I record this transaction I have the following:

Company Shares Buy Price Sell Price Brokerage Net Profit

AAA 1000 $1 $2 $50 $950

But what i'm basically asking is how do you calculate the brokerage when you sell a parcel of shares that is incomplete or spans across 2 separate buy parcels.

So going back to my example...

PARCEL 1. I buy 1000 shares in company AAA at $1. The brokerage is $20

PARCEL 2. I buy a further 2000 shares in AAA at $2. The brokerage is $30

PARCEL 3. I sell 2600 in AAA at $3. The brokerage is $40.

The brokerage cannot be 40+30+20 because I have not sold my entire holding like in the first example.

Yelnats, yes that is an option however if I was to record the transaction like in the table format in the first example, what would the brokerage cost actually be?

I hope this makes a bit more sense...

fwiw, here is how I calculate the cost base of my holdings:

Assume I bought 10,000 x AAA at $1 each, and a month later 9,000 x AAA at $1.10, and paid $25 brokerage each time, I simply add up the costs (including brokerage), add up the number of shares, and divide one by the other to get to the cost base per share.
In the example:
$10,000 + $9,900 + 2x$25 comes out to $19,950 for 19,000 shares or $1.05 cost per share.

If I sell later 15,000 of those shares at $2 each and pay $30 brokerage, the calculation is easy:
15,000 shares @ $1.05 have cost me $15,750
the credit I receive is $29,970 (15,000 x $2 - $30 brokerage), or $14,220 clear profit.

It's only in borderline cases, where I sell shares that were bought some more, some less than 12 months ago, that I have to work out percentages.
E.g. in the above example, if I bought the first lot over a year ago, but held the second lot less than 12 months, I'll split the sale profit and calculate the Capital Gain for each position. So, if I sell 15,000 shares @ $2 each, pay $30 brokerage, then I'll allocate 2/3rds of the brokerage to the first 10,000 I sold, making a profit of
($20,000 - $20) minus $10,025 costs = $19,980 - $10,025 = $9,955 to which half CGT applies,
and
($10,000 - $10) minus 5/9ths of $9,925 that the second lot cost me less than12 months ago for a full CGT of $4,476.11.
Clear as mud? :

 

[Gringotts Bank]

Hi,

I'd like to be able to sort column data by highest or lowest at top. Is there a formula for this? Or perhaps some other software that can do it?

edit: I've tried clicking z->a and a->z but it only works if I highlight more than one column

 

[pixel]

Hi,

I'd like to be able to sort column data by highest or lowest at top. Is there a formula for this? Or perhaps some other software that can do it?

edit: I've tried clicking z->a and a->z but it only works if I highlight more than one column

Are the values in that column calculated by any chance?If they are, sorting won't work because immediately after sorting, the values will be recalculated.
If they're true values, sorting should be possible. Excel 2007 does give me a warning though, and I have to confirm that I want to sort only that one column and not any adjacent ones.

 

[skc]

Hi,

I'd like to be able to sort column data by highest or lowest at top. Is there a formula for this? Or perhaps some other software that can do it?

edit: I've tried clicking z->a and a->z but it only works if I highlight more than one column

Data > Sort. Click option don't expand collection.

 

[mr. jeff]

Are the values in that column calculated by any chance?If they are, sorting won't work because immediately after sorting, the values will be recalculated.
If they're true values, sorting should be possible. Excel 2007 does give me a warning though, and I have to confirm that I want to sort only that one column and not any adjacent ones.

just use the sort button, then choose highest to lowest or whatever in the window that pops up, and if it is equation results then copy and paste special->values into another column

 

[Logique]

Hi,
I'd like to be able to sort column data by highest or lowest at top. Is there a formula for this? Or perhaps some other software that can do it?
edit: I've tried clicking z->a and a->z but it only works if I highlight more than one column

GB highlight the range, then click Data>Sort, then select which column of data to sort by, and whether ascending or descending. Be sure to highlight the whole range, or it can become very confusing very quickly.