How to split interest by bank account without a separate interest report?

louabill

Is there a simple way to get a unified tax report which splits interest income by bank account (or by bank)? This is kinda necessary for Schedule B.

I could make an extra report for Schedule B which splits on payee, but it would be nice to have a unified tax report instead of multiple separate reports.

Anywasy, I can split by account if I make a subcategory for each bank account and then change the rows of the unified tax report to 'category', but this seems counterintuitive. After all, charitable donations seem to be split by payee without any further ado.

Part of the reason I'm asking is becuase I have a tax report I've used since 1992 which allows me to split interest income by bank account as is needed for Schedule B. I believe it used tax report numbers, which do not exist any more (but the report works fine, still).

jacobs

The Schedule B report in Quicken Mac is actually built as a transaction report, so it lists every interest and dividend transaction; these can be collapsed down to a summary report, similar to what you show for Quicken Windows, by selecting Summary of All Items from the View menu on the report.

The issue is how the Payee is recorded on your interest transactions. I looked again at mine, and I noticed that when I recorded interest income transactions in my Schwab account, there is no Payee or Security. If I now edit those transactions to use "Charles Schwab" as the security, then those interest transactions are summarized into a total line for Charles Schwab. And if I had interest from Fidelity, and did that the same way, I'd end up with two lines for interest income on this part of the Schedule B report. (I enter my transactions manually, so I'm not sure how they are coded when downloaded from each financial institution.) I will also note that in Quicken Mac, Investments:Interest Income is a category reserved for use by Quicken for investment accounts, so interest recorded from a banking account goes into a separate Personal Income:Interest Earned category. The Schedule B report intelligently report son both categories of interest income, so as long as the Payee name or Security name for each type of transaction is the financial institution name, then the Schedule B report produces a perfect interest-by-account total, just like the Quicken Windows report.

Jon

@louabill @jacobs The subcategories on the tax report below "Interest Income" are the payees. When you enter interest income, if you enter the bank name as the payee, you'll get interest broken out by bank on your tax report. But if you just enter something generic like "Interest" for the payee (which is what I tend to do), then interest from all the banks will get lumped together under that generic payee name & won't be broken out by bank.

So you can get what you want but you'll have to put in the work each month to enter the correct payee names every time you earn interest.

Edit: and going back through the thread, I see @jacobs made this same point a couple days ago, perhaps it got overlooked.

NotACPA

Take a look at the report "Schedule B - Interest and Dividends". When I ran it just now, without any customization on my part, it automatically subtotaled by account.

jacobs

Try this to see if it provides what you're looking for:

• Click on Reports
• Select Crosstab > Category by Account
• Click on the Accounts tab, and de-select any accounts which don't have interest income (such as credit cards, petty cash, etc.), so that only your interest-earning accounts are selected
• Click on the Categories tab, click "Only transactions with selected categories", and click Clear All
• Type "Interest" in the Search box, and select only the categories which are for earned interest. This would generally be Investments: Interest Income and Personal Income: Interest Earned.
• Click on the Advanced tab and set Transfers to "Exclude all transfers"
• Set the date range to last year or whatever range you want.

The resulting report should show your interest earned, by account, with each account in its own column.

NotACPA

OOPS, I didn't realize that this was a QMac question, and I don't know if the report that I referenced exists in QMac.

jacobs

OOPS, I didn't realize that this was a QMac question, and I don't know if the report that I referenced exists in QMac.

There is a Schedule B report in Quicken Mac. However, I find that it is organized to subtotal by security, which works fine for dividend income, but may not work well for interest income. My interest income recorded in a brokerage account doesn't have a security, so it isn't subtotaled by account:

This might depend how one's interest income transactions are recorded.

Then for interest earned in a bank account, it's reported without being subtotaled by account, it's subtotaled by Payee:

So again, depending how one's transactions are recorded, if you have a different Payee for each bank account it might provide perfect subtotaling.

The alternative report I suggested, a cross-tab report with categories as rows and accounts as columns, seemed like a way to capture all the data irrespective of how the payees or securities were recorded on the transactions.

