Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with cell formulas
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 a 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with cell formulas
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*(E50) 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with cell formulas
1. =if(a1="","",formula)
2. lastdate - firstdate format as General 3. & 4 Possibly some form of this: http://support.microsoft.com/default...13&Product=xlw XL: Function to Compute Interest and Growth Rate on a Single Payment -- Regards, Tom Ogilvy Joe wrote in message news:t_Xhb.5627$Pe5.124@edtnps84... 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 a 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Two formulas in on cell based on two numbers in another cell? | Excel Discussion (Misc queries) | |||
Carrying formulas over to new cell when old cell is deleted: possi | Excel Worksheet Functions | |||
How to trace a cell? Which cells use a special cell in formulas? | Excel Discussion (Misc queries) | |||
Copying formulas from cell to cell to cell to....... | Excel Discussion (Misc queries) | |||
change cell value greater than another cell value using formulas | Excel Worksheet Functions |