Recording TIPS purchase in Roth IRA in quicken- Understanding FIDO information/labels?

Have a question about your personal investments? No matter how simple or complex, you can ask it here.
Post Reply
Topic Author
coachd50
Posts: 1815
Joined: Sun Oct 22, 2017 10:12 am

Recording TIPS purchase in Roth IRA in quicken- Understanding FIDO information/labels?

Post by coachd50 »

Just looking to see if anyone can help me with regards to a TIPS purchase in March 2023 and how it is most accurately recorded in quicken.

According to my Fido transaction history
Transaction date 3/23/2023
91282CGK1 is the "symbol"
Description is UNITED STATES TREAS NTS SER A-2033 1.12500% 01/15/2033
I bought "5000 shares" @ $99.47345 (their terms, not mine)
The total amount was $5005.72
Interest - $11.70
Settlement date 3/31/2023

When I look at the Position Data on Fido, it says that the Cost Basis is $5147.81 and Current Value is $4851.57
User avatar
#Cruncher
Posts: 3983
Joined: Fri May 14, 2010 2:33 am
Location: New York City
Contact:

Re: Recording TIPS purchase in Roth IRA in quicken- Understanding FIDO information/labels?

Post by #Cruncher »

coachd50 wrote: Mon Apr 01, 2024 4:31 pmJust looking to see if anyone can help me with regards to a TIPS purchase in March 2023 and how it is most accurately recorded in quicken. ... When I look at the Position Data on Fido, it says that the Cost Basis is $5147.81 and Current Value is $4851.57
I can't help you with recording TIPS in Quicken. But I can show how Fidelity gets the $5,147.81 it reports as cost basis. As shown in cell D13 of the table below, it adds the inflation adjustment (aka OID) in cell D12 to the cost in cell D9.

Code: Select all

Row                  Col A        Col B       Col C      Col D   Formula in Column B or D
  2             Face value        5,000
  3               Maturity    1/15/2033
  4                 Coupon       1.125%
  5                              Bought      Valued
  6                   Date    3/31/2023   3/29/2024
  7                  Price    99.473545   93.764640

  8  Purchase / Value Date       Real $   Idx Ratio  Indexed $
  9              3/31/2023    -4,973.68     1.00409  -4,994.02  =-B2*(B7/100)
 10              3/29/2024     4,688.23     1.03484   4,851.57  =B2*(C7/100)
 11  Overall gain / (loss)      -285.45                -142.45  =SUM(B9:B10)

Code: Select all

 12                    OID                              153.75  =B2*(C10-C9)
 13             Cost basis                            5,147.77  =-D9+D12
 14  Capital gain / (loss)                             -296.20  =D10-D13
Coupon interest
 15      Accrued 3/31/2023       -11.65     1.00409     -11.70  =-B2*(B4/2)*COUPDAYBS(B6,B3,2,1)/COUPDAYS(B6,B3,2,1)
 16              7/15/2023        28.13     1.01958      28.68  =B$2*(B$4/2)
 17              1/15/2024        28.13     1.03194      29.02  =B$2*(B$4/2)
 18      Accrued 3/29/2024        11.44     1.03484      11.83  =B2*(B4/2)*COUPDAYBS(C6,B3,2,1)/COUPDAYS(C6,B3,2,1)
 19         Total interest        56.03                  57.83  =SUM(B15:B18)
Miscellaneous
 20     Cost + accrued int                           -5,005.72  =D9+D15
Notes
  • You purchased at the 3/23/2023 re-opening auction.
  • You didn't specify the date for Fidelity's "Position Data". It appears to be 3/29/2024 since that lets me come within $0.04 of its $5,147.81 cost base in cell D13.
  • You didn't include Fidelity's 3/29/2024 price. I backed into it so as to match Fidelity's $4,851.57 valuation in cell D10.
  • Index ratios are from TreasuryDirect's TIPS/CPI Query Results. They are multiplied by the Real $ in column B to get the Indexed $ in column D.
Topic Author
coachd50
Posts: 1815
Joined: Sun Oct 22, 2017 10:12 am

Re: Recording TIPS purchase in Roth IRA in quicken- Understanding FIDO information/labels?

Post by coachd50 »

#Cruncher wrote: Tue Apr 02, 2024 10:49 am
coachd50 wrote: Mon Apr 01, 2024 4:31 pmJust looking to see if anyone can help me with regards to a TIPS purchase in March 2023 and how it is most accurately recorded in quicken. ... When I look at the Position Data on Fido, it says that the Cost Basis is $5147.81 and Current Value is $4851.57
I can't help you with recording TIPS in Quicken. But I can show how Fidelity gets the $5,147.81 it reports as cost basis. As shown in cell D13 of the table below, it adds the inflation adjustment (aka OID) in cell D12 to the cost in cell D9.

Code: Select all

Row                  Col A        Col B       Col C      Col D   Formula in Column B or D
  2             Face value        5,000
  3               Maturity    1/15/2033
  4                 Coupon       1.125%
  5                              Bought      Valued
  6                   Date    3/31/2023   3/29/2024
  7                  Price    99.473545   93.764640

  8  Purchase / Value Date       Real $   Idx Ratio  Indexed $
  9              3/31/2023    -4,973.68     1.00409  -4,994.02  =-B2*(B7/100)
 10              3/29/2024     4,688.23     1.03484   4,851.57  =B2*(C7/100)
 11  Overall gain / (loss)      -285.45                -142.45  =SUM(B9:B10)

Code: Select all

 12                    OID                              153.75  =B2*(C10-C9)
 13             Cost basis                            5,147.77  =-D9+D12
 14  Capital gain / (loss)                             -296.20  =D10-D13
Coupon interest
 15      Accrued 3/31/2023       -11.65     1.00409     -11.70  =-B2*(B4/2)*COUPDAYBS(B6,B3,2,1)/COUPDAYS(B6,B3,2,1)
 16              7/15/2023        28.13     1.01958      28.68  =B$2*(B$4/2)
 17              1/15/2024        28.13     1.03194      29.02  =B$2*(B$4/2)
 18      Accrued 3/29/2024        11.44     1.03484      11.83  =B2*(B4/2)*COUPDAYBS(C6,B3,2,1)/COUPDAYS(C6,B3,2,1)
 19         Total interest        56.03                  57.83  =SUM(B15:B18)
Miscellaneous
 20     Cost + accrued int                           -5,005.72  =D9+D15
Notes
  • You purchased at the 3/23/2023 re-opening auction.
  • You didn't specify the date for Fidelity's "Position Data". It appears to be 3/29/2024 since that lets me come within $0.04 of its $5,147.81 cost base in cell D13.
  • You didn't include Fidelity's 3/29/2024 price. I backed into it so as to match Fidelity's $4,851.57 valuation in cell D10.
  • Index ratios are from TreasuryDirect's TIPS/CPI Query Results. They are multiplied by the Real $ in column B to get the Indexed $ in column D.
Thank you! I would say that the information in your notes is correct. I apologize for not including that info.

As far as recording into QUICKEN, I guess I should ask what do some of those fields/numbers Fido is showing represent.
For example, the OID figure and the FIOD Cost Basis figures. How do those impact me?

I purchased this in my Roth to protect me from any tax implications while I try to learn more about TIPS and how they work etc.
Post Reply