locked
How does Money Deluxe Sunset handle US Treasury Bills? RRS feed

  • Question

  • Hello all. I just purchased 3x 28 Day US Treasury Bills and was wondering what was the best way to account for this in MS Money Deluxe Sunset Edition.

    Here's what i've done so far:

    1. Create a new Investment account called Treasury Direct

    2. Today (the date of the sale), I transferred $299.58 (the amount of the sale) to the Treasury Direct (cash).

    3. I purchased 3x of a new Investment called (Treasury Bill - 28 Day). I selected Investment Type=Bond, Bond Subtype=Treasury Bill (so far so good).

    4. For the price, I had to make it $9985.8833 instead of $99.858833 as reported in my account online

    5. Accrued Interest=0, Commission=0

    Now, how does this work when the T-bill matures in 28 days? Assuming I dont roll it over.

    Many thanks!

    Thursday, June 21, 2018 5:22 PM

Answers

  • As I stated, Redeem, as well as accrued interest doesn't report properly, but the following using Sell does.  My method is working perfectly in all reporting.  Here is an example for a 8-week bill through TreasuryDirect, with the equivalent APY of 2.429%. I expect it to be compatible with Schwab, as well, as this is the same method they use for secondary CDs (which they treat as bonds), other than my commission withdrawal.


    • Edited by ameridan Saturday, April 6, 2019 6:13 PM
    • Proposed as answer by ameridan Saturday, April 6, 2019 6:29 PM
    • Marked as answer by Cal LearnerModerator Sunday, April 7, 2019 6:36 PM
    Saturday, April 6, 2019 5:08 PM

