Need help with cell formulas

[Joe]

Can any of you EXCEL guru's help me out with couple of spreadsheet formulas
? (hopefully you can view att'd)

I'm trying to automate a sheet to track my stock option trades. I've
figured out how to have my profit/loss field come up with an accurate number
but it always shows a negative $20 for commissions even in the lines which I
do not show a trade, which messes up my sheet totals. So my questions
are:

1. How do I do a "IF" formula with the result being zero or blank if no
entry is made but also calculate the profit or loss when the trade is
entered? Currently I'm using (E5*100)*I5-(E5*100)*F5-20

2. How do I calculate a "number of days held" if date in = 08/19/03 & date
out = 09/13/03

3. What would the formula be to calculate annual % return from the
profit/loss column and the number of days held column ?

4. What would the formula be to calculate the immediate % return (not
annualized)

Again, I would like all the resulting fields to be blank or show zero,
unless trades are actually entered.

Thanks for all / any input.
Joe
(e-mail address removed)

 

[merjet]

1. How do I do a "IF" formula with the result being zero or blank if no

entry is made but also calculate the profit or loss when the trade is
entered? Currently I'm using (E5*100)*I5-(E5*100)*F5-20

Is E5 the number of contracts? If yes, use -20*(E5>0)

2. How do I calculate a "number of days held" if date in = 08/19/03 & date
out = 09/13/03

Just subtract, forcing an integer. INT(date_out - date_in).

3. What would the formula be to calculate annual % return from the
profit/loss column and the number of days held column ?

((Cost + profit/loss)/cost)^(365/days_held) -1. Format as %.

4. What would the formula be to calculate the immediate % return (not
annualized)

((Cost + profit/loss)/cost -1. Format as %.

HTH,
Merjet

 

[Tom Ogilvy]

1. =if(a1="","",formula)

2. lastdate - firstdate format as General

3. & 4

Possibly some form of this:
http://support.microsoft.com/default.aspx?scid=kb;en-us;214113&Product=xlw
XL: Function to Compute Interest and Growth Rate on a Single Payment