EDITING TO ADD: If you have interest in a brokerage money market account, it also matters whether the transactions are reported as interest income or as reinvested dividends. for instance, if you have a Schwab money market fund which Schwab sends to Quicken as cash rather than a security, it may record interest income, while a 1099 from Schwab will show the earnings for the money market fund as dividend income. For this any other reasons, I personally don't use the values in Quicken when filing my taxes; I only enter the values on the brokerage and bank 1099's, since those are the values the financial institutions have reported to IRS.

NotACPA

Here's the counterpart in QWin. Might be time for a Product Idea.

jacobs

The Schedule B report in Quicken Mac is actually built as a transaction report, so it lists every interest and dividend transaction; these can be collapsed down to a summary report, similar to what you show for Quicken Windows, by selecting Summary of All Items from the View menu on the report.

The issue is how the Payee is recorded on your interest transactions. I looked again at mine, and I noticed that when I recorded interest income transactions in my Schwab account, there is no Payee or Security. If I now edit those transactions to use "Charles Schwab" as the security, then those interest transactions are summarized into a total line for Charles Schwab. And if I had interest from Fidelity, and did that the same way, I'd end up with two lines for interest income on this part of the Schedule B report. (I enter my transactions manually, so I'm not sure how they are coded when downloaded from each financial institution.) I will also note that in Quicken Mac, Investments:Interest Income is a category reserved for use by Quicken for investment accounts, so interest recorded from a banking account goes into a separate Personal Income:Interest Earned category. The Schedule B report intelligently report son both categories of interest income, so as long as the Payee name or Security name for each type of transaction is the financial institution name, then the Schedule B report produces a perfect interest-by-account total, just like the Quicken Windows report.

louabill

Thanks for the answers. I can get an interest report by itself OK. I was trying to for an entire tax report (so 1040, Sched A, Sched B, etc.) in one go, like I have with my still-working-but-no-longer-createable report.

I think I'll just make subcategories by each bank and then by each account within bank, and then I'll get what I need with a category report. I guess most people's taxes are too complicated for a single tax report to rule them all. Luckily, mine aren't.

Cheers

volvogirl

That's kinda of what I do on Windows. I made an interest category for each bank. I have several accounts at each bank. Makes a nice report with all my accounts going down the side and columns for each bank. The accounts aren't listed in bank order but they post in the bank category. So the column totals will match the 1099Int tax forms I get.

jacobs

Have you tried the Tax Schedule Report? It combines most of the other tax reports (except Schedule D) in one report.

louabill

@volvogirl: So, I'm not crazy. Thanks.

@jacobs: The tax schedule report makes a single item for interest on Schedule B, so all the banks and accounts are all combined.

FWIW, I've already made subcategories of Interest income for each bank, and then subcategories for each account under each bank. Makes for a nice, tidy report.

jacobs

@jacobs: The tax schedule report makes a single item for interest on Schedule B, so all the banks and accounts are all combined.

No, it actually doesn't. The Schedule B section of the Tax Schedule report is the exact same as the stand-alone Schedule B report. Here's a screenshot of the Tax Schedule report showing the same account-by-account interest as I showed above from the Schedule B report:

If you're seeing Interest Income as a single line combining multiple accounts, you may need to click the ">" icon to expand it so you see each account as shown here.

FWIW, I've already made subcategories of Interest income for each bank, and then subcategories for each account under each bank. Makes for a nice, tidy report.

If that works for you, then great. I'm just explaining that it's not necessary to have all these extra categories and insuring that every interest payment is correctly categorized to its proper category, when the one default interest category can get you the data you need for Schedule B.

volvogirl

@louabill Here's my interest report from Windows if anyone is interested. I don't think it shows any of my personal info except for the banks. The totals at the bottom will match up to the the 1099s I get.

louabill

@jacobs: When I look at my Tax Schedule report, it does not behave like yours. I suspect the one that you are using is one that you inherited from an older version of Quicken for the Mac. Here is what mine looks like, despite there being multiple banks:

unsplit income.png

Now… I did define my own 'interest earned' category instead of using the built-in 'income:interest earned' category, but even if I unhook my category from Schedule B and change interest payments to the built-in category, I get the same collapsed interest total.

louabill

Shoot. I posted the image badly, thinking that uploading the image would display it inline. It should be

louabill

Just to be sure, I made a brand new file and put two fake accounts in it. I posted interest to both, using the default Quicken interest income category. I made a tax schedule report. The interest from the two institutions was combined as above.

Jon

@louabill @jacobs The subcategories on the tax report below "Interest Income" are the payees. When you enter interest income, if you enter the bank name as the payee, you'll get interest broken out by bank on your tax report. But if you just enter something generic like "Interest" for the payee (which is what I tend to do), then interest from all the banks will get lumped together under that generic payee name & won't be broken out by bank.

So you can get what you want but you'll have to put in the work each month to enter the correct payee names every time you earn interest.

Edit: and going back through the thread, I see @jacobs made this same point a couple days ago, perhaps it got overlooked.

RalphC

For tax reporting purposes, the IRS is not going to recognize a Quicken report as documentation of interest income in an audit. You must enter data from your 1099-INT's that you received from the bank(s) and other sources.

jacobs

When I look at my Tax Schedule report, it does not behave like yours. I suspect the one that you are using is one that you inherited from an older version of Quicken for the Mac. 

No, the report isn't carried forward from an old version; the Tax Schedule report I showed was just created, using the latest version of Quicken.

The issue you're seeing is, I'm sure, what @Jon described above: the sorting on the Schedule B report (and the Schedule B section of the combined Tax Schedule report) is by Payee name, in the case of banking-type transactions, or Security name for investment transactions. (That's necessary because there are two separate categories used for interest income from banking and investment transactions, and users can create their own interest categories for banking transactions as well.) In my screen shot above, I have an example of both: "Capital One" is the payee name on an interest transaction in a checking account, while "Charles Schwab Corp" is the security name for an interest transaction in a brokerage account.

@Jon said: So you can get what you want but you'll have to put in the work each month to enter the correct payee names every time you earn interest.

For the interest in checking/savings accounts, you could create a scheduled transaction for each months interest, using the correct Payee name and category. When the interest transaction downloads, it will either auto-match or you can drag one over the other, so you'll get the correct Payee name from the manual transaction and the correct amount from the downloaded transaction. There isn't a way to do scheduled investment transactions, so you can't do this for interest in a brokerage account.

@RalphC said: For tax reporting purposes, the IRS is not going to recognize a Quicken report as documentation of interest income in an audit. You must enter data from your 1099-INT's that you received from the bank(s) and other sources.

This is why I had't previously focused on doing things like entering the Payee name on my interest transactions, because I don't use the Quicken tax reports other than for financial planning or quick reality checks comparing to my tax returns. I only use the information from 1099s in the preparation of tax returns (whether done by myself or an accountant), not the report from Quicken.

Jon

For tax reporting purposes, I do both. Usually near the end of the year I will do a rough pass on my taxes to see where things stand and if there are any last minutes things I need to do, and for this I rely on information from Quicken. When it comes time to actually do my taxes I use the 1099s but for de minimus interest I still need data from Quicken.

louabill

@jacobs Aha! @Jon is correct as are you, and I did not read your replies carefully enough. I had 'interest' as the payee.

So… since I use scheduled transactions for the interest, I can go change all the payees, and life will be good. Thanks a million.

FWIW, I make sure I have things in Quicken so that I could rely on the tax report to file taxes. Why? The place I worked at (a place that will remain unnamed, but which made Dilbert look like a functional workplace) got my W-2's wrong 4 of the 6 years I worked there. The place my wife worked at got her W-2 fantastically wrong one year (claimed she'd been paid more than 10 times as much as she'd actually been paid). I suppose such things should be a thing of the past, now that bookkeepers are more used to computers.

Anyways, thanks again for the help, and sorry to have kept the discussion going longer than needed dut to laziness.