Excel Worksheet vs TSP return ???

Sort by newest Sort by date
B

BigLair

First Allocation
Reaction score
0
I've been tracking my TSP allocation changes in the TSP Tracker worksheet and it is showing a 7.34% return. However, TSP shows that I'm currently at -1.9%. I've gone through at least 3 times and verified every transaction and that the spreadsheet is calculating correctly and I don't know where I've gone wrong.

Could TSP have made a mistake sometime this year? In my 3rd Quarter statement, I show losing over $3K in my TSP on a balance of only $41K

The excel worksheet shows a 1.11% positive for that same time period.

And postulating would be welcome. I'm confused for sure! :eek:

Here's what I had during the 3rd Quarter:

[TABLE="width: 1101"]
[TR]
[TD]13-May-15[/TD]
[TD]21-Aug-15[/TD]
[TD]14.8027[/TD]
[TD]17.0259[/TD]
[TD]26.3641[/TD]
[TD]35.7779[/TD]
[TD]24.4640[/TD]
[TD]34%[/TD]
[TD]7%[/TD]
[TD]25%[/TD]
[TD]14%[/TD]
[TD]20%[/TD]
[TD]0.5844%[/TD]
[TD]0.89%[/TD]
[TD]-5.51%[/TD]
[TD]-5.79%[/TD]
[TD]-8.57%[/TD]
[TD]-3.64%[/TD]
[TD="align: right"]-1.30%[/TD]
[/TR]
[TR]
[TD]24-Aug-15[/TD]
[TD]25-Aug-15[/TD]
[TD]14.8061[/TD]
[TD]16.9476[/TD]
[TD]24.9844[/TD]
[TD]34.1023[/TD]
[TD]23.9315[/TD]
[TD]100%[/TD]
[TD]0%[/TD]
[TD]0%[/TD]
[TD]0%[/TD]
[TD]0%[/TD]
[TD]0.0230%[/TD]
[TD]-0.46%[/TD]
[TD]-5.23%[/TD]
[TD]-4.68%[/TD]
[TD]-2.18%[/TD]
[TD]0.02%[/TD]
[TD="align: right"]-1.28%[/TD]
[/TR]
[TR]
[TD]26-Aug-15[/TD]
[TD]25-Sep-15[/TD]
[TD]14.8328[/TD]
[TD]16.9751[/TD]
[TD]25.8827[/TD]
[TD]34.7928[/TD]
[TD]23.3555[/TD]
[TD]0%[/TD]
[TD]0%[/TD]
[TD]25%[/TD]
[TD]75%[/TD]
[TD]0%[/TD]
[TD]0.1803%[/TD]
[TD]0.16%[/TD]
[TD]3.60%[/TD]
[TD]2.02%[/TD]
[TD]-2.41%[/TD]
[TD]2.42%[/TD]
[TD="align: right"]1.11%[/TD]
[/TR]
[TR]
[TD]28-Sep-15[/TD]
[TD]1-Oct-15[/TD]
[TD]14.8380[/TD]
[TD]17.0467[/TD]
[TD]25.7953[/TD]
[TD]34.0993[/TD]
[TD]23.3202[/TD]
[TD]100%[/TD]
[TD]0%[/TD]
[TD]0%[/TD]
[TD]0%[/TD]
[TD]0%[/TD]
[TD]0.0351%[/TD]
[TD]0.42%[/TD]
[TD]-0.34%[/TD]
[TD]-1.99%[/TD]
[TD]-0.15%[/TD]
[TD]0.04%[/TD]
[TD="align: right"]1.14%[/TD]
[/TR]
[/TABLE]
 
First, make sure that the 7.34% is based upon the same time frame that your spreadsheet is using. :)

BigLair said:
I've been tracking my TSP allocation changes in the TSP Tracker worksheet and it is showing a 7.34% return. However, TSP shows that I'm currently at -1.9%. I've gone through at least 3 times and verified every transaction and that the spreadsheet is calculating correctly and I don't know where I've gone wrong.

Could TSP have made a mistake sometime this year? In my 3rd Quarter statement, I show losing over $3K in my TSP on a balance of only $41K

The excel worksheet shows a 1.11% positive for that same time period.

And postulating would be welcome. I'm confused for sure! :eek:

Here's what I had during the 3rd Quarter:

[TABLE="width: 1101"]
[TR]
[TD]13-May-15[/TD]
[TD]21-Aug-15[/TD]
[TD]14.8027[/TD]
[TD]17.0259[/TD]
[TD]26.3641[/TD]
[TD]35.7779[/TD]
[TD]24.4640[/TD]
[TD]34%[/TD]
[TD]7%[/TD]
[TD]25%[/TD]
[TD]14%[/TD]
[TD]20%[/TD]
[TD]0.5844%[/TD]
[TD]0.89%[/TD]
[TD]-5.51%[/TD]
[TD]-5.79%[/TD]
[TD]-8.57%[/TD]
[TD]-3.64%[/TD]
[TD="align: right"]-1.30%[/TD]
[/TR]
[TR]
[TD]24-Aug-15[/TD]
[TD]25-Aug-15[/TD]
[TD]14.8061[/TD]
[TD]16.9476[/TD]
[TD]24.9844[/TD]
[TD]34.1023[/TD]
[TD]23.9315[/TD]
[TD]100%[/TD]
[TD]0%[/TD]
[TD]0%[/TD]
[TD]0%[/TD]
[TD]0%[/TD]
[TD]0.0230%[/TD]
[TD]-0.46%[/TD]
[TD]-5.23%[/TD]
[TD]-4.68%[/TD]
[TD]-2.18%[/TD]
[TD]0.02%[/TD]
[TD="align: right"]-1.28%[/TD]
[/TR]
[TR]
[TD]26-Aug-15[/TD]
[TD]25-Sep-15[/TD]
[TD]14.8328[/TD]
[TD]16.9751[/TD]
[TD]25.8827[/TD]
[TD]34.7928[/TD]
[TD]23.3555[/TD]
[TD]0%[/TD]
[TD]0%[/TD]
[TD]25%[/TD]
[TD]75%[/TD]
[TD]0%[/TD]
[TD]0.1803%[/TD]
[TD]0.16%[/TD]
[TD]3.60%[/TD]
[TD]2.02%[/TD]
[TD]-2.41%[/TD]
[TD]2.42%[/TD]
[TD="align: right"]1.11%[/TD]
[/TR]
[TR]
[TD]28-Sep-15[/TD]
[TD]1-Oct-15[/TD]
[TD]14.8380[/TD]
[TD]17.0467[/TD]
[TD]25.7953[/TD]
[TD]34.0993[/TD]
[TD]23.3202[/TD]
[TD]100%[/TD]
[TD]0%[/TD]
[TD]0%[/TD]
[TD]0%[/TD]
[TD]0%[/TD]
[TD]0.0351%[/TD]
[TD]0.42%[/TD]
[TD]-0.34%[/TD]
[TD]-1.99%[/TD]
[TD]-0.15%[/TD]
[TD]0.04%[/TD]
[TD="align: right"]1.14%[/TD]
[/TR]
[/TABLE]
Click to expand...
 
