[judy.warren]

What is the formula that will add to the cost of stock, the commission for an odd lot, (where the lots of 100 have one commission and the remaining amt <100 has a different commission). Also, the formula that will subtract commission from the proceeds on an odd lot.

[ebaines]

Are you asking for the specific commission schedule for odd lot trading by a particular broker? There is no set fee for this - it all depends on who your brokerage house is. I'm sure you can find the schedule of trading fees on the web site of your brokerage house.

[judy.warren]

It could be for any commission rate. I don't think I ever mentioned that the formula I am looking for is an Excel formula. How do I enter a formula that will figure the odd lot and round lot commissions separately and then add or subtract them from the cost or the proceeds?

[ebaines]

If there is one rate for purchasing in blocks of 100, and another rate for lots of 1, then what you need is a formula that determines how many times 100 divides into the amount being purchased, and what the remainder is after you do the division. Let:

= number of shares
= number of whole lots (multiples of 100),
= number of individual lots
= commisision rate for whole lots (per hundred)
= commission rate for individual shares.

Then:

Commission =

where W and I can be calculated using the Excel functions:

W=floor(N/100, 1)
I = mod(N, 100)

[judy.warren]

IT WORKS!! Thank you. You are awesome.