Budget Issues - Working with Credit Card Payments (Google Sheets)

Google Sheets
, , , ,
1

Tiller Community,

How are you all doing today?

Maybe this is an issue you’ve all have seen before or encountered it yourself and found a fix/solution or a work around.

My problem is when I pay my credit card, the credit card company sends an authorization request/charge toward the card I have on file. After that, my checking card with my bank, sends the funds, which then it’ll be posted as the posted charge. I went into my Tiller Foundation Template and made a category for that, followed by an autocad rule; that way, I will not be confused either.

So, for the authorization charge, it’s always a positive number, which my guess is because you are paying down your debt. However, for the posted charge, it’s a negative number, which makes sense because you are paying money towards the credit card, thus losing that money.

Here is when my dilemma comes into play, on my Monthly Budget Sheet and also affects the Yearly Budget Sheet.

003
003822×54 2.9 KB

This screenshot is from the Categories sheet.

002
0021473×46 2.21 KB

This screenshot is from the Transactions sheet.

001
001744×239 2.96 KB

This screenshot is from the Monthly Budget sheet.

004
004801×43 1.2 KB

This screenshot is from the Yearly Budget sheet.

After I did a bit of research, it looks like a positive number has to be in the actual column and not a negative number, which is what I think is the problem here. However, I’m not sure what I need to do to be honest. I see the posted charge as the negative number and I have no budget amount; plus, it’s hidden from the reports too. I have the authorization set up as the expense, which links to the debt payment group, which is the actual category.

Why is my negative posted charge being pulled in my Monthly/Yearly Budget Sheets? In the budget column, I put, for example $120, and the actual, shows up as -$120 but the available shows up as $240. I did a test, and I am pretty sure it is supposed to equal out to $0.00. Instead, it is $240. Maybe I am overthinking things, but I am trying to make sense of all these; it isn’t working out, though.

What should I do in order to fix the budget? The way it is setup now, it’s reading false. I have two other credit cards and I am pretty sure its reading the same way.

Please help!

Note: Also, one of the reasons why I have two categories, one for authorization and one for posted charge, is to separate the two items due to my bank and credit card company filing it as a transaction. My intent was to keep one from my budget in its entirety, even though I know I am losing money by paying off my credit card, which results in a positive number against paying my credit card. Maybe I am missing something but please show me the light because I am loving Tiller. I have been telling my friends about it and such.

1 Like
2

Hi @dustin.d.sellers:

Welcome to the Community!

You have a very common challenge, and it can be made a little more simple if you categorize both sides of the credit card payment as a Transfer and then Hide the category from your reports. Your credit card purchases are likely being captured when you are categorizing each of the transactions as they hit your card and are pulled into your sheet. Including your card payment as an expense is double counting these purchases against your cash flow.

Here is a great discussion that may help.

Thanks for your military service, Dustin. Proud of you.

1 Like
3

Hey @Brad.warren :

Thanks, glad to be here! So, I read the post you marked down below but after some reading, I didn’t really solve my problem. So, I did mark both items (authorization and posted charge) on the categories sheet as a transfer and it did disappear. However, I need to have the credit card payment factored into my budget. When I did the transfer and hide from reports for both, it disappeared. However, when I kept the posted charge (- negative) hidden and kept the authorization charge (+ positive) in view, in the actual column, it showed up as a negative (-).

So, the authorization charge shows up as a negative. The posited charged shows up as a positive.

Now I am a bit even more confused. I know if I keep the authorization as a positive, in the actual column, it shows up as a -$120 and then it will add to $240 instead in the available column. However, if I put a (120) or -120 in the budget column on my categories sheet, it’ll even out in the last column (available column).

But if you look on my transactions, as I have showed you in the screenshots, the authorization is the positive number and the posted charged is the negative number.

How would I go about fixing this issue? I know I can hide both items on my categories so it wouldn’t be an issue for most. However, I need to factor in my credit card payment into my budget each month. Would I use the authorization charge or the posted charge? Would it be the negative or positive number? What is the best way/solution in order to fix this dilemma?

