Spreadsheet TSP tracking details

[RevSnow]

Hello all.

This looks like a place to meet up with others who track their TSP accounts with spreadsheets. I'm trying to get started doing that, and I'm running into some questions about details that I imagine others have also confronted and answered. If I'm in the wrong place for that sort of exchange, I hope someone will let me know.

To get things started, here's the first question I have. I'll use a simplified example to illustrate. Say I have requested for my TSP contributions to be allocated with 50% to the G Fund and 50% to the C Fund. Then say that for one pay period, the automatic agency match amount is $20.01. It appears that each Fund deposit from each source can only be made in whole cents, so one of the funds is going to have to get the extra penny. The split will be $10.00 into one Fund and $10.01 into the other. My question is how does the TSP accounting system make the choice where the extra penny goes? What's the algorithm?

Clearly I don't really care about a penny difference for its money values. I'm just looking to get my spreadsheet to match the actual TSP accounting in full detail.

Thanks for reading and, I hope, answering.

 

[burrocrat]

check out this thread for some discussion on three different spreadsheets people use here.

http://www.tsptalk.com/mb/showthread.php?t=7901&page=6

also nasa1974's account talk thread has some more details.

nasa, scout, and tsptalk all have spreadsheets available that seem to handle tracking pretty accurately over time, sometimes up or down $0.01 or 0.01% but it evens out in the end from what i've seen the the month i've been playing with them.

 

[Scout333]

Hello Rev, This spreadsheet might work for you since you receive matching amounts. If you receive matching amounts wouldn't the amount allocated include the employee portion? i.e. 20.01 * 2= 40.02 to be allocated. I don't receive matching amounts so I haven't actually seen how this works. Should balance out the next pay period though. Good luck!

"Hello Fellow TSP'rs, The new version of the Scout TSP Contribution spreadsheet is out. Tom was kind enough to post it in the Utilities section. It is very similar to the 2009 version. I did add a couple of very simple return on investment calculators. See the TSP Return and Tips tabs for info. FERS employees receiving matching contributions can input the employee contribution amounts each paydate and the calculator gives you return on investment %s including the matching contributions.

1-5-2010 Version: http://www.tsptalk.com/utilities/Scouts_Spreadsheet.xls

Enjoy the spreadsheet!"

 

[tsptalk]

Welcome RevSnow. TSP share prices and balances are calculated to the 100th of a penny.

I would think that means $20.01 would be split between 2 funds as $10.0050 in the G-fund, and $10.0050 in the C-fund, in your example.

 

[RevSnow]

Thank you for the replies.

TSP fund share prices and number of shares held are both kept to four decimal places, true. It is because of that precision that I can confirm without doubt that the contribution to each fund from each FERS source is made strictly in whole cents. In my example, one fund or the other is going to get the extra penny. The unknown is how TSP accounting systems make that choice.

For FERS participants like me, the TSP accountants keep track of all three sources of contributions separately -- the funds I contribute directly, the 1% automatic matching funds, and the additional matching funds. Each one of those sources contributes an amount in whole cents and purchases a number of shares rounded to the nearest .0001 of a share. There's an example in http://www.tsp.gov/forms/oc03-11.pdf on how this process of separate purchases and rounding before summing can have an impact on the precise number of shares purchased.

Thank you for the pointers to some spreadsheets others have already developed. From the descriptions, it appears that the nasa1974 spreadsheet would be the best one for me to use/modify/learn from. Is it actually available? I can't yet find my way around this web site well enough to locate it.

 

[nnuut]

Welcome to the Message Board RevSnow
Best of luck
Norman

 

[Scout333]

From the descriptions, it appears that the nasa1974 spreadsheet would be the best one for me to use/modify/learn from. Is it actually available? I can't yet find my way around this web site well enough to locate it.

You can PM NASA1974 directly and ask for it. I believe it is also discussed in his account NASA1974AccountTalk page 79. Good luck.