Join Early Retirement Today
Reply
 
Thread Tools Search this Thread Display Modes
Tracking basis with spreadsheet, how to structure the spreadsheet?
Old 07-31-2009, 12:21 PM   #1
Recycles dryer sheets
bamsphd's Avatar
 
Join Date: Nov 2005
Posts: 337
Tracking basis with spreadsheet, how to structure the spreadsheet?

After many years of using Quicken, I think I want to switch to a spreadsheet for tracking my basis in mutual funds, TIPS, and stocks. Right now I'm trying to decide what columns and pages to create, and wondered what systems others using a spreadsheet use, and why.

It seems obvious that I need basis purchase date, basis purchase price, and number of units/shares purchased. I'll probably also want a column that tells me where to find the original statement in case the IRS comes calling. However, I've been investing long enough to see stock splits, reverse splits, conversions to and from Admiral shares, spin-offs, mergers, acquisitions, not to mention the inflation adjustments on TIPS. I have not decided yet how I want to represent those more complex transactions.

I'm also still debating how to track sales. Basically, should the spreadsheet only show my current open positions, or should it also retain information on closed positions.

Does anyone who has been doing this for awhile want to share any wisdom or pointers?

Thanks,
Bill
__________________

__________________
bamsphd is offline   Reply With Quote
Join the #1 Early Retirement and Financial Independence Forum Today - It's Totally Free!

Are you planning to be financially independent as early as possible so you can live life on your own terms? Discuss successful investing strategies, asset allocation models, tax strategies and other related topics in our online forum community. Our members range from young folks just starting their journey to financial independence, military retirees and even multimillionaires. No matter where you fit in you'll find that Early-Retirement.org is a great community to join. Best of all it's totally FREE!

You are currently viewing our boards as a guest so you have limited access to our community. Please take the time to register and you will gain a lot of great new features including; the ability to participate in discussions, network with our members, see fewer ads, upload photographs, create a retirement blog, send private messages and so much, much more!

Old 07-31-2009, 12:27 PM   #2
Moderator
ziggy29's Avatar
 
Join Date: Oct 2005
Location: Texas
Posts: 15,612
Don't forget that taxable dividends and capital gains are added to your cost basis if reinvested.

If you buy $5000 in a mutual fund and pay taxes on $500 in reinvested dividends and capital gains, your new cost basis is $5500.

If you don't account for reinvested (and taxed) capital gains and dividends when you sell, you're paying too much tax.
__________________

__________________
"Hey, for every ten dollars, that's another hour that I have to be in the work place. That's an hour of my life. And my life is a very finite thing. I have only 'x' number of hours left before I'm dead. So how do I want to use these hours of my life? Do I want to use them just spending it on more crap and more stuff, or do I want to start getting a handle on it and using my life more intelligently?" -- Joe Dominguez (1938 - 1997)

RIP to Reemy, my avatar dog (2003 - 9/16/2017)
ziggy29 is offline   Reply With Quote
Old 07-31-2009, 12:32 PM   #3
Recycles dryer sheets
bamsphd's Avatar
 
Join Date: Nov 2005
Posts: 337
Quote:
Originally Posted by ziggy29 View Post
Don't forget that taxable dividends and capital gains are added to your cost basis if reinvested.
Thanks, I should have mentioned that I use "specific identification" of all the assets I'll track using a spreadsheet. So reinvested dividends will get recorded as a new purchase with a unique purchase date, price, and quantity.
__________________
bamsphd is offline   Reply With Quote
Old 07-31-2009, 12:33 PM   #4
Moderator
ziggy29's Avatar
 
Join Date: Oct 2005
Location: Texas
Posts: 15,612
Quote:
Originally Posted by bamsphd View Post
Thanks, I should have mentioned that I use "specific identification" of all the assets I'll track using a spreadsheet. So reinvested dividends will get recorded as a new purchase with a unique purchase date, price, and quantity.
Good. Just wanted to make sure you accounted for them in the total cost basis of your position. No need to pay more tax than you owe...
__________________
"Hey, for every ten dollars, that's another hour that I have to be in the work place. That's an hour of my life. And my life is a very finite thing. I have only 'x' number of hours left before I'm dead. So how do I want to use these hours of my life? Do I want to use them just spending it on more crap and more stuff, or do I want to start getting a handle on it and using my life more intelligently?" -- Joe Dominguez (1938 - 1997)

