The TSP, Quicken and Cost Basis (kinda long)

[jimbohoward69]

I've got a question that's been stumping me for a while now. First, some background:

I've been keeping track of my TSP (as a 401k) in Quicken since I first started contributing in 2002. Since the TSP didn't have any type of "share value" until June of 2003, the only transactions I performed up until then were "Transfers In" of cash from my paycheck. Once share values were created ($10.00), I used the the cash available in my Quicken account and bought the ten dollar shares according to the percentages, funds and dollar amounts I had in place. (Are you with me?)

Since June of 2003, I've done approximately 4-5 interfund transfers and have tracked them in Quicken. I used the "Transfer Share In/Out" method as opposed to "Buy/Sell" since no buying/selling technically took place. As of today, the number of shares I have in each fund and their respective values match the tsp.gov website so I know I'm entering the info correctly. (Still with me?)

HOWEVER, when I go into the Portfolio section of Quicken, the "cost basis" of each fund is considerably lower than the amount contributed. This is the only account in Quicken where this is happening. I can understand why the cost basis for my DRIP's and IRA is higher (due to dividends and capital gains) but I have no idea why the cost basis for my TSP shows up lower than the amount contributed.

Since the TSP has no dividends or cap gains (at least they aren't shown on the statements), can one explain why the cost basis would be less than the total amount contributed? Shouldn't they be exactly the same since the transfers are within the same account? Am I missing something here?

I know that cost basis doesn't matter YET since it's a tax deferred account and I'm still contributing, but could this ever be an issue in the future?

I apologize for the length of the post but this problem has been eating at me for months. Any insight would be greatly appreciated.

Thanks!

 

[khorahan]

A couple of questions...

1. How did you account for the difference between your contributions and any earnings when the $10 values were established?

2. How do you deal with matching contributions? (This might also impact the answer to my first question.)

 

[rokid]

I use "add" for my bi-weekly contributions and "buy/sell" for IFTs.

The cost basis is used to calculate your return. Therefore, if yours is too low, the return that Quicken calculates will be too high.

 

[DSA]

I've tracked TSP in Quicken since the beginning....like 17 years now, way back to the days of DOS based Quicken 2.0 or something like that.

I originally tracked my TSP as just a savings account, and added and subtracted gains and losses and balance adjustments off the quarterly statements.

Anyways, when Quicken came out with 401k tracking a few years ago, it sure seemed like a good idea to move my account data over to a 401k investment account...Quicken even handled employer contributions automatically. I tediously moved all my transactions by hand for several years into the fund.

Boy what a mistake. I guess I just don't understand this stuff enough to do it accurately, but trying to get the dollar amount matching by buying and selling and moving shares in Quicken has been a pain in the butt, espeically since I began moving money around in the funds frequently.

I wish I could just go back to savings account model easily - the investment account business takes to much work.

Does anyone know of a easy to use guide that would explain how best to handle the TSP in quicken? TSP is just different enough from other investments to not quite fit the model Quicken allows for.

 

[tsptalk]

Welcome DSA -

I don't know the answer to that but there have been discussions on Quicken here and there. Try this link ... http://www.tsptalk.com/mb/showthread.php?t=2350&highlight=quicken

Tom

 

[JAC]

Long post, but this is a complex problem. I also have a lot of TSP data in Quicken. I started out treating TSP funds as $1 per share, which stayed constant until 6/1/2003, and treated gains as reinvested dividends, and losses as "removals." Then, on 6/1/2003, I entered a 10:1 stock split. Since then things are "normal" from an investment performance/tracking/analysis point of view.

By the way, in order to get the net worth and total picture correct, you ought set up your paycheck so that your and the employer contribs transfer to the TSP 401 k acct. If you treat them as "adds" the net worth and portfolio performance analysis will be messed up.

Due to the pre-6/1/2003 $1/share scheme, though, the portfolio and security performance graphs were still messed up, if you include prior dates.

The solution seems to be to extrapolate backwards in time from 6/1/2003. In effect, if you know the "security" was worth $10 on that date, how much was it worth on every transaction date prior to that, such that on 6/1/2003 you ended up with X shares worth Y amt? If you know how much cash you contributed for every prior month (or all prior transactions), and you know the total fund value at the end of all prior months from your TSP statements, you can figure out the share price and share amts for every transaction.

This is completely fiction, of course, but since none of the TSP funds are actively traded, treating them like securities will never run foul of reality anyway, and this method has the advantage that fund and portfolio performance over time are reasonably accurate.

So I tried this in excel for the C Fund. I needed my and my employers contribs, their dates, and my TSP statements of fund total value. You enter the dates, the contribs, a blank column for shares, cumulative shares, price per share, and acct value (which you know from statements). Then you enter some formulas starting from the values on 6/1/2003, and drag upwards, and voila! The C fund traded for $2.74 on Jan 1, 1991, and went to an all time high of about $15.13 in 2000, with values for all pay dates and month-ends in between.

If I made no mistakes, this produces a share price of $10 on 6/1/2003. These results ought to apply to everyone's C Fund, not just mine, right? Now it's just in excel, but it won't be that hard to either enter it or import it into quicken. And now the biggest investment acct I've got will act properly (I hope).
Jonathan

 

[EW_ret]

JAC,

Boy, that must have been a lot of work if you modified all you transactions. I have not had major problems using Quicken to track my TSP portfolio, and I too have tracked my TSP account in Quicken since version 1 in 1991.

I used the $1/share NAV accounting method and added/subtracted shares monthly as long term capital gains (C Fund), or sell shares for losses (C and F Fund), or reinvested dividends (F and G funds) using my statements. I created a security for each TSP fund using the tracker symbol $ which means cash. Then on June 1, 2003 I sold all shares in each account, and bought back at $10/share, with the shares being 0.1 times original amount. Afterwards, I converted each TSP account over to a 401k when I purchased Quicken 2003. I now use Quicken 2006.

I believe it was essential to set up a separate account for each TSP fund, and I still have a separate 401k account for each TSP fund. I also created a security name for each TSP fund (C Fund, G Fund, etc) and used ticker symbol $. From the very beginning I determined that in order to track the performance of each fund accurately, I needed a separate account for each TSP fund and used a $1/share NAV. The reinvested long-term capital gains, and dividends, each month increased my share balance and provided performance tracking.

I was in the CSRS my entire career so I did not have to account for employer contributions. I have set up a 401K account for a friend who is in FERS. This is a single 401k account, which combines employee and employer contributions for each pay period. I created a security for each fund manually and used imaginary ticker symbols (i.e TSPGX. TSPIX, TSPSX, etc). We have to manually enter each fund share price each week, or when funds are transferred within the 401k account between funds.

 

[JAC]

It hasn't been a lot of work yet, as clikNdrag in excel did the work...but I haven't got all that back into quicken. As did you, I've been tracking all the contribs right along, so the dates, amounts, and balances were easily exported to excel.
If I understand your method correctly, what happened to the cash that accumulated in each account when, e.g. the C fund lost money? That screws things up, no? Ditto, long term capital gains? One could enter dummy transactions to void the cash...
Jonathan

 

[JAC]

P.S.
I also thought it was fun to backstrapolate the fund price...2.78 in 1991!
J

 

[EW_ret]

There was no cash balance accumulated in the accounts since I would sell and transfer the cash from one account, and then buy shares from the transferred cash in another TSP account. Contributions were transfers of cash from my checking account to each TSP account, and then buy shares from the transferred cash. The month end gains/losses were characterized as reinvested long-term capital gain, or reinvest divided. If a loss occurred that month I would sell shares and transfer the cash out of the account. The only day I had a cash balance was May 31, 2003 when I sold all shares in each TSP account. The following day June 1, 2003 I bought the cash balance in each account at $10/share.

All transactions between accounts were sell and transfer, followed by buy from transferred cash. To get accurate performance graphs for your TSP funds you must have an accurate cost basis for each fund. Adding or subtracting shares will not yield an accurate cost basis. Always buy or buy and transfer, sell or sell and transfer. The transfers only occur if you have set up a separate account for each TSP fund. I would not recommend this for new users using Quicken, which track 401k accounts. For a single 401k account, each sell of one fund must be paired with a buy of another fund for the same cash amount. Otherwise, cash will accumulate in the account if the sell and buys amounts do not match.

Quicken 2006 has made my life difficult with transferring money from a single mutual fund account. I had to answer NO to "single mutual fund?" query on the account Summary Page for the account to appear in the transfer drop-down menu. Quicken 2006 requires more work to get it to work the way I am accustomed. Quicken 2003 was easier to use for investments transactions. Quicken 2006 requires more work to get it to work the way I am accustomed. Quicken 2003 was easier to use for investments transactions.

 

[JAC]

I agree about buying and selling vs. adding/removing. In Q5 "Cash transferred out of account" requires an account to transfer it into. How did you avoid that? In other words, it's impossible to lose money. Would it were true.

In the interim, I discover it is not so simple as of v5 (OFX v QIF) to import transactions into quicken. Nevertheless, the closest to reality we could get is if TSP had treated the thing as an equity from the get/go, like the indices the various funds tracked.
Jonathan