Note: When I put the (120) or -120 in the budget column on my categories sheet, it’ll cancel out the -120 in the actual column. However, why is the authorization charge showing up as a -120 (negative number), instead of a positive number? Since my authorization charge is a 120 (positive number) on my transaction sheet. After trying the (120) or -120, it puts my budget for the debt payments at a -8 %, which isn’t right. It should be a positive number, I am pretty sure. With that being said, should I be using the -120 (negative number), which is the posted charge, instead of using 120 (positive number), the authorization charge for my budgeting?

P.S. I hope this isn’t too confusing because it is confusing me a bit and I want to solve this so I can go back to budgeting and get my life together again. Thank you!

4

Thanks, @dustin.d.sellers ….two quick questions…are your credit card transactions ( day-to-day purchases ) flowing into your sheet and are you assigning these to different budget categories, like groceries, clothing, gas, etc…?

5

No problem @Brad.warren : I just looked at my credit card transactions (day-to-day purchases) and they are following into my sheet. For example, if I bought food with my credit card, I would assign it under the food budget category. That way, I know I can’t spend as much for that much. If my budget was 100 and I spent 25 with my credit card for food, that means I only have $75 dollars left. Would you recommend against including credit card purchases with my budget?

Note: I hope the above paragraph helps get to the bottom of my issue/dilemma into getting it fixed. After all, I’m sure some other people may have issues like this of what I’m guessing.

6

Hi @dustin.d.sellers:

Great work! You are on your way with a healthy approach to budgeting and living well. Way to go!

Yes. You have hit at the heart of it: prepare a budget, and then categorize each of your cash and credit transactions accordingly and adjust your spending as you go. (Preparing the budget is easy. Living within it is the hard part. Ha!)

BUT…once you have categorized your charge transactions, you shouldn’t categorize your credit card payments or plan for them in your budget: you have already accounted for these items in the budget lines elsewhere. For example, if you planned to spend $50 each in groceries, fuel and clothing each in a month, that would add up to $150. But if you also plan in your budget to make a credit card payment to cover these expenses of $150, you are actually taking $300 out of your budget: once to provide funds for the transactions when they hit, and second when you make the payment. (As you know you’re actually only paying once for these things, but in the method you’re describing, your budget thinks you’re paying for the fuel, clothing and groceries twice.)

So, simply track your credit card transactions in their appropriate categories, keep track of your credit card payments but take them out of your reports using hidden categories (call these Transfers), and set the budget for these credit card payment categories as zero every month.

Does this help? Apologies in advance if I’m missing it.

7

Appreciate it @Brad.warren !

When I mentioned about budgeting, I would simply add $100 to food and on my transactions sheet, let’s say I spent $25 with my credit card at Burger King, $25 with my debit card at Dairy Queen, and $50 with my credit card at the grocery store. With that in mind, my budget will be at a $0.00, would it not? After all, there are sometimes when you use your credit card for purchases and you don’t want those not included in your budget, right? I want to ensure my budget is accounted for accurately without any issues.

I am pretty sure I don’t have my credit card payments categorized elsewhere. I have them under the group called debt_payments. That way, I know its separate from the bills/utilities.

What I meant to say is, I don’t have my credit card purchases in a separate category. All my accounts are combined when it comes to budgeting, if that makes sense?

In regards, to my credit card payments, one is an authorization charge (positive number) and another posted charge (negative number). If you look below, that is the dilemma that I am having. At the moment, I have the authorization charge budgeted as $120 and the posted charge is hidden and not a part of my budget. The posted charge is marked as a transfer and is hidden from the reports. But the $120.00 (authorization charge) shows up as a positive number on my transactions sheet. On the monthly budget sheet, the budget is $120.00 (positive number) and the actual amount is -$120.00, which in turn, shows $240.00 in the available column. However, not sure why those amounts are added together. But if I put a negative -$120.00 in my budget cell on my categories sheet for the authorization category, it cancels out the -$120.00 for the actual, then in turn the available amount is $0.00.

