This is a mobile optimized page that loads fast, if you want to load the real page, click this text.

Microsoft Excel Q&A

Jump to new
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
 
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?
 
Gringotts Bank said:
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?
Click to expand...

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
 
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.
 
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)
 

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.
 

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?
 
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...
 
xyzedarteerf said:
Some brokers don't let you choose which parcel you can sell.

I am a bit confused why your brokerage fees are increasing?
Click to expand...

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
 

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.
 
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? :
 
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
 
Gringotts Bank said:
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
Click to expand...
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.
 
Gringotts Bank said:
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
Click to expand...

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

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
 
Gringotts Bank said:
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
Click to expand...
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.
 
You must log in or register to reply here.

Similar threads

R
Best Books I Read Last Year
Replies
18
Views
4K
MDWake
J
Advice for a newbie
Replies
10
Views
4K
divs4ever
R
The 7 Moats of Quality Companies
Replies
7
Views
7K
divs4ever
Cheap or free Microsoft Office
Replies
0
Views
3K
sydboy007
The Beginner Trader's Manual Guide
Replies
9
Views
20K
commoyo
Menu
Log in
Register
Cookies are required to use this site. You must accept them to continue using the site. Learn more...