plus and minus

[JHI]

I created a spread sheet to track capital gains/losses. If my cost basis is
say A1 and sold is B1 what formula would work for the capital gains cell
C1. Keep in mind it's a plus or minus.

Thanks.

JI

 

[bony_tony]

err, wouldn't it just be =B1-A1 .......?

Tony

 

[KC Rippstein]

Well, there's going to be a bit more to a simple B1-A1...you have to track
net short-term gains/losses versus net long-term gains/losses and be sure
"net" means you have deducted broker fees and other costs to administer the
transaction(s). Then you can calc your overall net gain/loss. So I'm not
sure I understand fully what you have set up, as sold_value less cost_basis
is not appropriate for your need.

Really, column A should have a list of acquired dates for property or
acquired date +1 day for securities, column B should have cost of each,
column C should have sold value, column D should have short term
gains/losses line by line with a subtotal at the end, column E should have
long term gains/losses line by line with a subtotal at the end, and then
your hypothetical "C1" should actually be in column F (let's say cell F20)
and would be subtotalD + subtotalE, or D20 + E20. You can put 12/31/2006 in
cell F1 to help auto-calculate short term versus long term.

You should also format D2:F20 to the "Accounting" number format.

Naturally, you still need to do more calculating after this to figure out
either (a) how much net loss can be deducted this year and how much to carry
over for next year or (b) how much net gain to report on your tax filing.
Hope this helps.

 

[KC Rippstein]

Oh, don't forget the broker and/or transaction fees. You may want to put
"fees" in column D and shift everything else in my example below to the
right one column (short-term in column E, long-term in column F, and
year-end date with overall net and other calculations in column G).

 

[KC Rippstein]

Aye, aye, aye. Okay, I think using year-end date is wrong, too. You need a
sold date column. I should think before I type!

 

[JHI]

How does this look?


A Security
B Date bought
C Buy $
D Buy Comm.
E Date Sold
F Sell $
G Sell Comm
H Sell "Basis"
I Cost Basis>>>>>>>>>>>>>>>>>>>f2-g2
J Cap Gains/Loss>>>>>>>>>>>>>>>h2-i2
K Long/Short

 

[KC Rippstein]

Well, I'm not familiar with the Sell Basis" or Cost Basis, but I'm sure you
know what you're doing from here.

Just don't forget that securities are date_sold - date_bought +1 (and that
+1 may be the next market day or the next calendar day, I'm not sure, but
your tax advisor/accountant will know). You do not seem to be tracking net
Short separately from net Long, so make sure you have a sumif formula that
sums all Short =SUMIF($K$2:$K$20,"Short",$J$2:$J$20), another one that sums
all Long =SUMIF($K$2:$K$20,"Long",$J$2:$J$20), then one that sums those two
answers together. Your accountant must have those subtotals.

Good luck!