All replies

  • Wish someone would answer this question... with the upcoming rise in interest rates, I have been taking another look at T Bills and would also like to know how MS Money handles Treasury Direct..
    Monday, October 8, 2018 7:05 PM
  • Wish someone would answer this question... with the upcoming rise in interest rates, I have been taking another look at T Bills and would also like to know how MS Money handles Treasury Direct..

    I set it up Treasury Direct as an investment account. Money does not handle it in a special way. Treasury Direct does not have a provision for downloading transactions, as far as I know.

    Perhaps you are asking what security type can handle T-bills best. I don't know if this would be the time for the Bond type investment. You might experiment with a test file. I thinkprice box for a bond is a percent of par. Let us know the results of your experiments. Quantity is maybe in units of $1000 face value. My experiment just now did not work out. See if you do better.

    But do your testing on a new file or a copy of your file, rather than your real file initially.

    Tuesday, October 9, 2018 12:03 AM
    Moderator
  • OK, thanks for the reply. Have to say I am not crazy about Treasury Direct to begin with, but I want to make sure MS Money can provide the accounting support.

    I did find this on the MS Money accountant forum which is not too encouraging:

    https://www.accountantforums.com/threads/treasury-notes-in-microsoft-money.83240/

    Tuesday, October 9, 2018 2:27 PM
  • I came across this question in looking for a resolution myself.  The solution I've come up with isn't ideal, but I think it will work for me.  Feedback welcomed...

    Purchasing T-bills is pretty straight forward, using your linked bank account as the Cash transfer account for purchasing these "Bonds" 1,000 @ 98.5 (as an example) = $985.

    It appears that the redemption is the tricky part.  First, I created a unique Investment Income sub-category "Treasury Bill Interest" which I assigned the tax category as Schedule B and U.S. government interest.  Upon the sale date, 2 transactions are needed:

    1.  Record the interest as a separate investment activity, selecting "Other Income" as the activity.  I discovered that this allows you to then specify "Investment Income : Treasury Bill Interest" to report properly in the Tax transactions as State & local non-taxable income of $15. 

    2.  Then in the actual redemption transaction, record 1,000 @ 100, using the commission entry to offset the interest of $15, so that everything balances out.

    Monday, April 1, 2019 3:16 AM
  • That has good merit.  My comments below reflect some things I have considered, and I don't propose that any are better than your clear description.

    An alternative is to create a MMF, buying at the purchase price, sell for no gain, and recording an interest as you did. Use the memo to track the actual face amount.

    Similar is to make an Other investment that you enter the nominal "face value" as the number of shares, and the amount you paid as the Total. Then your idea of entering the interest amount as commission, and entering a separate interest:treasury, is novel and could be used. That clutters up the reporting I think, such as entering no-gain Sells into the tax stuff.

    There are several fairly good ways, and no ideal way IMO.

    Having some reinvestments at Treasury Direct (TD) could complicate things a tad possibly, and I don't know if that would be an advantage for one way or the other. For the Bond way, would you closed the bond, and enter a new bond Buy at reinvestment time? With MMF, I figure I could just let it ride, and do a dividend reinvestment when interest gets paid,  but that does not handle having interest:treasury tracked.

    I agree that the TD website is not easy to use.


    Monday, April 1, 2019 4:07 PM
    Moderator
  • If reinvesting within the TD account, simply check "Track Cash transactions" in Settings so that those bond redemptions are transferred to/from the Cash account, rather than the linked Bank account.  I just discovered a flaw in Money - if you use Redeem CD/Bond rather than Sell, it reports the entire amount as interest in the Tax software report.  I've expounded on this topic in my blog today @ https://microsoftmoneyoffline.wordpress.com/2019/04/01/u-s-treasury-bill-transactions/

    Sorry Cal, but what do you mean by MMF?  (I think I just guessed - Money Market Fund ;) )





    • Edited by ameridan Monday, April 1, 2019 8:26 PM
    Monday, April 1, 2019 7:21 PM
  • Yep, Money Market Fund. It has the advantage of not cluttering capital gains reports with zero CG entries, whereas Other, Stock, or Bond do I think

    I do track the cash transactions in Money always.

    With the bond method, what if you did not enter a commission equal to the interest? You get an interest credited? If you buy a same issue type later, do you re-use that same defined bond or make a new one?

    I am pretty much now using the 1099-DIVs and 1099-B downloaded from the broker for taxes. I use the Money info to get early forecasts and to check the 1099 stuff.

    I read your blog post. Looks good.

    Monday, April 1, 2019 8:49 PM
    Moderator
  • I just put in an order for my first t-bill auction today, so I don't have any actual experience with it yet.  Schwab always lists t-bills as "US TREASURY BILL DUE mm/dd/yyyy", so they are purchased individually, each with their own CUSIP.
    Monday, April 1, 2019 10:15 PM
  • I just put in an order for my first t-bill auction today, so I don't have any actual experience with it yet.  Schwab always lists t-bills as "US TREASURY BILL DUE mm/dd/yyyy", so they are purchased individually, each with their own CUSIP.
    Did you buy via Schwab? That would save you from having to navigate TD. I would see how it downloads via PocketSense from Schwab, and go with the flow.
    Monday, April 1, 2019 10:59 PM
    Moderator
  • I'm trying out Schwab + TreasuryDirect.
    Monday, April 1, 2019 11:15 PM
  • This post is not a prescription of how to do T-bills. It is a commentary on the "bond" way.

    I only now made a test file with only these two transactions (used Investment Transactions report):
    4/6/2018 tbill 1 Buy 10,000 $97.00  9,700.00
    9/6/2018 tbill 1 Redeem CD/Bond 10,000   10,000.00

    "tbill 1" is defined as a Bond:Treasury Bill.

    Logically the reports should show a $300 income for 2018. Let's see what the reports show:

    Tax-related transactions: shows Interest $10,000

    Tax software report: shows interest income of $10,000.

    Capital gains: $0.

    Monthly income and expenses: (9,700) in red,  which is -9,700, in the 4/2018 column. It shows 10,000 in the 9/2018 column. That would be cash flow, not income.

    This experiment seems to show that using type Bond is not going to be as useful as another way.

    screen snip including the accrued interest field

    I then discovered the Accrued Interest field, and thought that would help somehow.  Then the Tax software report shows interest income of 10,300. So no help.

    I can see that using no  Accrued Interest but declaring a 9,700 commission, does indeed cause $300 in interest reported. That is your method I think. It makes the interest reports right.  However it does mess up the cash account. Did I get something wrong? Edit: Yes, I did. See below.

    I entered a T-bill order through Fidelity. I should have the OFX results of the Buy soon.




    Saturday, April 6, 2019 4:06 PM
    Moderator
  • As I stated, Redeem, as well as accrued interest doesn't report properly, but the following using Sell does.  My method is working perfectly in all reporting.  Here is an example for a 8-week bill through TreasuryDirect, with the equivalent APY of 2.429%. I expect it to be compatible with Schwab, as well, as this is the same method they use for secondary CDs (which they treat as bonds), other than my commission withdrawal.


    • Edited by ameridan Saturday, April 6, 2019 6:13 PM
    • Proposed as answer by ameridan Saturday, April 6, 2019 6:29 PM
    • Marked as answer by Cal LearnerModerator Sunday, April 7, 2019 6:36 PM
    Saturday, April 6, 2019 5:08 PM
  • Got it. Thanks.  I agree. I had missed the critical part.

    This would be the MMF equivalent:

    Using bond is probably going to match up with the Pocketsense OFX downloads, so that would be a significant advantage.

    An advantage of the MMF method is that if you tried to enter a 100000 sell of the mmf, Money would come back and ask if you want to make that 99,629.80 instead. Also, there is no $0.00 capital gain entered into the reports.

    Saturday, April 6, 2019 6:24 PM
    Moderator
  • Here are how the details looked after downloading from Fidelity. Same for Schwab? That investment name is not optimum, but I am going to let it be. After closing the security, I might "delete" that security to remove that from lists.

    It seems to me that instead of entering the interest into the cash account, it might be better to enter it into the investment register, associating the interest with the particular issue.

    Tuesday, April 9, 2019 9:04 PM
    Moderator
  • They're concurring that it's hidden interest too.  At tax reporting time, it will be reported as interest though.

    You could enter the interest into the investment register, but then it will require yet another transaction to transfer back to the cash account.  I haven't tried out at Schwab yet, as I got my feet wet with TD first, but I expect a good outcome.  By the way, Schwab One sets up the checking account as the Cash account for the investment account directly (same account number), which is why it works so smoothly with them.

    Regarding the name, I always enter the investment with my own names first, then when downloading, I concur with Money's match.  By the way, you're matching your transaction to the wrong T-bill, based on the CUSIP ;)



    • Edited by ameridan Tuesday, April 9, 2019 10:48 PM
    Tuesday, April 9, 2019 10:41 PM
  • When you enter a transaction for a security into the investment register, the cash part automatically appears in the cash account too.

    What kind of names do you intend to use? Ones you can reasonably reuse, such as T-bill 26 week? OR T-bill 7/11/2019, or what?

    I marked for Fidelity to reinvest. We will see how that comes in.

    Tuesday, April 9, 2019 10:47 PM
    Moderator
  • I'm going to use specific "US TREASURY BILL DUE mm/dd/yyyy" names, since they could overlap, and I'd rather the price info stay kosher, but I realize why you might think otherwise - there will only be 3 transactions per investment; 1 buy and 2 sells.

    Now I see what you meant, yes for reinvest, the interest will go to the investment for a larger purchase the next go around.

    Tuesday, April 9, 2019 11:35 PM
  • I'm going to use specific "US TREASURY BILL DUE mm/dd/yyyy" names, since they could overlap, and I'd rather the price info stay kosher, but I realize why you might think otherwise - there will only be 3 transactions per investment; 1 buy and 2 sells.

    Now I see what you meant, yes for reinvest, the interest will go to the investment for a larger purchase the next go around.

    Regarding naming, your method looks good. Having a name that begins with a CUSIP number means it would be hard to find what  you intended. Suppose you have a 26 week bill and a 13 week bill that come due the same date. Would you use the same symbol for both?

    That's a good point, but that is not what I meant. I was thinking that by entering the interest/income for a specific investment, the Portfolio view would show useful performance info.

    Tuesday, April 9, 2019 11:41 PM
    Moderator

  • Regarding naming, your method looks good. Having a name that begins with a CUSIP number means it would be hard to find what  you intended. Suppose you have a 26 week bill and a 13 week bill that come due the same date. Would you use the same symbol for both?

    Matching is done by CUSIP anyways, but if that happened, I would probably enhance the names.

    That's a good point, but that is not what I meant. I was thinking that by entering the interest/income for a specific investment, the Portfolio view would show useful performance info.

    I always display the memo field in Portfolio view, and add details in the comments section of each investment's details. ie 2.429% APY @ 99.629778.  CUSIP is already displayed in the first column.



    • Edited by ameridan Wednesday, April 10, 2019 12:50 AM
    Wednesday, April 10, 2019 12:46 AM
  • Cal, I'm happy to report that all worked out well with this trial transaction, other than the fact that when the bank reported the return of the bond sale as a $100,000 deposit, I had to click on "Don't match this transaction", so that my details reflecting the hidden interest, wouldn't be overwritten.   Simply accept the bank deposit, and mark "VOID".  After 7 days, my pocketsense default download period, I'll just delete the voided transaction, and all is well.
    • Edited by ameridan Wednesday, June 5, 2019 4:25 PM
    Wednesday, June 5, 2019 4:23 PM