Treasury T-Bills in Quicken
I bought T-bills at several brokerages, hoping to finally make some income on idle cash while the Federal Reserve Banks sorts out the inflation problem. Some brokerages are set up that you buy $1,000 multiples of a given T-bill. Other brokerages try to make T-bills more available by selling then in $100. more bite-size increments.
When I bought T-bills, things went as expected. The purchase showed in Quicken equity registers as holding an asset. The cost showed up in the cash register as a negative/decrease in dollar amount.
In one particular brokerage, the maturity of the T-bill was quite an experience! The US Treasury does the maturity as two transactions, fine with me. You get your return of capital as one transaction. You get the interest earned as a separate transaction. The SUM of those two transactions equals the PAR value of the T-bill that you purchased. All well and good.
In Quicken, the Maturity of the T-bill shows up as a SoldX transaction, no problem, it cancels out the BoughtX transaction and so the T-bill no longer shows up as an asset in the Equity register of Quicken. All well and good. The return of capital shows with a negative sign in the equity register as it goes to the Quicken Cash/Checking register of the brokerage account. In the Quicken Cash/Checking register the cash shows up as a positive return of capital. All well and good.
The fun part. When I look at "my holdings" for that brokerage account, I now see:
1- The dollars returned at maturity are now a negative number of SHARES
2- The quote price is now 100, which I think is a bastardization of the 100% par value.
3- My LOSS is now listed as a negative value that strongly exceed my entire net worth and then some I speculate because Quicken has taken the huge negative shares (mentioned in 1-) times the quote of 100 (listed in 2-). Quicken has created a huge negative number that has totally sunk my net worth beneath a sea of red ink.
Okay, call me crazy. Maybe I'm totally missing something that is obvious to the gurus.
The only solution I see is to delete that Quicken entry.
My conclusion is that Quicken needs a much improved way to deal with T-bills.
I would be most appreciative if someone who actually is a developer at Quicken would respond with an explanation of the situation.
As usual, YMMV
And thanks in advance for constructive comments.
Comments
-
"The US Treasury does the maturity as two transactions, fine with me. "
That's not been my experience, at least not when purchasing through Treasury Direct. In that case the maturity value (basis + interest) showed up in my bank account, no distinction. What you saw there might be some broker manipulation. (Teasury Direct has no Quicken downloading ability.)
"In Quicken, the Maturity of the T-bill shows up as a SoldX transaction, no problem, it cancels out the BoughtX transaction and so the T-bill no longer shows up as an asset in the Equity register "
Not necessarily. Since you're buying through a broker the broker codes the transaction. I'd say the broker got that part right, assuming that the sale resulted in no gain or loss. If there was a gain on the sale, the interest element has been classified as capital gain. In my case buying a "test" T-Bill (real money, it was a test of how it would get reported in Quicken) at Schwab the downloaded transactions came in as a $1,000 Deposit entry and another entry that was nonsensical.
Clearly your situation makes no sense either, and I don't think it's exactly Quicken's fault, though it might be. (With a Direct Connect download the broker codes the entries. With an EWC+ download the broker allows Quicken to collect the information it thinks it needs and codes the entries.) Too, the issue of "pricing" is a chronic problem, with a shift of decimal points frequently causing confusion. This can be a fault at either end of the Financial Institution - Quicken chain.
In the case of my one purchase of a T-Bill through Treasury Direct, (another test) I purchased the T-Bill at the issue price, the Treasury sent me $10,000 at maturity. The proper set of transactons for the maturity is to do a BondSold action for the original purchase price for not gain or loss, then do an IntInc action for the difference. Easy Peasy.
In the case of Schwab (ESW+)where the maturity came in as a simple Deposit of the maturity amount and another, nonsensical, entry, I simply deleted those two entries and made the same sort of entry as I described above.
Since you're buying through a broker and, maybe, incurring transaction costs and/or premium or discount, I guess you could theoretically have a gain or loss element in your closing entry in addition to eleminating the security and recognizing interest income, but I don't know if that's really the case or not. In the one bond I bought through Schwab the 1099-INT confirmed my manual entry.
Since T-Bills are designed to sell at a discount and return principal and interest at maturity then I'd say the most-common "correct" Quicken entry here is selling the bond at no gain or loss and recognizing the rest of the cash as interest income.
0 -
My experience has been similar to Tom Young's. Any time a fixed income security matures at Schwab - T Bill, Note, bond, CD - it gets communicated to Quicken (Windows or Mac) as a deposit for the full face value, which requires the manual edits that Tom mentions. With the T Bill it is essential to make the new/corrected Bond Sale transaction exactly equal to your basis amount and basis shares. The interest income is the difference between basis and par for a T Bill (zero coupon bond).
Tax tracking: I just switched from QWin to QMac so can't remember if QWin tracked the interest as "US Treasury" interest for tax reports. (QMac, even if the T Bill is classified as a T Bill in the security settings, lumps the interest with fully taxable interest.)
Quicken user since 1990, MacBook Pro M2 Max on Sequoia 15.2
0 -
Tax tracking: I just switched from QWin to QMac so can't remember if QWin tracked the interest as "US Treasury" interest for tax reports. (QMac, even if the T Bill is classified as a T Bill in the security settings, lumps the interest with fully taxable interest.)
The QWin tax line options for a category are:
The Interest action flows either to the generic Schedule B: Interest Income or the Schedule B: Int. inc, nontaxable, depending on the 'Tax-free' status of the security. One could use a category with the Schedule B: US government interest, but would have to do so with a MiscInc transaction (rather than an IntInc transactions).
1 -
@q_lurker wrote: "The Interest action flows either to the generic Schedule B: Interest Income or the Schedule B: Int. inc, nontaxable, depending on the 'Tax-free' status of the security. One could use a category with the Schedule B: US government interest, but would have to do so with a MiscInc transaction (rather than an IntInc transactions)."
Thanks. So, it's the same with QMac… having to do a Misc Income with the US gov int tagged category to get US Gov Int reported somewhat properly (not grouped where it belongs, but at least grouped).
This is a flaw in both QWin and QMac, as it is incorrect to classify government bonds as tax exempt… the interest is federally taxable, and only exempt from state tax in most states. It should be classified as what it is and reported as such. Getting a tax exempt interest report that contains actual federally tax-exempt bonds … along with US government bond interest which is federally taxable just doesn't let anyone reconcile their 1099-INTs properly. Using MiscInc puts that US government interest in a different place in the final report, as well as requiring multiple extra steps.
Quicken user since 1990, MacBook Pro M2 Max on Sequoia 15.2
0 -
I agree with everything that has been said so far. I went back to the Fidelity transaction of the matured T-bill and made adjustments as appropriate, IMO. I edited the SoldX transaction which had a Bonds Sold label in the pane. I adjusted number of ‘shares’ and ‘Price/share’ to get the proper Redemption Payout. There was a space for Interest earned, so I filled it out and it got a total. In the editing pane, there also was a slot for Category, so I chose Income: Interest: Taxable – a category I created. When I hit ‘enter,’ Quicken changed the transaction into two transactions with no help from me. One was the return of basis paid for the zero coupon T-bill. The other was the interest earned. Huzzah! All well and good as far as I am concerned.
My question now is on the second transaction Quicken itself created. Why is the transaction = MiscExpX ? I get the ‘X’, and I suppose it takes less programming skill to jam the income into ‘Misc,’ but why ‘Exp’ for what is clearly income for tax purposes? There must be a CPA, double-entry bookkeeping reason for this?0 -
"My question now is on the second transaction Quicken itself created. Why is the transaction = MiscExpX ? I get the ‘X’, and I suppose it takes less programming skill to jam the income into ‘Misc,’ but why ‘Exp’ for what is clearly income for tax purposes? There must be a CPA, double-entry bookkeeping reason for this? "
I assume you used the Bonds Sold action, though I'm confused because that screen doesn't have a "Category" field for your use. But I do see that when you have an Investment Account with a linked checking Account and record the interest received in that Accrued Interest box it's recorded as a MiscExpX using the Category _AccruedInt.
Not to worry, it IS an income item. The programers, for whatever reason, use that MiscExpX action both for accrued interest you receive and accrued interest you pay, they just change the sign (and color) of the number. Run an Income and Expense report and you'll see that income.
(Generally, Quicken doesn't handle real accrued interest properly so I don't use that particular Category. I simply make two enteries - one Bond Sold and one IntInc.
0 -
Agree, thanks for the update0
-
1st time buyer of T-Bills. Using TD Ameritrade (purchased by Schwab and in transition) I just purchased x number of 1yr T-Bills and Quicken downloaded, into my account registry, all applicable dollar and share information. It shows my total cost correctly. However, in my accounts list (in the side bar) it shows the total for that account to be the same as before the purchase. Would this be because Quicken sees the purchase as an asset?0
-
If all you did was trade cash in the Account for T-Bills that are now also in the Account then the Account's balance wouldn't change. Is that the situation here?
1 -
Tom Young, let's say I had $1,000 in an investment account. The face value of the 1 yr. T-Bill I purchased is $1,000. The Treasury debits my account for the face value minus the % that the bill will pay, in this case, it was 4.62% (+/-) or $46.20. Therefore my account registry would show -$953.80 as the transaction entry and in this case the total value of my account. Then over in the Accounts list in the sidebar, my account continues to show $1,000 which makes me think Quicken views the full amount as an asset. This seems to make some sense since I can now sell the T-Bill on the secondary market if I so choose.0
-
@Michael3442 What you're saying now makes no sense. The face value of a T-bill, like anything else (other than CDs) is the market value. For a zero coupon bond like a T-bill, the value from anyone's point of view is not face value, but market value. The day you bought it for 953.80, that's pretty much market value (other than games brokerages play to make a teeny tiny profit off it).
If your account was $ 1,000 and you bought a bond for 953.80, the account is still $ 1,000 in valuation because you only spent 953.80 and you still have 46.20 in cash. If Quicken obtains market matrix valuations from your brokerage in subsequent days, you'll see the account value change, like any marketable security. I think that's whatj @Tom Young was trying to say as well.
Quicken user since 1990, MacBook Pro M2 Max on Sequoia 15.2
0