RIP to Reemy, my avatar dog (2003 - 9/16/2017)
ziggy29 is offline   Reply With Quote
Old 07-31-2009, 02:07 PM   #5
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
 
Join Date: Nov 2007
Posts: 7,527
I organize mine by having the ticker symbols in alphabetical order. Each ticker has a series of entries that consist of one transaction per row. At the end of the entries for a particular ticker, there is a summary of the total tax basis, including the total number of shares.

The columns contain the typical info - IIRC, purchase date, purchase price per share, # shares, purchase amount, transaction type (purchase, sale, div reinvest, cap gain dist reinvest, etc), sale date, sale price, description, comments.

Basically each row contains one "lot" in tax parlance. The occasional weird event like splits, buyouts, mergers, etc can be notated in the comments column or by inserting a comment in the cell (the kind where you mouse over and it pops up, and it leaves a red triangle in the corner of the cell).

So far, so good. I usually update the tax basis spreadsheet once a year either during the Christmas break or around new years once the year is over. Usually doesn't take too long, maybe a couple of hours if there is only one or two "weird events" like mergers, share conversions, etc.

Edit to add: Here's a peek at what my spreadsheet looks like with some hypothetical data.
__________________
FUEGO is offline   Reply With Quote
Old 07-31-2009, 03:24 PM   #6
Administrator
W2R's Avatar
 
Join Date: Jan 2007
Location: New Orleans
Posts: 38,890
Fuego, thanks for sharing your spreadsheet! I have added a new worksheet inspired by yours (but customized to my needs and preferences) to my Excel retirement planning workbook. I have been recording all of this information since the beginning, but on my original spreadsheet I included daily share price changes and consequent changes in daily value of each fund. The transactions are beginning to be hard to find while buried in so much information, and this new sheet should help.
__________________
Already we are boldly launched upon the deep; but soon we shall be lost in its unshored, harbourless immensities.

- - H. Melville, 1851
W2R is offline   Reply With Quote
Old 07-31-2009, 03:57 PM   #7
Thinks s/he gets paid by the post
 
Join Date: Jan 2006
Posts: 2,930
One thing that I've found very useful as a internal self-check is a column for total shares owned and also a column that multiplies your entries for #shares and NAV. The former, of course, is supposed to be the sum of previous shares owned and the new purchased shares. The latter is supposed to be the new purchase price. It is very easy to make data entry errors either by misreading or mis-typing. Having the extra columns and comparing them to what they're supposed to be (either manually or having another column for the difference which should be 0.00) will immediately flag errors so you can correct on the spot.

I like to see the old entries even if they have sold so consider highlighting them yellow.
Also since you do specific shares, you may want to have a column to record which lots
go w/ which sales.
__________________
kaneohe is offline   Reply With Quote
Old 07-31-2009, 04:09 PM   #8
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
 
Join Date: Nov 2007
Posts: 7,527
Quote:
Originally Posted by kaneohe View Post
One thing that I've found very useful as a internal self-check is a column for total shares owned and also a column that multiplies your entries for #shares and NAV. The former, of course, is supposed to be the sum of previous shares owned and the new purchased shares. The latter is supposed to be the new purchase price. It is very easy to make data entry errors either by misreading or mis-typing. Having the extra columns and comparing them to what they're supposed to be (either manually or having another column for the difference which should be 0.00) will immediately flag errors so you can correct on the spot.
I have a "cumulative shares" column that keeps a running tally of how many shares I have so that I can figure out where I'm making a data entry error if the totals don't match. Gotta make sure your entries match the total that you own and this serves as a good check.
__________________
FUEGO is offline   Reply With Quote
Old 07-31-2009, 05:04 PM   #9
Thinks s/he gets paid by the post
 
Join Date: Jan 2006
Posts: 2,930
Yeah, I discovered that after I sent that response . Should have looked at spreadsheet first. That's probably the most serious of the possible errors if you're doing specific shares sells. Still possible tho if # shares, NAV entered correctly, to enter the purchase price incorrectly and miss it e.g. if you change $716.63 to 761.63. Having the check
of #shares * NAV and subtracting the purchase price would catch the inconsistency
easily as any non-zero result would raise a flag.
__________________
kaneohe is offline   Reply With Quote
Old 07-31-2009, 05:39 PM   #10
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
 