Right, I understand that. What I don't understand is how TSP shows me losing more than -7% in the 3rd quarter when the TSPTalk spreadsheet shows a +1% gain. Something isn't correct.

As for the 7.34% return that is from 31 Dec 14 to 1 Dec 15 in the spreadsheet. TSP website shows about a -$900 loss during that same time frame.
 
BigLair said:
Right, I understand that. What I don't understand is how TSP shows me losing more than -7% in the 3rd quarter when the TSPTalk spreadsheet shows a +1% gain. Something isn't correct.

As for the 7.34% return that is from 31 Dec 14 to 1 Dec 15 in the spreadsheet. TSP website shows about a -$900 loss during that same time frame.
Click to expand...

Ok. -7 and +1 is a huge difference. Shouldn't be hard to find which is at fault. I would roll up my sleeves, pop my collar, and work through one and/or the other, step by step.

Please let us know what you find.

Unless someone knows that the TSPTalk spreadsheet is broke, I think that's the only way to figure it out (step by step).

And unless you are willing to send/post your redacted spreadsheet and statement, I can't figure it out from where I sit.
 
Where does the TSP website show you returns from the beginning of the year? I thought they always gave you results for the last 12 months in monthly increments.

If your contributions don't go into the same funds as your current allocation there will be some difference between your spreadsheet and TSP's accounting, but not that much. In my case it's only on the order of several 1/100 of a percent at most.
 
My guess is one of your moves to safety didnt go through. Check your TSP allocations for the quarter.
 
You can select dates from and to and it will show you a return/loss summary, not percentage but dollar amount for the period selected.
 
It is under Activity Summary:TSP 2015.JPG



Cactus said:
Where does the TSP website show you returns from the beginning of the year? I thought they always gave you results for the last 12 months in monthly increments.

If your contributions don't go into the same funds as your current allocation there will be some difference between your spreadsheet and TSP's accounting, but not that much. In my case it's only on the order of several 1/100 of a percent at most.
Click to expand...
 
BigLair said:
I feel I've done that several times...
Click to expand...

Yeah, you should have spotted something.?

I'll check it out asap, but I've got some trading business, etc. to attend to first. I should have a response today/tonight, if someone else doesn't figure it out first. :)
 
There are minor discrepancies in the share prices on the sheet, compared with the share prices in the TSP statement.

I didn't check them all, but I did find a minor discrepancy with your allocation on the sheet, for one transaction. It totaled 100.05 %. There could be more such issues.

But the major discrepancy is that Contributions aren't listed on the sheet until an IFT is made. So, from May until the end of August, if memory serves correctly, you had relatively large contributions entering funds, without being accounted for by the sheet. (You did not provide any statements prior to July, so I am assuming you were making similar (to your others) contributions between May and August.

May was the top of the market. The end of August was the bottom. Most "unexplained" losses probably occurred in this time frame.

There's yur problem. :)

I recommend reconciling the sheet share prices with the TSP statements as the statements become available.

I also recommend performing an "IFT" on the sheet to account for each Contribution. The Contribution will throw the percentages off on the allocations. This pseudo IFT will correct that; and should make your sheet more accurate going forward.

Hope I'm right. And hope this helps. :)
 
Thank you, that makes total sense and I'll do that. I feel pretty dumb at the moment.

userque said:
There are minor discrepancies in the share prices on the sheet, compared with the share prices in the TSP statement.

I didn't check them all, but I did find a minor discrepancy with your allocation on the sheet, for one transaction. It totaled 100.05 %. There could be more such issues.

But the major discrepancy is that Contributions aren't listed on the sheet until an IFT is made. So, from May until the end of August, if memory serves correctly, you had relatively large contributions entering funds, without being accounted for by the sheet. (You did not provide any statements prior to July, so I am assuming you were making similar (to your others) contributions between May and August.

May was the top of the market. The end of August was the bottom. Most "unexplained" losses probably occurred in this time frame.

There's yur problem. :)

I recommend reconciling the sheet share prices with the TSP statements as the statements become available.

I also recommend performing an "IFT" on the sheet to account for each Contribution. The Contribution will throw the percentages off on the allocations. This pseudo IFT will correct that; and should make your sheet more accurate going forward.

Hope I'm right. And hope this helps. :)
Click to expand...
 
You must log in or register to reply here.
Back
Top