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.

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.

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?

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)

IT WORKS!! Thank you. You are awesome.