Join Date: Nov 2007
Posts: 7,527
Quote:
Originally Posted by kaneohe View Post
Yeah, I discovered that after I sent that response . Should have looked at spreadsheet first. That's probably the most serious of the possible errors if you're doing specific shares sells. Still possible tho if # shares, NAV entered correctly, to enter the purchase price incorrectly and miss it e.g. if you change $716.63 to 761.63. Having the check
of #shares * NAV and subtracting the purchase price would catch the inconsistency
easily as any non-zero result would raise a flag.
That is sort of what I do. I enter # sh and purchase price per share, and the tax cost basis column multiplies these two numbers to give the total amount for the purchase of that lot. Then I visually check against the vanguard/fidelity statement to make sure my spreadsheet = the statement for that transaction. Belts and suspenders!
__________________
FUEGO is offline   Reply With Quote
Old 07-31-2009, 07:02 PM   #11
Thinks s/he gets paid by the post
 
Join Date: Jan 2006
Posts: 2,930
I guess I'm saying that I, at least, need 2 belts and 2 suspenders then. If I didn't make any mistakes doing the primary entering of #sh, NAV, $$$ (which I double/triple check
at least in my mind), then I wouldn't need the other redundancy. In practice, I find that
it's not perfect that way.....maybe 95-98%...but not 100%. If I force myself to do
those secondary checks.....and entering all the numbers in the spreadsheet instead of just visually comparing them.......I think I get much closer to 100%. I think the idea is that it's hard to lie the same way twice but easy if it's the truth. That's why the police separate out the suspects and get different versions. If they agree, likely the
truth but if not......... You might only need 1 belt and suspenders.
__________________
kaneohe is offline   Reply With Quote
Old 08-01-2009, 03:42 PM   #12
Thinks s/he gets paid by the post
teejayevans's Avatar
 
Join Date: Sep 2006
Posts: 1,221
Quote:
Originally Posted by kaneohe View Post
If I didn't make any mistakes doing the primary entering of #sh, NAV, $$$ (which I double/triple check
at least in my mind),
Don't know which spreadsheet program you are using but OpenOffice
has an extension that will download stock/mutual fund prices, so you
don't have to manually update fund prices everytime you want to
check your portfolio.
TJ
__________________
teejayevans is offline   Reply With Quote
Old 08-03-2009, 09:47 AM   #13
Recycles dryer sheets
 
Join Date: Dec 2006
Posts: 191
Quote:
Originally Posted by teejayevans View Post
Don't know which spreadsheet program you are using but OpenOffice
has an extension that will download stock/mutual fund prices, so you
don't have to manually update fund prices everytime you want to
check your portfolio.
TJ
I use OpenOffice, but did not think auto quotes are supported. Could you share a bit more on this extension?

Edit - never mind, I got it! Thanks for mentioning this!
__________________
lucija is offline   Reply With Quote
Old 08-03-2009, 10:31 AM   #14
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
audreyh1's Avatar
 
Join Date: Jan 2006
Location: Rio Grande Valley
Posts: 16,471
I let Fidelity track my basis! They use the average cost basis for the mutual funds which is fine by me. Stocks are listed by lot and I can identify the lot when I sell. I gave up on using a spreadsheet years ago. I have too many funds and it's just too darn complex!

Gosh, just tracking the one company stock we had (that we worked for), and tracking all the certificates and splits, etc. What a headache! The spreadsheet seemed to grow exponentially. When we finally surrendered the certificates to Fidelity, gave them the basis and they started tracking it from there - what a relief!!!

I'm also not worried about the IRS arguing with the Fidelity accounting. Nevertheless I do have the annual statement from each year in my bank safety deposit box which shows all asset purchases and sales. Just in case!

Audrey
__________________

__________________
audreyh1 is offline   Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Portfolio tracking spreadsheet justin FIRE and Money 8 06-28-2010 05:01 AM
Tracking basis in IRAs - when is it important? Lusitan FIRE and Money 11 01-26-2009 10:40 PM
Budget Spreadsheet Rustic23 FIRE and Money 5 08-25-2007 11:53 AM
Another spreadsheet bug? Cadence FIRECalc support 0 07-18-2007 07:48 AM
spreadsheet nnkrealtor Other topics 4 09-18-2005 04:01 PM

 

 
All times are GMT -6. The time now is 08:34 AM.
 
Powered by vBulletin® Version 3.8.8 Beta 1
Copyright ©2000 - 2017, vBulletin Solutions, Inc.