Hello Fellow TSP'rs! OK Here we go again! The Updated 2017 version of the Scout TSP Contribution spreadsheet is out. Tom was kind enough to post it in the Utilities section. Thanks to MadMike 61 there are modifications to the formulas affecting the contributions into the spreadsheet. Very nice! Otherwise it is very similar to the 2016 version. There are 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 pay date and the calculator gives you return on investment %s including the matching contributions. I added some additional input lines. Its so easy to mess up a spreadsheet when adding lines in the middle of the year. If you haven't used the spreadsheet before I suggest you start with the Tips tab. Hopefully this link will work.
http://www.tsptalk.com/utilities/Sco...ed-Version.xls
TSP Calculator - Thrift Savings Plan
Note. The original 2017 spreadsheet did not have the updated formula modifications so Tom has been kind enough to re-post the Spreadsheet. The one you want to use is the one titled Scouts_Spreadsheet-2017-Updated Version.xls. MadMike61 has some information regarding the modifications and suggestions for those who would rather update the formulas in their original Spreadsheet. See below. The contribution is entered on the same date as noted by TSP. With the modified formula there is no need to create a second line for the same date. So you enter the total contribution amount in column E for the pay date. No need to ever create a duplicate date line. Fund CSIGF amount are as advertised by TSP for that date. Note, for those who have already started the report for the year, they can start a new spreadsheet or use the new formulas and copy that formula to the rest of their fields in the column. The way to do that is to paste each fund formula into the row 8 then extend that formula to each subsequent date. You do that by highlighting the cell and moving your pointer to the bottom right of the cell until a “+” sign is displayed. Then right click with your mouse and drag the formula down to subsequent cells. The formula will automatically update for each subsequent cell. Modified formulas are shown below.
Below are the primer formulas for the 5 funds for line 8 on the Scouts 2017 tracker.
=IF((E8=0)=TRUE,+B7*W8,(E8*AB7)+(B7*W8)) C Fund - Column B
=IF((E8=0)=TRUE,+C7*X8,(E8*AC7)+(C7*X8)) S Fund - Column C
=IF((E8=0)=TRUE,+D7*Y8,(E8*AD7)+(D7*Y8)) I Fund - Column D
=IF((E8=0)=TRUE,+H7*U8,(E8*Z7)+(H7*U8)) G Fund - Column H
=IF((E8=0)=TRUE,+I7*V8,(E8*AA7)+(I7*V8)) F Fund - Column I
Thanks again to MadMike61 for his contribution to the Spreadsheet!
Bookmarks