Adding and Subtracting Brokerage from Stock Market Transactions

[nitin2k]

Hi,

I am a stock trader, and maintain a record of stock purchases and sales in a spreadsheet, like most people do.

I have a question:

Is it possible to write a formula in excel, which will (a) add brokerage commissions to the cost of shares purchased, for each transaction and (b) reduce brokerage commissions from the cost of shares sold, for each transaction? For this to happen, the formula will have to "detect" whether a particular transaction is a purchase or sale transaction.

Similarly, in DCF or other valuation methods, is it possible to write a formula which will compare the value in a particular cell, with the DCF valuation or other valuation mentioned in another cell, and then display the "call" in yet another cell - whether the stock is a buy or sell. For instance, current stock price is $100 and the DCF valuation comes to $90 only, in which case, the stock is a "Sell", since current price > DCF valuation. In case DCF > current price, stock is a "Buy".

I am not proficient in the use of excel formulae and would request assistance. Thanks.

 

[Sajan]

Hi Nitin2k,
Since I am feeling lazy (it is a Friday!) to make up some data, can you upload a sample file with data?

 

[SirJB7]

Hi, nitin2k!
I was wondering if the formula should include developer's compensation, maybe in the form of a 15% commission (as Chandoo uses to send by mail only, and by cargo ship with scale in Vladivostok and just because international donkey service are unavailable).
Regards!

@Sajan
Hi, buddy!
I have a little job for you, and it has a 10% commission included. Are you in?
Regards!
PS: I'm open-handed today. :D

 

[Sajan]

Hi SirJB7,
Thanks for thinking of me for that job, but I typically start my negotiations at 70% commission. For you, I am willing to do it for 50%, which I am sure you would agree is extremely generous. :D

By the way, not sure if you are aware of an international donkey service that was started up by some enterprising (*wait for it*) donkeys!! The only challenge is that the donkeys tend to get lost in the ocean currents, and the reliability rate is very low now.

 

[SirJB7]

@Sajan
Hi!
But that's what I firstly offered you: 70% of 15% = 10.5%, which rounded for clearness is 10%. I don't want you to settle down for a 7-8%, I appreciate your work much than that.
BTW, shhh... edit your post and delete the last part... if "that" guy reads it he'll be starting to use it immediately. And guess what? Instead of receiving out golden 64 Gb iPads before Christmas, in the best case we'd receive a 2nd-hand-almost-unused Texas Instruments TI-58 in a couple of years... at least the 59 model had magnetic cards.
http://www.ebay.com/itm/TI-Programm...pt=Vintage_Electronics_R2&hash=item20d9df4869
Regards!
PS: If we're lucky!
PS2: Do you know which is "that" guy favorite song? Guess, think a little...















... I said a little!...




















... Ok, it's Friday:
http://www.thebeatles.com/song/please-mister-postman

 

[nitin2k]

This is forum for brain-storming. If anyone can share ideas, I would appreciate. I do not think this is an "e-commerce" site.

 

[NARAYANK991]

Hi Nitin ,

You are right that this is a forum for exchanging ideas ; you are always free to ignore those who are not contributing ideas !

However , please note that this is first and foremost a forum where you can resolve problems related to Excel.

You have used some terms which may or may not be familiar to people who are proficient in Excel ; Sajan had specifically requested the upload of a sample file ; can you please do that ?

Narayan

 

[nitin2k]

Narayan,

Appreciate your response. My post was very much aimed at resolving a problem I faced. I shared my question here so that others facing a similar problem would also benefit. Attached is an example file. Your feedback would be welcome.

Nitin

 

[Sajan]

Hello Nitin,
Put the following formula in cell F3 and copy down to additional rows:
=$E3*(1+IF($C3="B",1,-1)*$L$1)

(This assumes that there are only two choices: B and not B (aka S). If there could be other options, then the formula will need to be tweaked.)


Regarding the "call" based on a comparison of valuation and market price, your formula just needed some absolute references similar to what you had for $L$1 in the first formula:
=IF($C14>$E14, $I$14, $I$15)

What should happen if the valuation = CMP? With the above formula, you would have a "sell" call in that condition.

-Sajan.

 

[nitin2k]

Appreciate your gesture, Sajan. Thanks a lot. For the stock trading part, there really are only choices B (buy) and S (sell).

As for the DCF part, I note that your formula is similar to mine, except for the $ part, which makes more sense. If at all DCF = CMP, then the call would be "hold", rather than sell. Thanks again.

 

[Sajan]

If you wish to accommodate the "hold" scenario, then you could try the following formula in cell G14
=CHOOSE(SIGN($C14-$E14)+2, "Sell", "Hold", "Buy")

 

[nitin2k]

Thanks again, Sajan.

 

[nitin2k]

Just a question, Sajan. When I copy your formula into the formula bar in my spreadsheet, the font changes from Calibri 11 to Verdana 11. How come? Because the font you have used in your post above, is Verdana? Funny, isn't it that Excel should copy not just the formula, but the font used as well?

 

[Sajan]

You are welcome! Thanks for the feedback. Glad to help.

Not sure if you copied / pasted the formula as HTML. That might explain the change in font.

 

[nitin2k]

I copied the formula from your message, and pasted it in the formula bar in the spreadsheet. Not sure that's what you mean by pasting the formula as html.

 

[nitin2k]

Sajan, I know I've bothered you enough already. But had this thought. Is there a way I could "highlight" (fill colour) the Buy and Sell rows in different colours, say orange for buy and green for sell? It would thus be visually possible to differentiate between buy and sell transactions. Could such a formula be written?