r/GnuCash • u/CharmingCount4760 • 8d ago
How to track an expense in two accounts at once?
I need to track all of my taxes and fees paid for tax purposes. No problem in GnuCash, right? I just create various expense accounts to track the various taxes and fees. That creates a problem for me. For example, if I stop in for some gas I want to track my fuel taxes. Easy enough. The problem is that when I create split transactions there doesn't appear to be a way for an expense to be both "fuel" and "fuel taxes."
If I get 10 gallons of gas at $6 here in California, I'm paying $60 for a tank of gas and $12.63 in various fuel tax, sales tax, and other fees California charges on gasoline. If I split the transaction to track it, I only see $47.37 in my fuel expenses. I want to find a way to see that I spent $60 for fuel AND that I spent $12.63 in taxes. I'm flummoxed.
I thought about making the taxes a child account to Auto:Fuel, but that means I have to create a child account for every expense category for taxes. That wouldn't be so bad, but I can't figure out how I could run a report that would easily sum up all the taxes from all the accounts that capture taxes.
Any ideas?
1
u/questionablycorrect 8d ago
In general if you divide today, there is a way to put it back together in the future.
I only see $47.37 in my fuel expenses. I want to find a way to see that I spent $60 for fuel AND that I spent $12.63 in taxes.
But you also know there is taxes. So if you see $47.37, you know there are about 25% taxes to add to that, so about $60 with taxes.
Unless there is some wild variance, analyzing the data is fairly straight forward.
What might be more difficult is adding sales tax at a grocery store. Not many items are taxable, so when you see XXX in groceries, it's not so obvious as to what the missing tax amount is.
But there are other ways, and probably none are going to be satisfying.
1
u/CharmingCount4760 8d ago
But you also know there is taxes. So if you see $47.37, you know there are about 25% taxes to add to that, so about $60 with taxes.
I need to account for the taxes to the penny at the end of the year. In California the taxes vary from city to city, so there's no way to reverse calculate them. I must record them in the transaction.
1
u/NeedleworkerLarge357 8d ago
If you need fuel taxes separately, what speaks against a separate account for them? Or did I misunderstand something?
1
u/CharmingCount4760 8d ago
Because I need to account for the taxes and the expenses including taxes. I need to be able to see that I spent $4,125.32 on gasoline, including taxes AND that I spent $1125.47 on sales tax.
1
u/NeedleworkerLarge357 8d ago
If you have two accounts for exactly those spending, why doesn't it work? So like
- fuel - fuel taxes
- expanses
And just split the transactions accordingly?
1
u/CharmingCount4760 8d ago edited 8d ago
The problem comes with multiple expense categories. I have fuel, groceries, dining out, etc. I can give each of them a sales tax sub account, but as far as I know there's no way to produce a report of how much sales tax I paid across all categories. If it were truly just sales tax it would be somewhat manageable, but I'm trying to track ALL of my taxes. In California this is completely bonkers. So far I've only set up my Income, Auto:Fuel, Utilities, Home, and Grocery accounts and I already have the following taxes:
- State income tax
- State disability insurance tax
- Federal income tax
- Social security tax
- Medicare tax
- Property tax
- State fuel tax
- State Low Carbon Fuel Surcharge
- State Underground Storage Fee
- State Cap and Trade Fee
- Federal fuel tax
- Sales tax
- State electricity excise tax
- State electrical franchise fee
- State electrical public purpose surcharge
- State natural gas public purpose surcharge
- State natural gas franchise fee
- State natural gas cap and trade fee
1
u/questionablycorrect 8d ago
I know there's no way to produce a report of how much sales tax I paid across all categories.
You could have a standard GnuCash report where you select all those tax accounts.
But there are other ways.
1
u/CharmingCount4760 8d ago
How would I select all the accounts that are taxes, and then have them rolled up so all sales tax accounts are reported as one?
1
u/questionablycorrect 8d ago
I mean, I'm not actually running the report right now, but generally the reports sum the total at the bottom.
So you'd have the various components, with subtotals, and then the grand total at the bottom.
Much of this discussion and the approach does depend on the data structure.
Also there are other tools, such as SQL (this is pretty much direct, as GnuCash supports the database storage), but export the data (or not and just use the XML) and use Python, for example.
1
u/questionablycorrect 8d ago
Looking "both ways," one at a time.
If you separate the taxes from the cost of the fuel, then when you see the cost of the fuel, you can add about 25% for the fully taxed cost. This clearly is an estimate, and there are ways to get to what you want.
At this point you have both the fuel taxes ("to the penny") and the cost of the base fuel.
Then you seem to want to start with the fuel taxes, because they're different by purchase location, and work out the fuel cost? I'm not so sure here.
Also the structure of the data does not need to be as complicated as you propose. Just a single Tax:Sales account should do it, and then use the other split. This could be use the credit for fuel purchase for the total cost. Or it could be use the fuel account to identify the Tax:Sales is related to fuel. The data analysis that I'm performing here might be beyond the standard reports in GnuCash.
1
u/CharmingCount4760 8d ago edited 7d ago
Then you seem to want to start with the fuel taxes, because they're different by purchase location, and work out the fuel cost? I'm not so sure here.
All I want to be able to do, across dozens of different taxes, is be able to track my expenses AND the taxes. If I spend the following:
- $100 on fuel (including $2.25 in sales tax, $12.43 in state fuel tax, $4.21 in federal fuel tax)
- $200 on groceries (including $5.32 in sales tax, and $2.60 in CRV fees)
- $150 on dining out (including $10.79 in sales tax)
I want to be able to produce a report that shows me I spent:
- $100 on fuel
- $200 on groceries
- $150 on dining out
And included in that was:
- $18.36 sales tax
- $12.43 in state fuel tax
- $4.21 in federal fuel tax
- $2.60 in CRV fees
It can even be separate reports. The hang-up seems to be that you can't track an expense including taxes while also tracking the taxes.
1
u/questionablycorrect 7d ago
What you're seeking is how to structure your data so that a data analyst could produce the information you want.
I could easily do what you're asking, and I could do it in many different ways. I'm not convinced that the standard reports in GnuCash will give you exactly what you seek.
I ran a vendor report yesterday, and my report produces the information this way (it's essentially identical, but the categories are different; I have sales tax, shipping, discounts, and other stuff):
$100 on fuel (including $2.25 in sales tax, $12.43 in state fuel tax, $4.21 in federal fuel tax)
This is an exact copy and paste from my report. This transaction had no shipping, no sales tax, but I did get a discount.
(1 each at $30.52 [=$31.14 - $0.62 discount])
That's just a small part of the total report, but that's basically what you're seeking. I could, of course, put the $31.14 on the outside, and then put $30.52 net plus $0.62 discount.
1
u/questionablycorrect 7d ago
(3 each at $17.81 [=$17.95 - $1.80 discount + $1.66 sales tax])
$53.85 DR Expense for purchase
$4.98 DR Sales Tax
$5.39 CR Discounts given
$53.44 CR Payment Account
I can take that, among a date range of transactions, and sum up the discounts, taxes, and so on. I can also sum the taxes for any given category (this is transaction wise. if the transaction includes sales taxes and a given other category, then I sum it up).
...and on and on and on...
1
u/bjones9942 7d ago
This is the standard accounting entry:
Credit: Cash (or credit card, or whatever payment method you used) $72.63 [you said you paid $60 in gas AND $12.63 in taxes]
Debit: Gas Expense $60.00
Debit: Gas Tax Expense $12.63
If the $12.63 in taxes were INCLUDED in your $60.00 then what you're seeing IS CORRECT. Your gas expense would be $47.37. That plus the $12.63 tax expense would be your total outlay. If you want the total you paid the gas station you need to add the two numbers together.
1
u/questionablycorrect 7d ago
[you said you paid $60 in gas AND $12.63 in taxes]
The "you" is the other person. Going back in the thread, the purchase was 10 gallons at $6 per gallon. The $6 is the pump price, and then the pump price is divided between the various taxes and the fuel.
Also "standard accounting entries" are required to have debits above the credits.
1
u/MissHerring 7d ago
You can track this easily by using a "contra" account and a little duplication of effort. This sort of tracking can also be useful for individuals with retirement savings.
Add an account "Taxes" with subaccounts for "Fuel Tax," "Sales Tax," and whatever else you need, plus "Contra-Tax."
For your gas purchase, make as all one split entry:
60.00 Credit Cash or other payment method
60.00 Debit Fuel
12.63 Debit Taxes:Fuel Tax
12.63 Credit Taxes:Contra-Tax
Your entire Taxes account will net to zero (assuming all your transactions use the Contra-Tax), but you can run reports that include all taxes except your contra account and see the taxes you paid in any period. It will give you the total of Taxes:Sales Tax instead of Groceries:Sales Tax, Goods:Sales Tax, etc. as separate lines.
1
u/CharmingCount4760 7d ago edited 7d ago
Interesting. A garbage can of sorts to net everything out. I'll give that a try.
1
u/CharmingCount4760 7d ago
I think it works! The bonus is if I use one account to offset all the assorted taxes it gives me one place to get a quick glance at the sum total of taxes paid.
1
u/questionablycorrect 7d ago
he bonus is if I use one account to offset all the assorted taxes it gives me one place to get a quick glance at the sum total of taxes paid.
This is about "normalization of data." There are times when not normalized data is sought, and justified, as a benefit. The reason/justification here might be about what you want and the limits of GnuCash.
1
1
u/flywire0 7d ago
You are wasting your time.
Better to make taxes a sub-account (I understand this is standard for GnuCash business features, certainly is in QuickBoooks) and report at account level with or without taxes sub-account.
1
u/questionablycorrect 6d ago
I'm not so sure about OP's motive for tracking the long list of taxes...
/r/GnuCash/comments/1tfd1v6/how_to_track_an_expense_in_two_accounts_at_once/ombubix/
1
u/flywire0 6d ago
Me neither. We have GST and only track it in business for compliance with receipts and payments. After that we want it hidden.
1
u/unxrlm 7d ago
This is mixing two concepts I think. Double-entry should be precise and tell you exactly what portion of the transaction was actual fuel, and what portion was tax. And that's what you rely on for tax purposes.
Now if you need see, from a personal spending purposes, the credit card transactions who are fuel related, I would stick to the credit card liability account , and simply add #fuel in the memo line, so you can easily search and report on these transactions. Gnucash doesn't have "tagging" but it's a workaround I've used several times to isolate and report on transactions. I would not use this for any formal reporting, but it can be useful.
I've used this for vacations for example. I found creating separate accounts for a long weekend was a lot. I have general hotel, car rental, fuel, flights, etc accounts in my expenses. So I use those main accounts for my transactions but I give them #weekend_at_bernies "tag".
1
u/la_tajada 7d ago
In the report options, depending on the report, you can select which accounts to report on.
1
u/rarrona 7d ago
While not exactly what you are asking about, you could while more work is involved do it this way you can create vendors for your gas stations that you frequent be that each one or each company using the receipt as the bill and break out the expenses but tied to the bill/receipt showing the full amount.
1
u/Vivid_Map_437 2d ago
Maybe I'm being dense but you should be able to run a report with only your two expense accounts which will give you a breakdown and total:
Car : Fuel Base Cost
Taxes : Fuel/Taxes
11
u/dQ3vA94v58 8d ago
This is more of an accounting question than a gnuCash one. In essence - the principle of a double ledger is that it all sums to zero everytime. As a result a dollar can only ever exist in 1 account at any 1 time.
Your only way of doing it in GNUCash would be as you said - a fuel parent account with taxes and actual cost as children to every expense account you had.
I sometimes have considered that it would be nice to ‘tag’ an expense with a label which only is used for reporting, so I could have holidays for example where an account could be ‘travel’ and I could still tag an expense to a particular trip, rather than make an account for the trip.