Tracking basis with spreadsheet, how to structure the spreadsheet?

[bamsphd]

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

 

[ziggy29]

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.

 

[bamsphd]

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.

 

[ziggy29]

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...

 

[FUEGO]

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.

 

[W2R]

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.

 

[kaneohe]

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.

 

[FUEGO]

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.

 

[kaneohe]

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.

 

[FUEGO]

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!

 

[kaneohe]

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.

 

[teejayevans]

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

 

[lucija]

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!

 

[audreyh1]

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