Stock Break Even Work Sheet.

This is something I worked out a while back.  Oddly I couldn't find an easy formula to plug in for excel, so I had to create one.  Recently somebody asked me for it, so I wanted to post it here so I could reference it.  The reason for creating this is I was trying to buy some ford stock at a 1.60 a share and wanted to know what the break even price was if I had to bale out of it.  Of course I didn't bail out and today it's a $9 something a share, "Quality is job 1"; Ford made my year !

Basically I needed a formula that would help me calculate how much a stock must rise in price to cover my commissions and taxes and break even.

There is some really good reading here in this book, Page 116, "Calculating the Stock Break Even Sales Price", my formula is adapted from that, good book to read.

http://books.google.com/books?id=YmZV-Jxt28kC&pg=PA114&lpg=PA114&dq=break+even+formula+for+selling+stocks&source=bl&ots=dR84EvLZGH&sig=dL-LdOG4oEptNx1wrrW9xaisjUY&hl=en&ei=NUGxSeHSAo3Btgequ-TDBw&sa=X&oi=book_result&resnum=1&ct=result#v=onepage&q=&f=false

Technincally my formula is not 100% accurate as depending on your particular tax situation the rate can can change between 15% or 28% and there is some "cost basis" versus "individual lots".  The forumula is just meant to be used as a guide, your actual mileage may vary.

I've updloaded the excel spreadhseet here.
Stock_Break_Even_Price.xls (22.5 KB)

Here is the formula, surprisingly my Algebra was terrible and it took me awhile to solve this.

Variable Description
 
N # of Shares
B  Buy Price per Share
C1  Buy Commission
C2 Sell Commission
P  Purchase Cost (Cash out)
X Sell Price Per Share
T Total Cash In
TR  Tax Rate
TX Total Taxes
G Gain
 Calculate Gain

Example Ford Stock 1.60 per share, 100 shares, 10 commission for both buy and sell
 
Description   Formulas                          Example
Cost            (N*B) + C1 = P                 (100 * 1.6) + 10 = 170
Gross           (N*X) - C2 = T                 (100 * X) - 10 = T
Taxes           ((N*X) - (N*B))*TR=TX     (( 100 * X) - (100 * 1.6)) * .15 = TX
Break Even    T-P-TX=0                        ((100 * X) - 10) - 170 - ((( 100 * X ) - (100 * 1.6)) * .15) = 0
                                                        ((100 * X) - 10) - ((( 100 * X ) - (100 * 1.6)) * .15) = 170
 How to solve for x ?                            (100X - 10) - ((100X  - 160) * .15) = 170
                                                        (100X - 10) - (15X  + 24) = 170
                                                        100X - 10 - 15X  + 24 = 170
                                                        100X  - 15X = 156
                                                        85X = 156
                                                        X=1.835294118

Migrating from one cluster technology to another or even with-in the same technology is fairly easy.  Recently I'm run into an issue where we need to migrate a SQL Server Instance from a HP Polyserve Cluster to a Microsoft Cluster.

There were two issues I found in setting this up:

• Installing SQL Server on a Microsoft Cluster requires a virtual name
• Keeping the exact same Port Number

The reason for the above two issues was to keep the down time to an absolute minimum and ensure there were no changes necessary to the application or infrastructure (firewalls).

Fortunately both HP Polyserve and Microsoft Clustering use virtual names, this is what makes this possible.

I found the following two links helpful:

How to: Rename a SQL Server 2005 Virtual Server
How to change the network IP addresses of SQL Server failover cluster instances

The key to making this happen is to install SQL Server using a temporary Virtual name and IP Address and ensure to use the EXACT Same instance name.  Instance names can not be changed with SQL Server 2005 (or at least it's not supported to change them).  Changing the port number is pretty standard stuff.

Now you can pre-test your migration of databases and user logins, and load test the new hardware. 

At the designated change time we performed the following.

• Take the Microsoft Cluster Off line
• Take the Instance on HP Polyserve and delete the Instance and virtual name (binaries and data files will be kept as a backout plan)
• Using the SQL Server Configuration editor, change the IP address on all nodes in the Microsoft Cluster:
• Using the Cluster Administrator change the SQL Server IP Address
• Using the Cluster Administrator change the SQL Server network name
• Bring the Cluster on-line
• Test

The one issue we ran into was with logical networks and VLANS, I don't have a complete understanding of network topology, but only certain logical networks with-in our environment can host different ranges of IP Addresses.  Initiall we built the new cluster on a logical network that was unable to host the existing virtual name and the switch failed, ensure to talk to your network, windows and DNS engineers about exactly what your wanting to do so they can build things properly the first time, as they don't like switching and changing things twice any more than DBA's do !

SQL Server security, logins, auditing and reporting still not where it needs to be ?  Maybe someone can help me with ideas, if so email, me or leave me some comments.

I've been through a lot of requirements and implementing everything the information security group wants is like Macgyver with duct tape.  Currently this is for SQL 2005, but I'd like to know how to implement it for SQL 2008 as well.

Currently I'm meeting the following Information Security requirements:

• Limit Logins by specific IP or IP Ranges in combination with Time of Day [login trigger]
• Limit Logins by Time of Day [login trigger]
• Disconnect logins that are connected past there authorized time. [job running every 5 minutes]
• Keep Record of All Logins (success or Failures) [daily job to scrape error log]
• Keep Counts of Login Failures [daily job to scrape error log]
• Keep track of Date and Time Password was changed (sql authenticated only) [Tracing]
• Provide reporting and alerting that shows counts of failure, by month, by id etc. [Reporting Services and SQL Agent]
• Limit number of concurrent connections by ID and by Global Count [login trigger], so the max number of connections for an instance may be 1000, and the max for a user maybe 5, and the max for the application id may be 300.

Now they want me to selectively enforce password length and expiration based on the following requirements for SQL Server Authenticated:

• Service accounts  28 characters, expire yearly  (accounts that are not, or should not be used for general sign on’s and limited by what machine(s) they can login from)
• Admin accounts 15 characters, expire yearly (accounts that are used by DBA’s)
• User accounts 8 characters, expire every 90 days (normal user accounts) [this is easy as our Active Directory can enforce this]

It just seems that meeting all these requirements in SQL becomes very difficult to administer; we're running a combination of:

• Dedicated database for reporting, configuration and capture of statistics
• Login Trigger (difficult to administer and one little issue everyone is locked out)
• Tracing (to capture when a user changes their password)
• SQL Agent jobs (to clean up, scrape and roll over reporting tables)
• Reporting Services (for reporting)
• Enabling the DAC
• Enabling Login Auditing
• Enabling CCC (Common Criteria Compliance)

My counter-parts in Oracle seem to have an easier time meeting all these requirements, with less overhead, easier to administer and keep running, and less danger of locking everyone out.

SQL Server continues to frustrate me with this, anyone have suggestions or an easier time with SQL Server 2005 or 2008?

All these things should be available out of the box, duct taping all that stuff together in sql server is absolutely brutal, documenting and training someone on it is even worse.

It does provide good job security, as let the suits out source the DBA Staff after implementing all of these requirements and they'd sure have a tough time finding someone to run this stuff easily!

The "Suits" and "Information Security"!