Note: When I put the (120) or -120 in the budget column on my categories sheet, it’ll cancel out the -120 in the actual column. However, why is the authorization charge showing up as a -120 (negative number), instead of a positive number? Since my authorization charge is a 120 (positive number) on my transaction sheet. After trying the (120) or -120, it puts my budget for the debt payments at a -8 %, which isn’t right. It should be a positive number, I am pretty sure. With that being said, should I be using the -120 (negative number), which is the posted charge, instead of using 120 (positive number), the authorization charge for my budgeting?

However, when I tried to do that for the posted charge instead (with the same thing as the above paragraph), it looks like it evened out to a $0.00 in the available column.

What should I do?

8

While I am not exactly sure what your sheet is doing, I can offer how I handle credit cards.

The purchase ( negative amount)of the Burger King is what you track in your budget, not the CC payment. You most likely put this is Eating Out or something. How you pay for it is irrelevant. If you pay it with your debit or your credit card it is the same amount of money leaving your account. However, instead of paying it with your Debit card directly from your checking account, you use your checking account to pay off the credit card balance. That is basically a transfer of money from your checking to Credit card company. This is why you put credit card payment as type Transfer. Every purchase you make on the card should be in your budget. Otherwise you risk being overspending. If you do overspend and cannot cover it you will need to also budget for the interest on your CC. But that is different than paying your credit card. Not sure if I helped you but this is how we do it.

2 Likes
9

Hi @dustin.d.sellers

I’d like to also recommend you add your category/groups to this shared document.

I am new to using Tiller but have found that setting these up and refining them along the way are key to what you are working on. Here is a post I have on that: Category & Groups

I’ve been through the same initial set up and questions so feel free to send me a message and I’ll help where I can.

2 Likes
10

Hey @richl :

Thanks for your advice and input. I did some tinkering. After you mentioned that I should be tracking the (negative amount) into my budget, I switched the authorization (positive number) to the posted (negative number) on my categories sheet. I switched my authorization to transfer and hide; then turned my posted charge into an expense and removed the hiding. Now it seems to be showing correctly.

Budget Now
Budget Now721×221 2.97 KB

Also, that makes sense for when you budget of your credit card, debit card, etc. After all, you want to make sure you take in consideration all your purchases, so you don’t overspend and break your budget.

I think you did help but I might have another question. In regard to my debt sheet, should that be a separate file vs being a sheet in my tiller foundation?

11

@pete :

I just added my category/groups to that shared document.

Thank you for sharing that link.

Sure thing and I appreciate it by the way!

12

@dustin.d.sellers I looked at your Income, Expense, and Transfer groups. Very detailed, which is a personal choice. I believe if you simplify the Categories it may be beneficial over time as there are individual reports within Tiller Money and Tiller Community Solutions that will provide you the detailed analysis you are looking for.

It would appear to me that you may want to modify these Categories from EXPENSE Group to TRANSFER Group and Hide from Reports:

Name of Company - XXXX Card Payment - (Authorization Charge)
Name of Company - XXXX Credit Card Payment (Authorization Charge)
Name of Company - XXXX Credit Card Payment (Authorization Charge)

@Brad.warren and @richl provided the same advice regarding transfer (money in/money out of accounts).

Think of a credit card transaction as the same as writing a check or paying cash for something. The item that is purchased will show up on as a transaction which you can assign a Category or use AutoCat to help assign the Category. Your budget will be based upon your Income and Expenses. If you use the Credit Card or Cash for something that is not listed as a specific Category (expense) then you would generally assign that to a Miscellaneous Category. Looking at your list, I would assign these transactions to Misc. - Other Miscellaneous. If over time you see this Category having a higher amount of expenses than your budget you can begin to figure out how to eliminate or assign certain items their own Category for tracking and budgeting.

I have found the Profit & Loss report to be of great benefit and Tiller just released a Live P&L report which can be added to your Tiller spreadsheet from the Community Solutions. You also may want to give that a go to see how it may help.

Bottom line, Thank you for your Service and you are on the right track. I believe with a few minor tweaks you will have it to your liking.

1 Like